Search
Close this search box.

SQL Server script commands to check if object exists and drop it

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

Print | posted on Friday, September 14, 2012 12:53 PM |

This article is part of the GWB Archives. Original Author: Daniel Schroeder

Related Posts