How To… Adding a standard field to all tables in a Large SQL Database

On occasion you may need to add a standard field to a group of tables within your database or to them all.
The following script sample shows how to add a standard field to all tables, simply filtering the table list will allow this to be aplied to a selection of tables… In this sample I show adding a Microsoft TimeStamp field to all tables within a database: –





@TableNames TABLE (TableTempID bigint IDENTITY(1,1) PRIMARY KEY CLUSTERED, TableName varchar(2000))

INSERT INTO @TableNames (TableName) (select [name] from dbo.sysobjects where OBJECTPROPERTY(id, N‘IsUserTable’) = 1 AND OBJECTPROPERTY(id,‘ismsshipped’) = 0)

DECLARE @Counter bigint
@MaxTableCount bigint

SELECT @MaxTableCount = max(TableTempID) from @TableNames
SET @Counter = 1

DECLARE @TableName Varchar(2000)
DECLARE @sqlQuery nVarchar(4000)

WHILE (@Counter <= @MaxTableCount)

    SELECT @TableName = ltrim(rtrim(TableName)) FROM @TableNames WHERE TableTempID = @Counter
    SET @sqlQuery = ‘ALTER TABLE [‘ + @TableName +
    EXECUTE sp_executesql @sqlQuery
    SET @Counter = @Counter + 1



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s