• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 496
  • Last Modified:

Test for column existence with Transact-SQL

How can I determine if a column exists? I would like to create an idempotent script to make some schema changes and would like to know if I have already added a column.
0
steve_webber
Asked:
steve_webber
2 Solutions
 
Éric MoreauSenior .Net ConsultantCommented:
you can query the sys.columns table
0
 
Jared_SCommented:
if not Exists(select * from sys.columns where Name = N'columnName'  
            and Object_ID = Object_ID(N'tableName'))

begin

    Add the column...

end
0
 
KatieAndEmilCommented:
Below is link to SQL Tutorial with what you are after (and similar)

SQL check if column exists

Hope that helps
Emil
0
 
keyuCommented:
Search in Table:

if Exists(select * from sys.columns where Name = N'columnName'  
            and Object_ID = Object_ID(N'tableName'))

Search in Database:

SELECT t.name AS table_name,
 SCHEMA_NAME(schema_id) AS schema_name,
 c.name AS column_name
 FROM sys.tables AS t
 INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
 WHERE c.name LIKE '%EmployeeID%'
 ORDER BY schema_name, table_name;

Search all coumns in database :

SELECT t.name AS table_name,
 SCHEMA_NAME(schema_id) AS schema_name,
 c.name AS column_name
 FROM sys.tables AS t
 INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
ORDER BY schema_name, table_name;

Ref.Link:

http://blog.sqlauthority.com/2008/08/06/sql-server-query-to-find-column-from-all-tables-of-database/
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now