Methods for Checking Object Existence in SQL Server
Tech
2
1. Check if a Database Exists
IF EXISTS (SELECT 1 FROM sys.databases WHERE name = 'TargetDatabase')
DROP DATABASE TargetDatabase;
2. Check if a Table Exists
IF OBJECT_ID(N'dbo.TargetTable', N'U') IS NOT NULL
DROP TABLE dbo.TargetTable;
3. Check if a Stored Procedure Exists
IF OBJECT_ID(N'dbo.TargetProcedure', N'P') IS NOT NULL
DROP PROCEDURE dbo.TargetProcedure;
4. Check if a Temporary Table Exists
IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable;
5. Check if a View Exists
-- For SQL Server 2005 and later
IF EXISTS (SELECT 1 FROM sys.views WHERE object_id = OBJECT_ID(N'dbo.TargetView'))
DROP VIEW dbo.TargetView;
6. Check if a Function Exists
IF OBJECT_ID(N'dbo.TargetFunction', N'FN') IS NOT NULL
DROP FUNCTION dbo.TargetFunction;
7. Retrieve User-Created Object Information
SELECT name, id, crdate FROM sys.objects WHERE type = 'U';
/*
Common type values:
C = CHECK constraint
D = DEFAULT constraint
F = FOREIGN KEY constraint
FN = Scalar function
IF = Inline table-valued function
P = Stored procedure
PK = PRIMARY KEY constraint
S = System table
TF = Table-valued function
TR = Trigger
U = User table
V = View
*/
8. Check if a Column Exists
IF EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.TargetTable') AND name = 'TargetColumn')
ALTER TABLE dbo.TargetTable DROP COLUMN TargetColumn;
9. Check if a Column is an Identiyt Column
IF COLUMNPROPERTY(OBJECT_ID(N'dbo.TargetTable'), 'TargetColumn', 'IsIdentity') = 1
PRINT 'Identity column';
ELSE
PRINT 'Not an identity column';
SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.TargetTable') AND is_identity = 1;
10. Check if a Index Exists in a Table
IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'dbo.TargetTable') AND name = 'TargetIndex')
PRINT 'Index exists';
ELSE
PRINT 'Index does not exist';
11. View Objeccts in the Database
SELECT * FROM sys.objects WHERE name = 'TargetObject';
