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 |