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

Posted on by
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: –

 

USE

[MyDB]
GO

DECLARE

@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
DECLARE @MaxTableCount bigint

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

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

WHILE (@Counter <= @MaxTableCount)
BEGIN

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

END

  

Leave a Reply

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

Gravatar
WordPress.com Logo

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

Facebook photo

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

Cancel

Connecting to %s