Executing a Script on multiple databases

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Executing a Script on multiple databases

Author

Topic

ramoneguru
Yak Posting Veteran

69 Posts

Posted - 2007-04-25 : 15:58:27

I have a stored procedure that simply removes beginning and ending whitespace in a few columns of some tables like so:

CREATE PROCEDURE spDeleteWhiteSpace
AS

/*Clean up Report Templates*/
UPDATE ReportTemplate
SET TemplateDescription = RTRIM(LTRIM(TemplateDescription))

UPDATE ReportTemplate
SET TemplateName = RTRIM(LTRIM(TemplateName))

/*Clean up ReportVersion table*/
UPDATE ReportVersion
SET ReportName = RTRIM(LTRIM(ReportName))

UPDATE ReportVersion
SET ReportName = RTRIM(LTRIM(ViewTableName))

/*Clean up ViewTableContext*/
UPDATE ViewTableContext
SET ViewTableName = RTRIM(LTRIM(ViewTableName))

Now, I also have several databases defined on a single instance thusly:

DB Instance: PrdServer
Database Names:
SCPrd
HIPrd
MNPrd
LAPrd
CAPrd
TXPrd
....
...
and so on for about 40 more databases.

I need a way to create this stored procedure on each DB without having to manually change the name of the database every time using the USE <db name> command (ie. USE SCPrd).

Any ideas?
--Nick

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-25 : 16:33:06

you can loop through master..sysdatabases
I think there's an undocumented proc sp_foreachdb that you can also use to loop through each db.

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-04-25 : 16:58:31

for this kind of thing, i generally use shell scripts combined with sqlcmd. for example:


for /F "tokens=1,2 delims=," %%i in (servers_and_databases.txt) do @(
	set SERVER=%%i
	set DATABASE=%%j
	sqlcmd.exe -i myscript.sql -E -S %SERVER% -d %DATABASE% 	
)

where servers_and_databases.txt is a csv file with server and database names. this way your script only executes against the servers and databases you specify, rather than using the shotgun blast approach of foreachdb.

www.elsasoft.org

ramoneguru
Yak Posting Veteran

69 Posts

Posted - 2007-04-25 : 17:06:36

Ahhhh, awesome. Thanks.
--Nick

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources