3

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ck84vi
  • 1,556
  • 7
  • 27
  • 49
  • 1
    You can always wrap your query in an `IF EXISTS` statement for that table or column. The real question is, is your database that far out of sync with your application code that the database schema may change between deployments? If that's the case, you have bigger issues. – George Stocker Feb 18 '15 at 03:19
  • @George Stocker: My problem is that i will need to create new columns during runtime. Therfore i just want to check before if the column already exists or not. – ck84vi Feb 18 '15 at 03:23
  • @ck84vi That should be part of your question. That's an important piece. Since you said that, my answer is the way to go. – George Stocker Feb 18 '15 at 03:24
  • @ck84vi An issue with creating new columns at Runtime is that your existing indexes won't pick them up, causing it to use the Clustered Index whenever a new column is added. You'd then have to rejigger your indexes if you want your application to be fast. – George Stocker Feb 18 '15 at 03:32

2 Answers2

7

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
Community
  • 1
  • 1
George Stocker
  • 57,289
  • 29
  • 176
  • 237
  • I think you still need to issue this as two separate calls to the db. My understanding is that the database would fail the entire query at the compile stage if the table or column doesn't exist, even though they are inside the if statement. – Joel Coehoorn Feb 18 '15 at 03:24
  • @JoelCoehoorn It appears he's not using `DataReader`, he's actually creating columns on the fly. – George Stocker Feb 18 '15 at 03:24
1

Query against the information_schema views:

select * 
from information_schema.columns
where column_name = @mycolumn
    and table_name = @mytable
Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794