Post reply

Run SQL code on each database

  • April 1, 2013 at 9:18 am

    #275799

    Comments posted to this topic are about the item Run SQL code on each database

  • April 10, 2013 at 3:38 am

    #1605145

    First off, thanks for taking the time to compose and share this script.

    There are a few things that you might want to take into consideration:

    1) The state of the database. You're going to run into problems if a DB is in a restore state for example.

    2) Secondly by using the windows functions you're precluding it's use on some earlier versions.

    As I said, thanks for sharing. If you could amend to cater for these points then you have a great little script on your hands.

    Hope this helps,
    Rich

    [p]
    [/p]

  • April 10, 2013 at 5:11 am

    #1605157

    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

  • April 10, 2013 at 6:09 am

    #1605179

    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

  • April 10, 2013 at 3:13 pm

    #1605414

    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

  • February 5, 2015 at 10:49 am

    #1775798

    You might consider the need to surround the database name in the USE statement with brackets. I just ran CREATE DATABASE [Stop that] and now have a database with a space in the database name.

    ATBCharles Kincaid

  • February 5, 2015 at 11:48 am

    #1775834

    To the Point #1:

    Add/comment the initial SELECT where clause:

    where name <> 'TEMPDB'

    and (status & 512) = 0 /*online dbs only*/

    and (status & 1024) = 0 /*read-write dbs only*/

    As the TEMPDB is not the database you want to run your scripts in

    Alex Donskoy

  • March 22, 2017 at 6:39 am

    #1934283

    Thanks for sharing. one comment, it is a little less overhead to increment negatively to zweo instead of having another variable @i and incrementing it by +1: 

    Declare @DBCnt int, @iCur int;
    DECLARE @dbs as Table (DBName varchar(100), DBNumber int) --Temp table for DBs to run against
    INSERT INTO @dbs select [name] as DBName, ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY [name]) AS DBNumber from sys.databases;
    set @DBCnt = @@rowcount
    WHILE @DBCnt > 0 --Start loop over all DB's
    BEGIN
        select * FROM @dbs where DBNumber = @DBCnt;
       -- do something 
        set @DBCnt = @DBCnt-1;
    END

Viewing 8 posts - 1 through 7 (of 7 total)

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