Yesterday, someone asked me how to alter every table of a database.
The script below declares a cursor, fills it with the list of tables and executes an alter statement while looping thru the cursor.
-- Declare a cursor for the list of all the tables in the database
DECLARE ListOfTables CURSOR FOR
SELECT Table_Name
FROM Information_Schema.Tables
OPEN ListOfTables
DECLARE @TableName Varchar(100)
DECLARE @QueryString Varchar(MAX)
DECLARE @Column Varchar(100)
-- Replace the alter statement here:
SET @Column = 'TestKolom3 VARCHAR(20) NOT NULL'
-- Execute alter statement for every table
FETCH NEXT FROM ListOfTables INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @QueryString = 'ALTER TABLE ' + @TableName + ' ADD ' + @Column
print 'Executing Query : ' + @QueryString
EXEC(@QueryString)
FETCH NEXT FROM ListOfTables INTO @TableName
END
CLOSE ListOfTables
DEALLOCATE ListOfTables
Enjoy!