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.
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Michael_D you missed a period
SELECT *
FROM MyDatabase..INFORMATION_SC HEMA.COLUM NS WHERE COLUMN_NAME = 'MyColumn' AND Table_Name ='MyTable'
SELECT *
FROM MyDatabase..INFORMATION_SC
Nope,
It works perfectly on my side :)
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.
Could not find server 'MyDatabase' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
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.
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_SCHE MA.COLUMNS
WHERE COLUMN_NAME = 'ContactName'
AND Table_Name ='Customers'
Michael_D,
My Sincerest humble apologies. Forgot INFORMATION_SCHEMA was the schema.
RobAinscough, it should be
SELECT *
FROM northwind.INFORMATION_SCHE
WHERE COLUMN_NAME = 'ContactName'
AND Table_Name ='Customers'
ASKER
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.
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.
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!!!!!
Grade B!!!
THANK YOU!!!!!
FROM DbName.dbo.INFORMATION_SCH