SQL Server script commands to check if object exists and drop it
- by deadlydog
Over the past couple years I’ve been keeping track of common SQL Server 
script commands that I use so I don’t have to constantly Google them.  Most of 
them are how to check if a SQL object exists before dropping it.  I thought 
others might find these useful to have them all in one place, so here you 
go:
   1: --===============================   2: -- Create a new table and add keys and constraints   3: --===============================   4: IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TableName' AND TABLE_SCHEMA='dbo')   5: BEGIN   6:     CREATE TABLE [dbo].[TableName]    7:     (   8:         [ColumnName1] INT NOT NULL, -- To have a field auto-increment add IDENTITY(1,1)   9:         [ColumnName2] INT NULL,  10:         [ColumnName3] VARCHAR(30) NOT NULL DEFAULT('')  11:     )  12:    13:     -- Add the table's primary key  14:     ALTER TABLE [dbo].[TableName] ADD CONSTRAINT [PK_TableName] PRIMARY KEY NONCLUSTERED  15:     (  16:         [ColumnName1],   17:         [ColumnName2]  18:     )  19:       20:     -- Add a foreign key constraint  21:     ALTER TABLE [dbo].[TableName] WITH CHECK ADD CONSTRAINT [FK_Name] FOREIGN KEY  22:     (  23:         [ColumnName1],   24:         [ColumnName2]  25:     )  26:     REFERENCES [dbo].[Table2Name]   27:     (  28:         [OtherColumnName1],   29:         [OtherColumnName2]  30:     )  31:       32:     -- Add indexes on columns that are often used for retrieval  33:     CREATE INDEX IN_ColumnNames ON [dbo].[TableName]  34:     (  35:         [ColumnName2],  36:         [ColumnName3]  37:     )  38:       39:     -- Add a check constraint  40:     ALTER TABLE [dbo].[TableName] WITH CHECK ADD CONSTRAINT [CH_Name] CHECK (([ColumnName] >= 0.0000))  41: END  42:    43: --===============================  44: -- Add a new column to an existing table  45: --===============================  46: IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='dbo'  47:     AND TABLE_NAME = 'TableName' AND COLUMN_NAME = 'ColumnName')  48: BEGIN  49:     ALTER TABLE [dbo].[TableName] ADD [ColumnName] INT NOT NULL DEFAULT(0)  50:       51:     -- Add a description extended property to the column to specify what its purpose is.  52:     EXEC sys.sp_addextendedproperty @name=N'MS_Description',   53:         @value = N'Add column comments here, describing what this column is for.' ,   54:         @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',  55:         @level1name = N'TableName', @level2type=N'COLUMN',  56:         @level2name = N'ColumnName'  57: END  58:    59: --===============================  60: -- Drop a table  61: --===============================  62: IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TableName' AND TABLE_SCHEMA='dbo')  63: BEGIN  64:     DROP TABLE [dbo].[TableName]  65: END  66:    67: --===============================  68: -- Drop a view  69: --===============================  70: IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'ViewName' AND TABLE_SCHEMA='dbo')  71: BEGIN  72:     DROP VIEW [dbo].[ViewName]  73: END  74:    75: --===============================  76: -- Drop a column  77: --===============================  78: IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='dbo'  79:     AND TABLE_NAME = 'TableName' AND COLUMN_NAME = 'ColumnName')  80: BEGIN  81:    82:     -- If the column has an extended property, drop it first.  83:     IF EXISTS (SELECT * FROM sys.fn_listExtendedProperty(N'MS_Description', N'SCHEMA', N'dbo', N'Table',  84:                 N'TableName', N'COLUMN', N'ColumnName')  85:     BEGIN  86:         EXEC sys.sp_dropextendedproperty @name=N'MS_Description',   87:             @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',  88:             @level1name = N'TableName', @level2type=N'COLUMN',  89:             @level2name = N'ColumnName'  90:     END  91:    92:     ALTER TABLE [dbo].[TableName] DROP COLUMN [ColumnName]  93: END  94:    95: --===============================  96: -- Drop Primary key constraint  97: --===============================  98: IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='PRIMARY KEY' AND TABLE_SCHEMA='dbo'  99:         AND TABLE_NAME = 'TableName' AND CONSTRAINT_NAME = 'PK_Name') 100: BEGIN 101:     ALTER TABLE [dbo].[TableName] DROP CONSTRAINT [PK_Name] 102: END 103:   104: --=============================== 105: -- Drop Foreign key constraint 106: --=============================== 107: IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='FOREIGN KEY' AND TABLE_SCHEMA='dbo' 108:         AND TABLE_NAME = 'TableName' AND CONSTRAINT_NAME = 'FK_Name') 109: BEGIN 110:     ALTER TABLE [dbo].[TableName] DROP CONSTRAINT [FK_Name] 111: END 112:   113: --=============================== 114: -- Drop Unique key constraint 115: --=============================== 116: IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='UNIQUE' AND TABLE_SCHEMA='dbo' 117:         AND TABLE_NAME = 'TableName' AND CONSTRAINT_NAME = 'UNI_Name') 118: BEGIN 119:     ALTER TABLE [dbo].[TableNames] DROP CONSTRAINT [UNI_Name] 120: END 121:   122: --=============================== 123: -- Drop Check constraint 124: --=============================== 125: IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='CHECK' AND TABLE_SCHEMA='dbo' 126:         AND TABLE_NAME = 'TableName' AND CONSTRAINT_NAME = 'CH_Name') 127: BEGIN 128:     ALTER TABLE [dbo].[TableName] DROP CONSTRAINT [CH_Name] 129: END 130:   131: --=============================== 132: -- Drop a column's Default value constraint 133: --=============================== 134: DECLARE @ConstraintName VARCHAR(100) 135: SET @ConstraintName = (SELECT TOP 1 s.name FROM sys.sysobjects s JOIN sys.syscolumns c ON s.parent_obj=c.id 136:                         WHERE s.xtype='d' AND c.cdefault=s.id  137:                         AND parent_obj = OBJECT_ID('TableName') AND c.name ='ColumnName') 138:   139: IF @ConstraintName IS NOT NULL 140: BEGIN 141:     EXEC ('ALTER TABLE [dbo].[TableName] DROP CONSTRAINT ' + @ConstraintName) 142: END 143:   144: --=============================== 145: -- Example of how to drop dynamically named Unique constraint 146: --=============================== 147: DECLARE @ConstraintName VARCHAR(100) 148: SET @ConstraintName = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS  149:                         WHERE CONSTRAINT_TYPE='UNIQUE' AND TABLE_SCHEMA='dbo' 150:                         AND TABLE_NAME = 'TableName' AND CONSTRAINT_NAME LIKE 'FirstPartOfConstraintName%') 151:   152: IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='UNIQUE' AND TABLE_SCHEMA='dbo' 153:         AND TABLE_NAME = 'TableName' AND CONSTRAINT_NAME = @ConstraintName) 154: BEGIN 155:     EXEC ('ALTER TABLE [dbo].[TableName] DROP CONSTRAINT ' + @ConstraintName) 156: END 157:   158: --=============================== 159: -- Check for and drop a temp table 160: --=============================== 161: IF OBJECT_ID('tempdb..#TableName') IS NOT NULL DROP TABLE #TableName 162:   163: --=============================== 164: -- Drop a stored procedure 165: --=============================== 166: IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='PROCEDURE' AND ROUTINE_SCHEMA='dbo' AND 167:         ROUTINE_NAME = 'StoredProcedureName') 168: BEGIN 169:     DROP PROCEDURE [dbo].[StoredProcedureName] 170: END 171:   172: --=============================== 173: -- Drop a UDF 174: --=============================== 175: IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='FUNCTION' AND ROUTINE_SCHEMA='dbo' AND  176:         ROUTINE_NAME = 'UDFName') 177: BEGIN 178:     DROP FUNCTION [dbo].[UDFName] 179: END 180:   181: --=============================== 182: -- Drop an Index 183: --=============================== 184: IF EXISTS (SELECT * FROM SYS.INDEXES WHERE name = 'IndexName') 185: BEGIN 186:     DROP INDEX TableName.IndexName 187: END 188:   189: --=============================== 190: -- Drop a Schema 191: --=============================== 192: IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'SchemaName') 193: BEGIN 194:     EXEC('DROP SCHEMA SchemaName') 195: END
