Field Exists?

I'm looking for the best way (and most efficient) to check to see if a field
exists.
I have the following, but am not sure if it's the best way.
IF EXISTS(SELECT Name from syscolumns where ID=OBJECT_ID('tablename')
AND Name='Fieldname')
Is there a better way?
Also curious if there's a syntax for doing it that works with more than just
SQL Server (possibly MySQL and/or Oracle)?Generic != most efficient.
The query you posted seems OK. Note that if you have a view named tablename
with a column named
Fieldname, you would get a hit. Same if you have a stored procedure named ta
blename with a parameter
named Fieldname. So consider filtering on xtype column as well.
If you want generic, use the INFORMATION_SCHEMA.COLUMNS view. This is ANSI S
QL standard, if the
other RDBMS follows the standard, the same query should work. This is probab
ly less efficient, so
check the view definition (it is in master for SQL Server 2000) and see what
it looks like.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Les Stockton" <LesStockton@.discussions.microsoft.com> wrote in message
news:471B3ADA-139D-4FA8-919C-7DE096F8DDF2@.microsoft.com...
> I'm looking for the best way (and most efficient) to check to see if a fie
ld
> exists.
> I have the following, but am not sure if it's the best way.
> IF EXISTS(SELECT Name from syscolumns where ID=OBJECT_ID('tablename')
> AND Name='Fieldname')
> Is there a better way?
> Also curious if there's a syntax for doing it that works with more than ju
st
> SQL Server (possibly MySQL and/or Oracle)?
>|||Another option is to use the metadata functions like COL_LENGTH or
COLUMNPROPERTY. Use any argument and if it returns NULL then you can
conclude the column does not exist. See SQL Server Books Online for the
various arguments you can use for these functions.
Anith|||"Les Stockton" <LesStockton@.discussions.microsoft.com> wrote in message
news:471B3ADA-139D-4FA8-919C-7DE096F8DDF2@.microsoft.com...
> I'm looking for the best way (and most efficient) to check to see if a
> field
> exists.
> I have the following, but am not sure if it's the best way.
> IF EXISTS(SELECT Name from syscolumns where ID=OBJECT_ID('tablename')
> AND Name='Fieldname')
> Is there a better way?
> Also curious if there's a syntax for doing it that works with more than
> just
> SQL Server (possibly MySQL and/or Oracle)?
>
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS c
JOIN INFORMATION_SCHEMA.TABLES t ON c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
WHERE c.TABLE_NAME = 'tablename'
AND c.COLUMN_NAME = 'columnname'
AND t.TABLE_TYPE = 'BASE TABLE'
You can use this query. It will ensure that you don't have a conflict with
a view as well.
Rick Sawtell
MCT, MCSD, MCDBA