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.

  • Stored procedures, which I don't want to use
  • 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?

    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

    针对information_schema视图进行查询： 

    select * 
from information_schema.columns
where column_name = @mycolumn
    and table_name = @mytable
    链接地址: http://www.djcxy.com/p/94422.html

    上一篇: 如何在SQL Server中创建外键？

    下一篇: 检查SQL Server数据库表中是否存在表或列