And here’s the same code, just not in the little code view window so that you 
don’t have to scroll it.--===============================
-- Create a new table and add keys and constraints
--===============================
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TableName' AND TABLE_SCHEMA='dbo')
BEGIN
	CREATE TABLE [dbo].[TableName]  (
		[ColumnName1] INT NOT NULL, -- To have a field auto-increment add IDENTITY(1,1)
		[ColumnName2] INT NULL,
		[ColumnName3] VARCHAR(30) NOT NULL DEFAULT('')
	)
 
	-- Add the table's primary key
	ALTER TABLE [dbo].[TableName] ADD CONSTRAINT [PK_TableName] PRIMARY KEY NONCLUSTERED
	(
		[ColumnName1],  [ColumnName2]
	)
	
	-- Add a foreign key constraint
	ALTER TABLE [dbo].[TableName] WITH CHECK ADD CONSTRAINT [FK_Name] FOREIGN KEY
	(
		[ColumnName1],  [ColumnName2]
	)
	REFERENCES [dbo].[Table2Name]  (
		[OtherColumnName1],  [OtherColumnName2]
	)
	
	-- Add indexes on columns that are often used for retrieval
	CREATE INDEX IN_ColumnNames ON [dbo].[TableName]
	(
		[ColumnName2],
		[ColumnName3]
	)
	
	-- Add a check constraint
	ALTER TABLE [dbo].[TableName] WITH CHECK ADD CONSTRAINT [CH_Name] CHECK (([ColumnName] >= 0.0000))
