Database add two new columns automatically

DECLARE Alter_tables_cursor CURSOR
   FOR
   select table_name from information_schema.tables where table_name<>'dtProperties' and table_type<>'VIEW'
OPEN Alter_tables_cursor
DECLARE @tablename sysname
FETCH NEXT FROM Alter_tables_cursor INTO @tablename
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
 PRINT 'Altering  Primary Key -->'+@tablename+' '
 EXEC('ALTER TABLE '+@tablename+' ADD  rowguid  DATATYPE NULL ')
 EXEC('ALTER TABLE '+@tablename+' ADD  ModifiedDate   DATETIME NULL ')
 FETCH NEXT FROM Alter_tables_cursor INTO @tablename
 
END
PRINT 'All user-defined tables have been Altered.'
DEALLOCATE Alter_tables_cursor

Select allOpen in new window

Make sure u add the column as mentioned above with allow NULLs as NULL or else it will generate an error. You cannot add columsn with NOT NULL to an existing table.

Cheers

DECLARE Alter_tables_cursor CURSOR
   FOR
   select table_name from information_schema.tables where table_name<>'dtProperties' and table_type<>'VIEW'
OPEN Alter_tables_cursor
DECLARE @tablename sysname
FETCH NEXT FROM Alter_tables_cursor INTO @tablename
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
 PRINT 'Altering  Primary Key -->'+@tablename+' '
 EXEC('ALTER TABLE '+@tablename+' ADD  rowguid  DATATYPE NULL ')
 EXEC('ALTER TABLE '+@tablename+' ADD  ModifiedDate   DATETIME NULL ')
 EXEC('UPDATE  '+@tablename+' SET rowguid = ' & newid() & "'"')
 EXEC('UPDATE  '+@tablename+' SET ModifiedDate = ' & date() & "'"')
 FETCH NEXT FROM Alter_tables_cursor INTO @tablename
 
END
PRINT 'All user-defined tables have been Altered.'
DEALLOCATE Alter_tables_cursor

ah, sorry only need to add them to "physcial" tables only not in any other table view etc

ROWGUID is used for database replication, it needs the default value set to "newid()" and replication to be turned on (see example below)
ModifiedDate is used to track changes made to the table, it needs the default value set to "getdate()".

Example of create table Query with database replication turn on ROWGUIDCOL
--------------------------------------------------------------------------
create table TableA (
TableAID int identity not null,
Name nvarchar(50) not null,
rowguid uniqueidentifier *ROWGUIDCOL* default (newid()) not null unique, <--- need this to be added to every physical table (as shown)
ModifiedDate datetime default (getdate()) not null,     <--- need this to be added to every physical table (as shown)
TableBID int null, constraint PK_TableA primary key (TableAID));

Select allOpen in new window

Requirements:

Add the following two columns to each table in the database, (excluding view tables).


ColumnName: Modified
DataType: datetime
Default value: getdate()
Unique: yes

ColumnName: rowguid
DataType: uniqueidentifier
Default value: newid()
Unique: yes
(with database replication turned on)

solution has not been tested.