This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing few options for checking whether a table exists in a SQL Server database or not.
When you are preparing the full database change script, you should put DDL statements in the IF EXISTS condition for avoiding any error.
Option 1:
|
1 2 3 4 5 6 7 8 |
IF OBJECT_ID(N'table_name', N'U') IS NOT NULL BEGIN PRINT 'Table Exists...' END ELSE BEGIN PRINT 'Table Does not Exists...' END |
Option 2:
|
1 2 3 4 5 6 7 8 |
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'table_name') BEGIN PRINT 'Table Exists...' END ELSE BEGIN PRINT 'Table Does not Exists...' END |
Option 3:
|
1 2 3 4 5 6 7 8 |
IF EXISTS(SELECT 1 FROM sys.Objects WHERE Object_id = OBJECT_ID(N'table_name') AND Type = N'U') BEGIN PRINT 'Table Exists...' END ELSE BEGIN PRINT 'Table Does not Exists...' END |
Option 4:
|
1 2 3 4 5 6 7 8 |
IF EXISTS(SELECT 1 FROM sys.Tables WHERE Name = N'table_name' AND Type = N'U') BEGIN PRINT 'Table Exists...' END ELSE BEGIN PRINT 'Table Does not Exists...' END |
Option 5:
|
1 2 3 4 5 6 7 8 |
IF EXISTS(SELECT name FROM sys.sysobjects WHERE Name = N'table_name' AND xtype = N'U') BEGIN PRINT 'Table Exists...' END ELSE BEGIN PRINT 'Table Does not Exists...' END |
Leave a Reply