END
 
--===============================
-- Add a new column to an existing table
--===============================
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='dbo'
	AND TABLE_NAME = 'TableName' AND COLUMN_NAME = 'ColumnName')
BEGIN
	ALTER TABLE [dbo].[TableName] ADD [ColumnName] INT NOT NULL DEFAULT(0)
	
	-- Add a description extended property to the column to specify what its purpose is.
	EXEC sys.sp_addextendedproperty @name=N'MS_Description',  @value = N'Add column comments here, describing what this column is for.' ,  @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',
		@level1name = N'TableName', @level2type=N'COLUMN',
		@level2name = N'ColumnName'
END
 
--===============================
-- Drop a table
--===============================
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TableName' AND TABLE_SCHEMA='dbo')
BEGIN
	DROP TABLE [dbo].[TableName]
END
 
--===============================
-- Drop a view
--===============================
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'ViewName' AND TABLE_SCHEMA='dbo')
BEGIN
	DROP VIEW [dbo].[ViewName]
END
 
--===============================
-- Drop a column
--===============================
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='dbo'
	AND TABLE_NAME = 'TableName' AND COLUMN_NAME = 'ColumnName')
BEGIN
 
	-- If the column has an extended property, drop it first.
	IF EXISTS (SELECT * FROM sys.fn_listExtendedProperty(N'MS_Description', N'SCHEMA', N'dbo', N'Table',
				N'TableName', N'COLUMN', N'ColumnName')
	BEGIN
		EXEC sys.sp_dropextendedproperty @name=N'MS_Description',  @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',
			@level1name = N'TableName', @level2type=N'COLUMN',
			@level2name = N'ColumnName'
	END
 
	ALTER TABLE [dbo].[TableName] DROP COLUMN [ColumnName]
END
 
--===============================
-- Drop Primary key constraint
--===============================
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='PRIMARY KEY' AND TABLE_SCHEMA='dbo'
		AND TABLE_NAME = 'TableName' AND CONSTRAINT_NAME = 'PK_Name')
