SQL Server script commands to check if object exists and drop it
Posted
by deadlydog
on Geeks with Blogs
See other posts from Geeks with Blogs
or by deadlydog
Published on Fri, 14 Sep 2012 17:53:10 GMT
Indexed on
2012/09/14
21:38 UTC
Read the original article
Hit count: 394
Filed under:
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 © Geeks with Blogs or respective owner
