?
Solved

How to check if a Field Exists?

Posted on 2005-06-29
11
Medium Priority
?
4,029 Views
Last Modified: 2008-01-09
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.
0
Comment
Question by:RobAinscough
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 38

Expert Comment

by:Jim P.
ID: 14331173
SELECT *
FROM DbName.dbo.INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'Field1'

0
 
LVL 38

Accepted Solution

by:
Jim P. earned 75 total points
ID: 14331252
You can also do it the hard way:

select tables.name, cols.*  
from northwind.dbo.syscolumns cols
right join northwind.dbo.sysobjects tables
on cols.id = tables.id
where cols.name ='ContactName'
 
0
 
LVL 13

Assisted Solution

by:Michael_D
Michael_D earned 75 total points
ID: 14331295
Try

SELECT *
FROM MyDatabase.INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'MyColumn' AND Table_Name ='MyTable'
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
LVL 38

Expert Comment

by:Jim P.
ID: 14331346
Michael_D you missed a period

SELECT *
FROM MyDatabase..INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'MyColumn' AND Table_Name ='MyTable'
0
 
LVL 13

Expert Comment

by:Michael_D
ID: 14331487
Nope,

It works perfectly on my side :)
0
 
LVL 13

Expert Comment

by:Michael_D
ID: 14331520
And Your version returns error :
Could not find server 'MyDatabase' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
0
 

Author Comment

by:RobAinscough
ID: 14331521
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.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 14331592
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'
0
 

Author Comment

by:RobAinscough
ID: 14331594
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.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 14331645

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'
0
 
LVL 13

Expert Comment

by:Michael_D
ID: 14331695
WOW!!!

Grade B!!!

THANK YOU!!!!!
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

592 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question