How to use the undocumented stored procedure sp_msforeachtable to add a column to all tables in a database ?

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

About Sam Beauvois

Application Developer, .NET enthusiast since 2004, I'm interested in technology watch, usability, code quality, patterns & practices, UX, ...

1 comment

  1. ALTER PROCEDURE [dbo].[PHD_SearchPhDStudentForUpdating]

    @Faculty_ID int = null,
    @Seat_No int = null,
    @PHD_Subject_ID bigint = null,
    @Applicant_Name varchar(225) = null,
    @Entrance_Exemption char(1) = null,
    @Bank_ID bigint = null,
    @DD_No varchar(6) = null,
    @Gender char(1) = null,
    @Category_ID bigint = null,
    @Form_No bigint = null,
    @Date_Of_Birth varchar(10) = null,
    @page int = null,
    @pagelength int = null,
    @sortfield varchar(100) = null,
    @IsDescending varchar(10) = null

    AS

    DECLARE @rowcount int
    DECLARE @innerrows int
    DECLARE @sortdesc varchar(100)
    DECLARE @sortasc varchar(100)
    DECLARE @a varchar(6)
    DECLARE @b varchar(6)
    DECLARE @SQL varchar(max)
    DECLARE @CountSql nvarchar(2000)
    DECLARE @Where varchar(200)
    SET @Where = ”
    SET @SQL = ”

    IF @IsDescending=’0′
    BEGIN
    SET @a = ‘ DESC ‘
    SET @b = ‘ ASC ‘
    END
    ELSE
    BEGIN
    SET @a = ‘ ASC ‘
    SET @b = ‘ DESC ‘
    END

    IF charindex(@sortfield, ‘ Form_No’) > 0
    BEGIN
    set @sortdesc = ”
    set @sortasc = ”
    END
    ELSE
    BEGIN
    set @sortdesc = ‘, Form_No ‘ + @a
    set @sortasc = ‘, Form_No ‘ + @b
    END

    IF(@Form_No IS NOT NULL)
    SET @Where = @Where + ‘Form_No = ‘ + cast(@Form_No as varchar) + ‘ AND ‘

    IF(@Seat_No IS NOT NULL)
    SET @Where = @Where + ‘Seat_No = ‘ + cast(@Seat_No as varchar) + ‘ AND ‘

    IF(@Faculty_ID IS NOT NULL)
    SET @Where = @Where + ‘PDE.Faculty_ID= ‘ + cast(@Faculty_ID as varchar) + ‘ AND ‘

    IF(@Applicant_Name IS NOT NULL)
    SET @Where = @Where + ‘Applicant_Name = ”’ + @Applicant_Name + ”’ ‘ + ‘ AND ‘

    IF(@Gender IS NOT NULL)
    SET @Where = @Where + ‘Gender = ”’ + @Gender + ”’ ‘ + ‘ AND ‘

    IF(@Date_Of_Birth IS NOT NULL)
    SET @Where = @Where + ‘Date_Of_Birth = ”’ + @Date_Of_Birth + ”’ ‘ + ‘ AND ‘

    IF(@PHD_Subject_ID IS NOT NULL)
    SET @Where = @Where + ‘PDE.PHD_Subject_ID = ‘ + cast(@PHD_Subject_ID as varchar) + ‘ AND ‘

    IF(@Category_ID IS NOT NULL)
    SET @Where = @Where + ‘Category_ID = ‘ + cast(@Category_ID as varchar) + ‘ AND ‘

    IF(@DD_No IS NOT NULL)
    SET @Where = @Where + ‘DD_No= ”’ + @DD_No + ”’ ‘ + ‘ AND ‘

    IF(@Entrance_Exemption IS NOT NULL)
    SET @Where = @Where + ‘Entrance_Exemption = ”’ + @Entrance_Exemption + ”’ ‘ + ‘ AND ‘

    IF(@Bank_ID IS NOT NULL)
    SET @Where = @Where + ‘Bank_ID = ‘ + cast(@Bank_ID as varchar) + ‘ AND ‘

    SET @CountSql =
    N’ SELECT @rowcount = COUNT(*)
    FROM PhD_Entrance PDE
    INNER JOIN PHD_Subject_Master AS PSM
    ON PSM.PHD_Subject_ID = PDE.PHD_Subject_ID
    AND PSM.Deleted = 0
    WHERE ‘+ @Where + ‘ 1 = 1′

    exec sp_executesql @CountSql, N’@rowcount Int OUTPUT’, @rowcount = @rowcount output

    SET @innerrows = @rowcount – (@page * @pagelength)

    print @innerrows

    SET @SQL =
    ‘SELECT TOP (‘ + str(@pagelength) + ‘) Tmp.*,’+ CONVERT(varchar(9), @rowcount) + ‘As totalRows
    FROM
    (
    SELECT
    TOP (‘ + str(@innerrows) + ‘)
    PDE.Form_No,
    PDE.Applicant_Name,
    IsNull(RIGHT(”0000” + CAST(PDE.Seat_No as varchar),5),”–”)as Seat_No,
    PSM.PHD_Subject_Name,
    COALESCE(cast(Total_Marks as varchar)+ ”/” + cast(Out_Of_Marks as varchar) ,”–”) as Marks
    FROM PhD_Entrance PDE
    INNER JOIN PHD_Subject_Master AS PSM
    ON PSM.PHD_Subject_ID = PDE.PHD_Subject_ID
    AND PSM.Deleted = 0
    WHERE ‘+ @Where + ‘ 1 = 1
    ORDER BY ‘+ @sortfield + @a + @sortdesc + ‘
    )Tmp
    ORDER BY ‘ + @sortfield + @b + @sortasc

    EXEC(@SQL)

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>