Adding a column to a table dynamically against a database?

I have the folowing databases DB1,DB2,DB3,D4,DB5......

I have to loop through each of the databases and find out if the database has a tablename with the word 'Documents'( like 'tbdocuments' or 'tbemployeedocuments' and so on.....)

If the tablename having the word 'Documents' is found in that database i have to add a column named 'IsValid varchar(100)' against that table in that database and there can be more than 1 'Documents' table in a database.

can someone show me the script to do it?

Thanks.

This script should do the trick, the specific line that does the ALTER TABLE is commented you would have to remove the comment. Additionaly, I also added a commented line that lets you specify which databases to search, if this line is documented it will search all databases except system databases. IMPORTANT: The script is written for SQL 2005, if you need it to work in 2000 just reply on the thread the change should be fairly simple.

 -------------------------------------------

DECLARE ALL_DATABASESCURSORREAD_ONLYFOR SELECT nameFROM SYS.DATABASESWHERE name NOT IN ('MASTER','MSDB','MODEL','TEMPDB','DISTRIBUTION')
--ANDname IN ('DB1','DB2','DB3') -- UNCOMMENT THIS LINE TO SPECIFY SPECIFIC DATABASES OTHERWISE ALLDECLARE @.DB_NAMENVARCHAR(255)DECLARE @.TABLE_NAMENVARCHAR(255)-- OTHERDECLARE @.SQL1NVARCHAR(MAX)DECLARE @.SQL2NVARCHAR(MAX)-- TEMPORARY TABLE TO HOLD ALL TABLES, FOR A DATABASEDECLARE @.TEMPORARY_SYS_TABLESTABLE (TABLE_NAMENVARCHAR(255))OPEN ALL_DATABASESFETCH NEXT FROM ALL_DATABASESINTO @.DB_NAMEWHILE (@.@.fetch_status <> -1)BEGINIF (@.@.fetch_status <> -2)BEGIN-- OUTPUT DATABASE NAMEPRINT'BEG------ (' + @.DB_NAME + ') --------'-- BUILD QUERY FOR TABLESSET @.SQL1 = 'SELECT name FROM ['+ @.DB_NAME +'].[SYS].[TABLES]WHERE NAMELIKE''%DOCUMENTS%'''-- TRUNCATEDELETE @.TEMPORARY_SYS_TABLES-- LOAD WITH DATAINSERT @.TEMPORARY_SYS_TABLES(TABLE_NAME)EXECUTE SP_EXECUTESQL @.SQL1DECLARE ALL_MMR_TABLES CURSORREAD_ONLYFOR SELECT TABLE_NAMEFROM @.TEMPORARY_SYS_TABLESOPEN ALL_MMR_TABLESFETCH NEXT FROM ALL_MMR_TABLES INTO @.TABLE_NAMEWHILE (@.@.fetch_status <> -1)BEGINIF (@.@.fetch_status <> -2)BEGIN-- BUILD SQLSET @.SQL2 = 'USE' + @.DB_NAME + ';GO;ALTER TABLE' + @.TABLE_NAME + 'ADD IS_VALIDNVARCHAR(100);'-- PRINTPRINT @.SQL2-- EXECUTE-- EXECUTE SP_EXECUTESQL @.SQL2 -- REMOVE THIS COMMENT TO PERFORM THE ADD OF THE COLUMNENDFETCH NEXT FROM ALL_MMR_TABLES INTO @.TABLE_NAMEENDCLOSE ALL_MMR_TABLESDEALLOCATE ALL_MMR_TABLES-- OUTPUT DATABASE NAMEPRINT 'END------ (' + @.DB_NAME + ') --------'FETCH NEXT FROM ALL_DATABASESINTO @.DB_NAMEENDENDCLOSE ALL_DATABASESDEALLOCATE ALL_DATABASES

-------------------------------------------

Hope this helps,

|||

Thanks for replying.

I am using SQL Server 2000.

I tried executing the statement as per your script above, in master database

SET @.SQL2 = 'USE' + @.DB_NAME + ';GO;ALTER TABLE' + @.TABLE_NAME + 'ADD IS_VALIDNVARCHAR(100);'
PRINT @.SQL2
EXECUTE SP_EXECUTESQL @.SQL2

