CODEWITHSUNDEEP
c#sqlsql-server
ck84vi
ck84vi

Reputation: 1586

Check if table or column exists in SQL Server database table

Before I create a column or table in a SQL Server database I want to check if the required table and/or columns exist.

I have searched around and found 2 ways so far.

  1. Stored procedures, which I don't want to use
  2. By using the SqlCommand.ExecuteScalar() method and catching an exception to determine if the table/column exists, which for me is a work around but not a perfect solution.

Is there another way to check if table/column exists in SQL Server?

Upvotes: 3

Views: 14813

Answers (2)

George Stocker
George Stocker

Reputation: 57907

To check if a schema exists before creating it, you do the following:

To check if a column exists; you use IF NOT EXISTS and then put your actual query inside of that.

IF NOT EXISTS(SELECT * FROM sys.columns 
        WHERE [name] = N'columnName' AND [object_id] = OBJECT_ID(N'tableName'))
BEGIN
    ALTER TABLE ADD COLUMN MYCOLUMN
END

For a table, the query is a little similar:

IF (NOT EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'TheSchema' 
                 AND  TABLE_NAME = 'TheTable'))
BEGIN
    CREATE TABLE MYTABLE
END

Upvotes: 7

Joel Coehoorn
Joel Coehoorn

Reputation: 416111

Query against the information_schema views:

select * 
from information_schema.columns
where column_name = @mycolumn
    and table_name = @mytable

Upvotes: 1

Related Questions