running a stored procedure against all databases on a server

Question

Post reply

running a stored procedure against all databases on a server

benjamin potts

SSC Veteran

Points: 265
More actions
September 6, 2005 at 4:10 am

#89605

Hi,
I have a stored procedure sp_dbversion which gives me the software version number of the database we run. I have about 100 test/backup databases on our server and would like to know what version each database is running.
im looking for an output like testdb1 v4.23
sp_dbversion's output is just 'v4.23'
Any help would be greatly appreciated!
Ben
Below is some code I have been using but cant get it to work for some reason.
declare @cmd1 varchar(500)
declare @cmd2 varchar(500)
declare @cmd3 varchar(500)
set @cmd1 ='if ''?'' <> ''tempdb'' print ''*** Processing DB.... ? ***'''
set @cmd2 = 'if ''?'' <> ''tempdb'' print ''********************************'''
set @cmd3 = 'if ''?'' <> ''tempdb'' exec ''exec ?..sp_dbversion'''
exec sp_MSforeachdb @command1=@cmd1,
@command2=@cmd2,
@command3=@cmd3

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

This works just fine;

exec sp_msforeachdb 'use ? select db_name() as ''now processing'' exec sp_helpdb ?'

..so maybe try:

exec sp_msforeachdb 'use ? select db_name() as ''now processing'' exec dbo.sp_dbversion'

..or something similar..

/Kenneth

benjamin potts SSC Veteran Points: 265 More actions · Answer 2

thats great. works a treat!

one last thing though if someone has an idea....

there are other different types of databases on the server, ones that im not bothered about and these dont have the sp_dbversion stored procedure.

How would I alter

exec sp_msforeachdb 'use ? select db_name() as ''now processing'' exec dbo.sp_dbversion'

to include a check for the existance of sp_dbversion in each database. If it exists then execute it.

Sorry for the probably simple question but im new to all this.

Thanks again

benjamin potts SSC Veteran Points: 265 More actions · Answer 3

solved it thanks again Kenneth Wilhelmsson

exec sp_msforeachdb 'if exists(select * from master..sysdatabases where name = ''?'') use ?

if exists(select *

from ?..sysobjects

where name = ''sp_dbversion'') select db_name() as '' ''

if exists(select *

from ?..sysobjects

where name = ''sp_dbversion'') exec dbo.sp_dbversion'

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 4

Here's another way - it will also list names of db's not having the proc in question so you can see if some db's may have been missed..

exec sp_msforeachdb 'use ? select db_name() as ''now processing'' if object_id(''dbo.sp_dbversion'') is not null exec dbo.sp_dbversion else print ''procedure not existing'''

A last note on naming. If you'r proc is called 'sp_dbversion', you should consider changing the prefix to something else. sp_ is indicating a 'system procedure'. All procedures beginning with sp_ is treated special by the server, and has certain rules they should abide to in order to always be predictable when they execute. To avoid this, it's better to name you procs to something not beginning with sp_ - the exception being if you write a proc that is supposed to act and behave just like a sp_ proc.

/Kenneth