Technical Article

sp_MSforeachtable:Add SQL objects to multiple database on a server.

,

Add SQL objects to multiple database on a server.

If you wanted to add a SQL table or view or any SQL object to multiple database on your server and do not want to execute the script over and over again in all the multiple databases than the following SQL script can be used.

There have been instances where I had to add a stored procedure or a table in multiple client database as a part of the schema change process and this script has come in handy.


--ADD SQL OBJECTS(TABLES, VIEWS, SPS, UDFS ET AL) TO ALL THE DATABASES ON THE SERVER. 
PRINT '##### BEGIN DB SCRIPT ##### '
EXEC SP_MSFOREACHDB 
'USE [?]IF ''?'' NOT IN (''MASTER'', ''MODEL'', ''MSDB'', ''TEMPDB'', ''ADVENTUREWORKS'' )
BEGIN
IF NOT EXISTS
(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''MYTABLE'')
BEGIN
CREATE TABLE [DBO].[MYTABLE](COLUMN_1 VARCHAR(50) NULL, COLUMN_2 DATETIME NULL)
PRINT '' TABLE CREATED IN DATABASE ('' + DB_NAME() + '')''
END
ELSE
PRINT '' TABLE PRESENT IN DATABASE ('' + DB_NAME() + '')''
END '
PRINT '##### DB SCRIPT COMPLETED #####'

Please note that sp_MSforeachtable is undocumented/unsupported .

Even though you can use it in SQL server 2000, 2005, 2008 it might or might not be removed in the future versions.

So, use at your own risk and discretion.

--ADD SQL OBJECTS(TABLES, VIEWS, SPS, UDFS ET AL) TO ALL THE DATABASES ON THE SERVER. PRINT '##### BEGIN DB SCRIPT ##### 'EXEC SP_MSFOREACHDB 'USE [?]IF ''?'' NOT IN (''MASTER'', ''MODEL'', ''MSDB'', ''TEMPDB'', ''ADVENTUREWORKS'' )BEGINIF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''MYTABLE'')BEGINCREATE TABLE [DBO].[MYTABLE](COLUMN_1 VARCHAR(50) NULL, COLUMN_2 DATETIME NULL)PRINT '' TABLE CREATED IN DATABASE ('' + DB_NAME() + '')''ENDELSEPRINT '' TABLE PRESENT IN DATABASE ('' + DB_NAME() + '')''END 'PRINT '##### DB SCRIPT COMPLETED #####'

Rate

4 (1)

Share

Share

Rate

4 (1)