Stupid SQL Tricks - Using Computed Columns for Code Generation

I recently had a need to drop and recreate all the default constraints on some tables so I could convert the data types of from varchar to nvarchar. The constraints were originally created allowing SQL Server to automatically name them. So, they looked something like: DF__TableName__Colu__2BE6BFCF. Maybe this doesn't bother you. To me, it's an abomination.

SET NOCOUNT ON;

DECLARE @sql nvarchar(MAX), @executeScriptsImmediately bit, @id int;

SET @executeScriptsImmediately = 0; -- Set this to 1 to immediately drop and recreate constraints

DECLARE @tbl TABLE ( Id int IDENTITY(1,1), TableName nvarchar(128) NOT NULL, ColumnName nvarchar(128) NOT NULL,  
    DefaultName nvarchar(128) NOT NULL, DefaultDefinition nvarchar(MAX),
    NewDefaultName AS ( N'DF_' + TableName + N'_' + ColumnName ),
    DropText AS (N'IF EXISTS ( SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N''[' + DefaultName + N']'') AND type = ''D'' )
BEGIN  
    ALTER TABLE [dbo].[' + TableName + N'] DROP CONSTRAINT [' + DefaultName + N']
END'),  
    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'' )
BEGIN  
    ALTER TABLE [dbo].[' + TableName + N'] ADD CONSTRAINT [DF_' + TableName + N'_' + ColumnName + N'] DEFAULT ' + DefaultDefinition + N' FOR [' + ColumnName + N']
END'),  
    OrigCreateText AS (N'IF NOT EXISTS ( SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N''[' + DefaultName + N']'') AND type = ''D'' )
BEGIN  
    ALTER TABLE [dbo].[' + TableName + N'] ADD CONSTRAINT [' + DefaultName + N'] DEFAULT ' + DefaultDefinition + N' FOR [' + ColumnName + N']
END')  
    );

INSERT INTO @tbl ( TableName, ColumnName, DefaultName, DefaultDefinition )  
SELECT    t.name, c.name, dc.name, dc.definition  
FROM    sys.columns c  
        JOIN sys.tables t ON t.object_id = c.object_id
        JOIN sys.objects d ON d.object_id = c.default_object_id
        JOIN sys.types tp ON tp.system_type_id = c.system_type_id
        JOIN sys.default_constraints dc ON dc.object_id = d.object_id
WHERE    d.type = N'd'  
-- This filters query to only defaults on varchar data types
--  AND    tp.name = N'varchar'
-- This filters query to only defaults that do not match the naming convention
  AND    dc.name <> N'DF_' + t.name + N'_' + c.name
ORDER BY t.name, c.name

PRINT N'-------------------------------------------';  
PRINT N'-- Backup of original default contraints';  
PRINT N'-------------------------------------------';  
PRINT N'/*';  
SET @id = ( SELECT TOP 1 Id FROM @tbl ORDER BY Id );  
WHILE ( @id IS NOT NULL )  
BEGIN  
    SELECT @sql = OrigCreateText FROM @tbl WHERE Id = @id;
    PRINT @sql;
    SET @id = ( SELECT TOP 1 Id FROM @tbl WHERE Id > @id ORDER BY Id );
END  
PRINT N'*/';  
PRINT N'';

PRINT N'-------------------------------------------';  
PRINT N'-- Drop default contraints';  
PRINT N'-------------------------------------------';  
SET @id = ( SELECT TOP 1 Id FROM @tbl ORDER BY Id );  
WHILE ( @id IS NOT NULL )  
BEGIN  
    SELECT @sql = DropText FROM @tbl WHERE Id = @id;
    PRINT @sql;
    IF (@executeScriptsImmediately = 1) EXEC sp_executesql @sql;
    SET @id = ( SELECT TOP 1 Id FROM @tbl WHERE Id > @id ORDER BY Id );
END  
PRINT N'';

PRINT N'-------------------------------------------';  
PRINT N'-- Create new default contraints';  
PRINT N'-------------------------------------------';  
SET @id = ( SELECT TOP 1 Id FROM @tbl ORDER BY Id );  
WHILE ( @id IS NOT NULL )  
BEGIN  
    SELECT @sql = CreateText FROM @tbl WHERE Id = @id;
    PRINT @sql;
    IF (@executeScriptsImmediately = 1) EXEC sp_executesql @sql;
    SET @id = ( SELECT TOP 1 Id FROM @tbl WHERE Id > @id ORDER BY Id );
END  
PRINT N'';  

View and download this script from GitHub.

Granted, you could accomplish the same thing by moving the "templates" to the SELECT statement that inserts rows into the the table variable. I just felt especially nerdy using computed columns. I like being especially nerdy.