crashdan (4/10/2013)
smcguire (4/10/2013)
I recently discovered the original stored procedure that you referenced. The one issue that I have with it is the limitation that it runs on ALL databases! There is no way to limit the list of databases it runs on. I would like to see an additional parameter (such as @LimitDB) which would contain a like clause that would be used when querying for the list of databases (if not specified then all databases would be selected).--Stewart McGuire
You can take what was posted and add like below (as an example)
INSERT INTO @dbs
select name as DBName, ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY name) AS DBNumber
from sys.databases
AND name like @LimitDB
Firstly thanks for the feedback guys
In regards to limiting the databases I actually do similar to what you are suggesting crashdan, but I must admit I hard code it in and have a flag to ignore the hard coded databases or not. In my circumstance though we usually only want to run it on a handful of constant databases but I leave open the option to run it on all.
The other option, as per the example, is at run time by looking at the current database it has reached (by using the ? variable) and using an if to decide to run your script or not. Much like your suggestion you can feed in the databases you want to run the script/s on.
@Command1 = ' if ''?'' not like ''%master%'' begin select DB_NAME() end'
Thanks
Ash