declare @UserDataType varchar(20) ,@DefaultUser int ,@CreatedByField varchar(50) ,@CreatedOnField varchar(50) ,@UpdatedByField varchar(50) ,@UpdatedOnField varchar(50) select @UserDataType = 'int' ,@DefaultUser = 1 ,@CreatedByField = 'CreatedBy' ,@CreatedOnField = 'CreatedOn' ,@UpdatedByField = 'UpdatedBy' ,@UpdatedOnField = 'UpdatedOn' ;with MyTables as ( select name from sys.objects where 1=1 and type = 'U' and name not like 'aspnet[_]%' and name not in ('MenuItem','MenuItemRole') ) /*- Comment Header ------------------------------------------------------------------------------*/ select name ,sequence = 0 ,sql = '/*- ' + name + ' ' + replicate('-',92-len(name)) + '*/' from MyTables where Name not in (select o.name from sys.objects o inner join sys.columns c on o.object_id = c.object_id where o.type = 'U' and c.name=@CreatedByField) or Name not in (select o.name from sys.objects o inner join sys.columns c on o.object_id = c.object_id where o.type = 'U' and c.name=@CreatedOnField) or Name not in (select o.name from sys.objects o inner join sys.columns c on o.object_id = c.object_id where o.type = 'U' and c.name=@UpdatedByField) or Name not in (select o.name from sys.objects o inner join sys.columns c on o.object_id = c.object_id where o.type = 'U' and c.name=@UpdatedOnField) /*- CreatedBy, Part 1 (Add nullable column) -----------------------------------------------------*/ union select name ,sequence = 10 ,sql = 'ALTER TABLE [' + name + '] ADD ' + @CreatedByField + ' ' + @UserDataType + ' null' from MyTables where 1=1 and Name not in (select o.name from sys.objects o inner join sys.columns c on o.object_id = c.object_id where o.type = 'U' and c.name=@CreatedByField) /*- CreatedBy, Part 2 (GO) ----------------------------------------------------------------------*/ union select name ,sequence = 32 ,sql = 'GO' from MyTables where 1=1 and Name not in (select o.name from sys.objects o inner join sys.columns c on o.object_id = c.object_id where o.type = 'U' and c.name=@CreatedByField) /*- CreatedBy, Part 3 (Update column with default user) -----------------------------------------*/ union select name ,sequence = 33 ,sql = 'UPDATE [' + name + '] SET ' + @CreatedByField + ' = ' + convert(varchar(255), @DefaultUser) from MyTables where 1=1 and Name not in (select o.name from sys.objects o inner join sys.columns c on o.object_id = c.object_id where o.type = 'U' and c.name=@CreatedByField) /*- CreatedBy, Part 4 (Alter column to be non-nullable) -----------------------------------------*/ union select name ,sequence = 34 ,sql = 'ALTER TABLE [' + name + '] ALTER COLUMN ' + @CreatedByField + ' ' + @UserDataType + ' not null' from MyTables where 1=1 and Name not in (select o.name from sys.objects o inner join sys.columns c on o.object_id = c.object_id where o.type = 'U' and c.name=@CreatedByField) /*- CreatedOn -----------------------------------------------------------------------------------*/ union select name ,sequence = 20 ,sql = 'ALTER TABLE [' + name + '] ADD ' + @CreatedOnField + ' datetime not null default getdate()' from MyTables where 1=1 and Name not in (select o.name from sys.objects o inner join sys.columns c on o.object_id = c.object_id where o.type = 'U' and c.name=@CreatedOnField) /*- UpdatedBy, Part 1 ---------------------------------------------------------------------------*/ union select name ,sequence = 31 ,sql = 'ALTER TABLE [' + name + '] ADD ' + @UpdatedByField + ' ' + @UserDataType + ' null' from MyTables where 1=1 and Name not in (select o.name from sys.objects o inner join sys.columns c on o.object_id = c.object_id where o.type = 'U' and c.name=@UpdatedByField) /*- UpdatedBy, Part 2 ---------------------------------------------------------------------------*/ union select name ,sequence = 32 ,sql = 'GO' from MyTables where 1=1 and Name not in (select o.name from sys.objects o inner join sys.columns c on o.object_id = c.object_id where o.type = 'U' and c.name=@UpdatedByField) /*- UpdatedBy, Part 3 ---------------------------------------------------------------------------*/ union select name ,sequence = 33 ,sql = 'UPDATE [' + name + '] SET ' + @UpdatedByField + ' = ' + convert(varchar(255), @DefaultUser) from MyTables where 1=1 and Name not in (select o.name from sys.objects o inner join sys.columns c on o.object_id = c.object_id where o.type = 'U' and c.name=@UpdatedByField) /*- UpdatedBy, Part 4 ---------------------------------------------------------------------------*/ union select name ,sequence = 34 ,sql = 'ALTER TABLE [' + name + '] ALTER COLUMN ' + @UpdatedByField + ' ' + @UserDataType + ' not null' from MyTables where 1=1 and Name not in (select o.name from sys.objects o inner join sys.columns c on o.object_id = c.object_id where o.type = 'U' and c.name=@UpdatedByField) /*- UpdatedOn -----------------------------------------------------------------------------------*/ union select name ,sequence = 40 ,sql = 'ALTER TABLE [' + name + '] ADD ' + @UpdatedOnField + ' datetime not null default getdate()' from MyTables where 1=1 and Name not in (select o.name from sys.objects o inner join sys.columns c on o.object_id = c.object_id where o.type = 'U' and c.name=@UpdatedOnField) /*-----------------------------------------------------------------------------------------------*/ order by name ,sequence
declare @UserDataType varchar(20) ,@DefaultUser int ,@CreatedByField varchar(50) ,@CreatedOnField varchar(50) ,@UpdatedByField varchar(50) ,@UpdatedOnField varchar(50) ,@UserTable varchar(50) ,@PrimaryKeyField varchar(50) select @UserDataType = 'int' ,@DefaultUser = 1 ,@CreatedByField = 'CreatedBy' -- NO BRACKETS, SPACES, ,@UpdatedByField = 'UpdatedBy' -- OR PERIODS! ,@UserTable = 'AppUser' -- ARE ALLOWED IN ,@PrimaryKeyField = 'AppUserID' -- ANY OF THESE ;with MyTablesA as ( select name ,FkeyCreated = 'FK_' + name + '_' + @UserTable + '_' + @CreatedByField ,FkeyUpdated = 'FK_' + name + '_' + @UserTable + '_' + @UpdatedByField from sys.objects where 1=1 and type = 'U' and name not like 'aspnet[_]%' and Name in ( select o.name from sys.objects o inner join sys.columns c on o.object_id = c.object_id where 1=1 and o.type = 'U' and c.name in (@CreatedByField, @UpdatedByField) ) ) /*-----------------------------------------------------------------------------------------------*/ ,MyTables as ( select * from MyTablesA where FkeyCreated not in (select name from sys.objects) or FkeyUpdated not in (select name from sys.objects) ) /*- Comment Header ------------------------------------------------------------------------------*/ select name ,sequence = 0 ,sql = '/*- ' + name + ' ' + replicate('-',92-len(name)) + '*/' from MyTables /*- CreatedBy -----------------------------------------------------------------------------------*/ union select name ,sequence = 10 ,sql = 'ALTER TABLE [' + name + '] with check add constraint [' + FkeyCreated + '] foreign key (' + @CreatedByField + ') references ' + @UserTable + ' (' + @PrimaryKeyField + ')' from MyTables where 1=1 and Name in (select o.name from sys.objects o inner join sys.columns c on o.object_id = c.object_id where o.type = 'U' and c.name=@CreatedByField) and FkeyCreated not in (select name from sys.objects) /*- UpdatedBy -----------------------------------------------------------------------------------*/ union select name ,sequence = 20 ,sql = 'ALTER TABLE [' + name + '] with check add constraint [' + FkeyUpdated + '] foreign key (' + @UpdatedByField + ') references ' + @UserTable + ' (' + @PrimaryKeyField + ')' from MyTables where 1=1 and Name in (select o.name from sys.objects o inner join sys.columns c on o.object_id = c.object_id where o.type = 'U' and c.name=@UpdatedByField) and FkeyUpdated not in (select name from sys.objects)
ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.