How to Loop through all DBs in an Instance?

  • Hi,

    Can anyone provide a method to loop through a DB's in an instance WITHOUT using the proc sp_MSforeachdb

    I have tried the following, and the "EXEC(@String)" does not error, but I always stay in the context of the DB I run it from.

    Very frustrating, there must be a way to do this?!?!?!?

    ------------------------------------------

    DECLARE @DatabaseName nvarchar(50)

    DECLARE @String nvarchar(50)

    SELECT name

    INTO #nameOfAllDbsInTheInstance

    FROM sys.sysdatabases

    DECLARE DBName CURSOR

    FOR SELECT D.[name]

    FROM #nameOfAllDbsInTheInstance AS D

    OPEN DBName

    FETCH NEXT FROM DBName

    INTO @DatabaseName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @String = 'USE ' + @DatabaseName

    PRINT @String

    EXEC(@String)

    SELECT DB_NAME()

    FETCH NEXT FROM DBName

    INTO @DatabaseName

    END

    DROP TABLE #nameOfAllDbsInTheInstance

    CLOSE DBName

    DEALLOCATE DBName

    ------------------------------------------

  • YOur cursor doesnt do here anything , simply "USe database"

    What actaully you are trying to achieve here ?

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • It is just an example, the cursor here just gets the DBName and I try and change to the context of that DB.

    I know it's not actually do anything as it is just and example, the whole point is how to change DB context dynamically?

  • tom.moore.777 89426 (1/23/2013)


    the whole point is how to change DB context dynamically?

    yes it can be done with the same way as you did but need additional code too like :

    set @lstr = 'USe ' + @databasename + '; create table Test (id int ) ;'

    exec (@lstr)

    this will create test table in all the databases passed by @databasename from cursor

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • tom.moore.777 89426 (1/23/2013)


    Can anyone provide a method to loop through a DB's in an instance WITHOUT using the proc sp_MSforeachdb

    Any particular reason for avoiding the use of sp_MSforeachdb?


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Kingston Dhasian (1/23/2013)


    tom.moore.777 89426 (1/23/2013)


    Can anyone provide a method to loop through a DB's in an instance WITHOUT using the proc sp_MSforeachdb

    Any particular reason for avoiding the use of sp_MSforeachdb?

    msForeachdb has a few bugs and limits.

    iirc there was a character limitation. There is also inconsistent behavior such as it skipping databases.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Here are two alternatives to sp_MSforeachdb

    http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/29/a-more-reliable-and-more-flexible-sp-msforeachdb.aspx

    And then this one that I have been using:

    http://spaghettidba.com/2011/09/09/a-better-sp_msforeachdb/

    With a few samples of usage here

    http://jasonbrimhall.info/2012/07/17/a-trio-of-eachdb/

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • If you want to do the same thing in each database, then this would be another alternative solution

    DECLARE @SQL NVARCHAR(MAX)

    SELECT @SQL =

    REPLACE(

    CAST(

    (

    SELECT 'USE ' + QUOTENAME(name) +';' + CHAR(13) + CHAR(10) +

    --PUT WHAT YOU WANT TO DO IN EACH DATABASE IN THIS BLOCK

    ----

    'SELECT ' + CHAR(13) + CHAR(10) +

    'DatabaseName = DB_NAME(), ' + CHAR(13) + CHAR(10) +

    'a.FILEID, ' + CHAR(13) + CHAR(10) +

    '[FILE_SIZE_MB] = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2)), ' + CHAR(13) + CHAR(10) +

    '[SPACE_USED_MB] = CONVERT(DECIMAL(12, 2), ROUND(fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +') / 128.000, 2)), ' + CHAR(13) + CHAR(10) +

    '[FREE_SPACE_MB] = CONVERT(DECIMAL(12, 2), ROUND((a.size - fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +')) / 128.000, 2)), ' + CHAR(13) + CHAR(10) +

    'a.NAME, a.FILENAME ' + CHAR(13) + CHAR(10) +

    'FROM dbo.sysfiles a;' + CHAR(13) + CHAR(10)

    ----

    FROM

    sys.databases

    FOR XML PATH('')

    ) AS NVARCHAR(MAX)

    ),

    '&#x 0D;',CHAR(13) + CHAR(10)

    )

    --SELECT @SQL

    EXECUTE sp_executesql @SQL

    Just remove the space between the x and the 0 in the following string in the script '&#x 0D;'

    The above example loops through all the databaes and gets their file usage, free space, used space, total space etc, so I can track DB growth.

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

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