BEGIN
	ALTER TABLE [dbo].[TableName] DROP CONSTRAINT [PK_Name]
END
 
--===============================
-- Drop Foreign key constraint
--===============================
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='FOREIGN KEY' AND TABLE_SCHEMA='dbo'
		AND TABLE_NAME = 'TableName' AND CONSTRAINT_NAME = 'FK_Name')
BEGIN
	ALTER TABLE [dbo].[TableName] DROP CONSTRAINT [FK_Name]
END
 
--===============================
-- Drop Unique key constraint
--===============================
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='UNIQUE' AND TABLE_SCHEMA='dbo'
		AND TABLE_NAME = 'TableName' AND CONSTRAINT_NAME = 'UNI_Name')
BEGIN
	ALTER TABLE [dbo].[TableNames] DROP CONSTRAINT [UNI_Name]
END
 
--===============================
-- Drop Check constraint
--===============================
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='CHECK' AND TABLE_SCHEMA='dbo'
		AND TABLE_NAME = 'TableName' AND CONSTRAINT_NAME = 'CH_Name')
BEGIN
	ALTER TABLE [dbo].[TableName] DROP CONSTRAINT [CH_Name]
END
 
--===============================
-- Drop a column's Default value constraint
--===============================
DECLARE @ConstraintName VARCHAR(100)
SET @ConstraintName = (SELECT TOP 1 s.name FROM sys.sysobjects s JOIN sys.syscolumns c ON s.parent_obj=c.id
						WHERE s.xtype='d' AND c.cdefault=s.id  AND parent_obj = OBJECT_ID('TableName') AND c.name ='ColumnName')
 
IF @ConstraintName IS NOT NULL
BEGIN
	EXEC ('ALTER TABLE [dbo].[TableName] DROP CONSTRAINT ' + @ConstraintName)
END
 
--===============================
-- Example of how to drop dynamically named Unique constraint
--===============================
DECLARE @ConstraintName VARCHAR(100)
SET @ConstraintName = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS  WHERE CONSTRAINT_TYPE='UNIQUE' AND TABLE_SCHEMA='dbo'
						AND TABLE_NAME = 'TableName' AND CONSTRAINT_NAME LIKE 'FirstPartOfConstraintName%')
 
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='UNIQUE' AND TABLE_SCHEMA='dbo'
		AND TABLE_NAME = 'TableName' AND CONSTRAINT_NAME = @ConstraintName)
BEGIN
	EXEC ('ALTER TABLE [dbo].[TableName] DROP CONSTRAINT ' + @ConstraintName)
END
 
--===============================
-- Check for and drop a temp table
--===============================
IF OBJECT_ID('tempdb..#TableName') IS NOT NULL DROP TABLE #TableName
 
--===============================
-- Drop a stored procedure
--===============================
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='PROCEDURE' AND ROUTINE_SCHEMA='dbo' AND
		ROUTINE_NAME = 'StoredProcedureName')
BEGIN
	DROP PROCEDURE [dbo].[StoredProcedureName]
END
 
--===============================
-- Drop a UDF
--===============================
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='FUNCTION' AND ROUTINE_SCHEMA='dbo' AND  ROUTINE_NAME = 'UDFName')
BEGIN
	DROP FUNCTION [dbo].[UDFName]
END
 
--===============================
-- Drop an Index
--===============================
IF EXISTS (SELECT * FROM SYS.INDEXES WHERE name = 'IndexName')
BEGIN
	DROP INDEX TableName.IndexName
END
 
--===============================
-- Drop a Schema
--===============================
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'SchemaName')
BEGIN
	EXEC('DROP SCHEMA SchemaName')
END