EVRAQRzx

1SET NOCOUNT ON;
2
3DECLARE @sql nvarchar(MAX), @executeScriptsImmediately bit, @id int;
4
5SET @executeScriptsImmediately = 0; -- Set this to 1 to immediately drop and recreate constraints
6
7DECLARE @tbl TABLE ( Id int IDENTITY(1,1), TableName nvarchar(128) NOT NULL, ColumnName nvarchar(128) NOT NULL, 
8	DefaultName nvarchar(128) NOT NULL, DefaultDefinition nvarchar(MAX),
9	NewDefaultName AS ( N'DF_' + TableName + N'_' + ColumnName ),
10	DropText AS (N'IF EXISTS ( SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N''[' + DefaultName + N']'') AND type = ''D'' )
11BEGIN
12	ALTER TABLE [dbo].[' + TableName + N'] DROP CONSTRAINT [' + DefaultName + N']
13END'),
14	CreateText AS (N'IF NOT EXISTS ( SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N''[DF_' + TableName + N'_' + ColumnName + N']'') AND type = ''D'' )
15BEGIN
16	ALTER TABLE [dbo].[' + TableName + N'] ADD CONSTRAINT [DF_' + TableName + N'_' + ColumnName + N'] DEFAULT ' + DefaultDefinition + N' FOR [' + ColumnName + N']
17END'),
18	OrigCreateText AS (N'IF NOT EXISTS ( SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N''[' + DefaultName + N']'') AND type = ''D'' )
19BEGIN
20	ALTER TABLE [dbo].[' + TableName + N'] ADD CONSTRAINT [' + DefaultName + N'] DEFAULT ' + DefaultDefinition + N' FOR [' + ColumnName + N']
21END')
22	);
23
24INSERT INTO @tbl ( TableName, ColumnName, DefaultName, DefaultDefinition )
25SELECT	t.name, c.name, dc.name, dc.definition
26FROM	sys.columns c
27		JOIN sys.tables t ON t.object_id = c.object_id
28		JOIN sys.objects d ON d.object_id = c.default_object_id
29		JOIN sys.types tp ON tp.system_type_id = c.system_type_id
30		JOIN sys.default_constraints dc ON dc.object_id = d.object_id
31WHERE	d.type = N'd'
32-- This filters query to only defaults on varchar data types
33--  AND	tp.name = N'varchar'
34-- This filters query to only defaults that do not match the naming convention
35  AND	dc.name <> N'DF_' + t.name + N'_' + c.name
36ORDER BY t.name, c.name
37
38PRINT N'-------------------------------------------';
39PRINT N'-- Backup of original default contraints';
40PRINT N'-------------------------------------------';
41PRINT N'/*';
42SET @id = ( SELECT TOP 1 Id FROM @tbl ORDER BY Id );
43WHILE ( @id IS NOT NULL )
44BEGIN
45	SELECT @sql = OrigCreateText FROM @tbl WHERE Id = @id;
46	PRINT @sql;
47	SET @id = ( SELECT TOP 1 Id FROM @tbl WHERE Id > @id ORDER BY Id );
48END
49PRINT N'*/';
50PRINT N'';
51
52PRINT N'-------------------------------------------';
53PRINT N'-- Drop default contraints';
54PRINT N'-------------------------------------------';
55SET @id = ( SELECT TOP 1 Id FROM @tbl ORDER BY Id );
56WHILE ( @id IS NOT NULL )
57BEGIN
58	SELECT @sql = DropText FROM @tbl WHERE Id = @id;
59	PRINT @sql;
60	IF (@executeScriptsImmediately = 1) EXEC sp_executesql @sql;
61	SET @id = ( SELECT TOP 1 Id FROM @tbl WHERE Id > @id ORDER BY Id );
62END
63PRINT N'';
64
65PRINT N'-------------------------------------------';
66PRINT N'-- Create new default contraints';
67PRINT N'-------------------------------------------';
68SET @id = ( SELECT TOP 1 Id FROM @tbl ORDER BY Id );
69WHILE ( @id IS NOT NULL )
70BEGIN
71	SELECT @sql = CreateText FROM @tbl WHERE Id = @id;
72	PRINT @sql;
73	IF (@executeScriptsImmediately = 1) EXEC sp_executesql @sql;
74	SET @id = ( SELECT TOP 1 Id FROM @tbl WHERE Id > @id ORDER BY Id );
75END
76PRINT N'';