Thursday, March 31, 2011

Adding Columns to all Tables

The following T-SQL script loops through all schemas and adds four record audit fields to all database tables:

DECLARE @schema nvarchar(255)
,@command NVARCHAR(2000)

DECLARE crs CURSOR FOR
SELECT name + '.INFORMATION_SCHEMA'
FROM sys.Databases
WHERE name NOT IN ('master','tempdb','model','msdb'
,'ReportServer','ReportServerTempDB','Sharepoint_ReportServer'
,'Sharepoint_ReportServerTempDB','BIDocumenter')

OPEN crs

FETCH NEXT FROM crs
INTO @schema

SET @Command = N'DECLARE @TempTable TABLE (tblName nvarchar(255));
DECLARE @cmd NVARCHAR(2000);
DECLARE @tblName nvarchar(255);
INSERT INTO @TempTable
SELECT TABLE_CATALOG + ''.'' + TABLE_SCHEMA + ''.['' + TABLE_NAME + '']''
FROM '
+ @schema + '.TABLES
WHERE TABLE_TYPE = ''BASE TABLE'' AND TABLE_NAME NOT IN (''sysdiagrams'',''dtproperties'')
ORDER BY TABLE_NAME;
DECLARE c CURSOR FOR SELECT * FROM @TempTable;


OPEN c;

FETCH NEXT FROM c INTO @tblName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = ''ALTER TABLE '''
+ ' + @tblName + '' ADD Created_By NVARCHAR(255) NULL DEFAULT (user_name());'';

PRINT @cmd;

EXEC sp_executesql @cmd;

SET @cmd = ''ALTER TABLE '''
+ ' + @tblName + '' ADD Date_Created DATETIME NULL DEFAULT (GETDATE());'';

PRINT @cmd;

EXEC sp_executesql @cmd;

SET @cmd = ''ALTER TABLE '''
+ ' + @tblName + '' ADD Modified_By NVARCHAR(255) NULL;'';

PRINT @cmd;

EXEC sp_executesql @cmd;

SET @cmd = ''ALTER TABLE '''
+ ' + @tblName + '' ADD Date_Modified DATETIME NULL;'';

PRINT @cmd;

EXEC sp_executesql @cmd;

FETCH NEXT FROM c INTO @tblName;

END

CLOSE c;

DEALLOCATE c;'

WHILE @@FETCH_STATUS = 0
BEGIN

PRINT @Command

PRINT ''

EXEC sp_executesql @Command

PRINT ''

FETCH NEXT FROM crs

INTO @schema

SET @Command = N'DECLARE @TempTable TABLE (tblName nvarchar(255));
DECLARE @cmd NVARCHAR(2000);
DECLARE @tblName nvarchar(255);
INSERT INTO @TempTable     
SELECT TABLE_CATALOG + ''.'' + TABLE_SCHEMA + ''.['' + TABLE_NAME + '']''
FROM '
+ @schema + '.TABLES
WHERE TABLE_TYPE = ''BASE TABLE'' AND TABLE_NAME NOT IN (''sysdiagrams'',''dtproperties'')
ORDER BY TABLE_NAME;
DECLARE c CURSOR FOR SELECT * FROM @TempTable;


OPEN c;

FETCH NEXT FROM c INTO @tblName;

WHILE @@FETCH_STATUS = 0
BEGIN

SET @cmd = ''ALTER TABLE '''
+ ' + @tblName + '' ADD CreatedBy NVARCHAR(255) NULL (user_name());'';

PRINT @cmd;

EXEC sp_executesql @cmd;

SET @cmd = ''ALTER TABLE '''
+ ' + @tblName + '' ADD CreatedBDate DATETIME NULL DEFAULT (GETDATE());'';

PRINT @cmd;

EXEC sp_executesql @cmd;

SET @cmd = ''ALTER TABLE '''
+ ' + @tblName + '' ADD ModifieddBy NVARCHAR(255) NULL;'';

PRINT @cmd;

EXEC sp_executesql @cmd;

SET @cmd = ''ALTER TABLE '''
+ ' + @tblName + '' ADD ModifiedDate DATETIME NULL;'';

PRINT @cmd;

EXEC sp_executesql @cmd;

FETCH NEXT FROM c INTO @tblName;

END

CLOSE c;

DEALLOCATE c;'

END

CLOSE crs

DEALLOCATE crs

If you find this script useful please donate generously.

No comments:

Post a Comment