Link to home
Start Free TrialLog in
Avatar of RobAinscough
RobAinscough

asked on

How to check if a Field Exists?

I need to discover if a Field Exists in a specific database table on MS SQL Server 2000.  I was using:

            SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'Field1'

this apparently is NOT the correct approach is it will default to the Master database not the database I'm connected to.

What is the correct way to determine if a field exists in a table for my database?

thanks, Rob.
Avatar of Jim P.
Jim P.
Flag of United States of America image

SELECT *
FROM DbName.dbo.INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'Field1'

ASKER CERTIFIED SOLUTION
Avatar of Jim P.
Jim P.
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Michael_D you missed a period

SELECT *
FROM MyDatabase..INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'MyColumn' AND Table_Name ='MyTable'
Nope,

It works perfectly on my side :)
And Your version returns error :
Could not find server 'MyDatabase' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
Avatar of RobAinscough
RobAinscough

ASKER

Sorry folks, none of the above are working.

I tried to execute the above query from a View in either MyDatabase or Master and still get:

could not find "MyDatabase" in sysservers.  And I know MyDatabase does exists as I can browse to in in Enterprise Manager and manipulate tables, etc. etc.
Dumb Question:  Is the query analyzer running against the same server?

Michael_D,

My Sincerest humble apologies. Forgot  INFORMATION_SCHEMA was the schema.

RobAinscough, it should be


SELECT *
FROM northwind.INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'ContactName'
  AND Table_Name ='Customers'
Ok, this works:

SELECT     *
FROM         INFORMATION_SCHEMA.COLUMNS
WHERE     (COLUMN_NAME = 'Field1') AND (TABLE_NAME = 'Table1')

From within a View/connection to MyDatabase.

Table_Name was what I needed.

Thanks, Rob.
Avatar of Scott Pletcher

IF EXISTS(      SELECT 1
      FROM dbName.dbo.syscolumns WITH (NOLOCK)
      WHERE OBJECT_NAME(id) = N'tableName'
      AND name = N'columnName')
      PRINT 'Yes'
ELSE
      PRINT 'No'
WOW!!!

Grade B!!!

THANK YOU!!!!!