and it is throwing this error:

Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near 'GO'.

|||

Apparently all that is required is that you remove the GO. The documentation for the Stored Procedure SP_EXECUTESQL says it can only operate within a single BATCH, I guess that is why GO is not allowed.

SET @.SQL2 = 'USE' + @.DB_NAME + ';ALTER TABLE' + @.TABLE_NAME + 'ADD IS_VALIDNVARCHAR(100);'

Full Script for SQL 2000, and you should definitely run from 'master' database.

___________________________________________________________________________________________

DECLARE ALL_DATABASESCURSORREAD_ONLYFOR SELECT nameFROM MASTER.DBO.SYSDATABASES--WHERE name NOT IN ('MASTER','MSDB','MODEL','TEMPDB','DISTRIBUTION')--AND name IN ('DB1','DB2','DB3') -- UNCOMMENT THIS LINE TO SPECIFY SPECIFIC DATABASES OTHERWISE ALLDECLARE @.DB_NAMENVARCHAR(255)DECLARE @.TABLE_NAMENVARCHAR(255)-- OTHERDECLARE @.SQL1NVARCHAR(MAX)DECLARE @.SQL2NVARCHAR(MAX)-- TEMPORARY TABLE TO HOLD ALL TABLES, FOR A DATABASEDECLARE @.TEMPORARY_SYS_TABLESTABLE (TABLE_NAMENVARCHAR(255))OPEN ALL_DATABASESFETCH NEXT FROM ALL_DATABASESINTO @.DB_NAMEWHILE (@.@.fetch_status <> -1)BEGINIF (@.@.fetch_status <> -2)BEGIN-- OUTPUT DATABASE NAMEPRINT'BEG------ (' + @.DB_NAME + ') --------'-- BUILD QUERY FOR TABLESSET @.SQL1 = 'SELECT TABLE_NAMEFROM ['+ @.DB_NAME +'].[INFORMATION_SCHEMA].[TABLES]WHERE TABLE_NAMELIKE''%DOCUMENTS%'''-- TRUNCATEDELETE @.TEMPORARY_SYS_TABLES-- LOAD WITH DATAINSERT @.TEMPORARY_SYS_TABLES(TABLE_NAME)EXECUTE SP_EXECUTESQL @.SQL1DECLARE ALL_MMR_TABLES CURSORREAD_ONLYFOR SELECT TABLE_NAMEFROM @.TEMPORARY_SYS_TABLESOPEN ALL_MMR_TABLESFETCH NEXT FROM ALL_MMR_TABLES INTO @.TABLE_NAMEWHILE (@.@.fetch_status <> -1)BEGINIF (@.@.fetch_status <> -2)BEGIN-- BUILD SQLSET @.SQL2 = 'USE' + @.DB_NAME + ';ALTER TABLE' + @.TABLE_NAME + 'ADD IS_VALIDNVARCHAR(100);'-- PRINTPRINT @.SQL2-- EXECUTE-- EXECUTE SP_EXECUTESQL @.SQL2 -- REMOVE THIS COMMENT TO PERFORM THE ADD OF THE COLUMNENDFETCH NEXT FROM ALL_MMR_TABLES INTO @.TABLE_NAMEENDCLOSE ALL_MMR_TABLESDEALLOCATE ALL_MMR_TABLES-- OUTPUT DATABASE NAMEPRINT 'END------ (' + @.DB_NAME + ') --------'FETCH NEXT FROM ALL_DATABASESINTO @.DB_NAMEENDENDCLOSE ALL_DATABASESDEALLOCATE ALL_DATABASES-------------------------------------------

Hope this helps,

|||

Although the script complies successfully, changes do not take place in the desried table.

Any clue?

|||

Just remember to remove the comment from the following line of code.

-- EXECUTE SP_EXECUTESQL @.SQL2 -- REMOVE THIS COMMENT TO PERFORM THE ADD OF THE COLUMN

It should look like this.

EXECUTE SP_EXECUTESQL @.SQL2 -- REMOVE THIS COMMENT TO PERFORM THE ADD OF THE COLUMN

|||

Thanks!

sql