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