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: –

 

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

  

How To… Changing TEXT or NTEXT field to a VARCHAR field

As there is no direct way of converting a TEXT or NTEXT field to VARCHAR, it is necessary to rename your existing field, create a new field of the required size and then copy the existing data across, converting it, and then dropping the old column.
 
The following sample shows how to achieve this: –

EXEC

sp_rename ‘table_name.original_field_name’, ‘temp_field_name’, ‘COLUMN’
go
ALTER TABLE [table_name] ADD [original_field_name] VARCHAR(1000)
go
UPDATE [table_name] SET [original_field_name] = SUBSTRING([temp_field_name], 1, 1000)
go
ALTER TABLE [table_name] DROP COLUMN [temp_field_name]
go