There are two examples, in both I add a column to each tables except the spnet membership tables.
Example :
Add the “CreatedDate” column to all tables :
print 'Add [CreatedDate] Column for each user table' EXEC sp_msforeachtable ' -- add column exec('' declare @tableName as nvarchar(max) set @tableName = ''''_?_'''' IF (CHARINDEX(''''aspnet_'''',@tableName) = 0) -- no aspnet membership BEGIN IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''''?'''') AND type in (N''''U'''')) BEGIN IF columnproperty(object_id(''''?''''), ''''CreatedDate'''', ''''ColumnId'''') is null BEGIN ALTER TABLE ? ADD [CreatedDate] datetime NOT NULL END END END '') ' ; print ' [CreatedDate] Column is created for each user table'
Example 2 :
Add the “CreatedUserID” column to all tables and enable constraints. For the example, the reference use the aspnet_Users table
print 'Add [CreatedUserID] Column for each user table' EXEC sp_msforeachtable ' -- add column exec('' declare @tableName as nvarchar(max) set @tableName = ''''_?_'''' IF (CHARINDEX(''''aspnet_'''',@tableName) = 0) -- no aspnet membership BEGIN IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''''?'''') AND type in (N''''U'''')) BEGIN IF columnproperty(object_id(''''?''''), ''''CreatedUserID'''', ''''ColumnId'''') is null BEGIN ALTER TABLE ? ADD [CreatedUserID] uniqueidentifier NOT NULL END END END '') -- add constraint declare @constraintName as nvarchar(max) set @constraintName = ''FK_?_CreatedUserID'' IF (CHARINDEX(''aspnet_'',@constraintName) = 0) -- no aspnet membership BEGIN set @constraintName = REPLACE(@constraintName,''[dbo].['','''') set @constraintName = REPLACE(@constraintName,'']'','''') IF NOT EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N''''+@constraintName+'''') AND type = (N''F'')) BEGIN exec('' ALTER TABLE ? ADD CONSTRAINT [''+@constraintName+''] FOREIGN KEY([CreatedUserID]) REFERENCES [aspnet_Users]([UserID]) '') END END ' ; print ' [CreatedUserID] Column is created for each user table'
The difficulty is to add the right numbers of ‘ .
Incoming search terms:
- sp_msforeachtable
- abilitynhy
- informationujb
- live95n
- parkb2u
- pictured9pn
- shoottjy