Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2012
    Posts
    25

    Unanswered: looping through databases

    I am working on a sql task where i have to loop through all the databases in the server and do operations on those databases.. For example: selecting db1 and executing some select statements, selecting db2 and executing some select statements so on..
    here is the code
    DECLARE @Loop int
    DECLARE @DBName varchar(max)
    declare @maxRow int

    set @Loop = 1;
    SET @DBName = ''
    SELECT @maxRow=max(database_id) FROM sys.databases
    WHILE (@Loop <= @maxRow)
    BEGIN

    SELECT TOP 1 @DBName = d.Name
    FROM master.sys.databases d
    WHERE d.Name > @DBName
    AND d.database_id not in (1, 2, 3, 4) and d.state_desc = 'ONLINE'
    ORDER BY d.Name

    set @Loop = @Loop+1;

    PRINT @DBNAME
    END
    But the result is
    db1
    db2
    db3
    db4
    db4
    db4
    I know the culprit is here
    SELECT @maxRow=max(database_id) FROM sys.databases
    WHILE (@Loop <= @maxRow)
    But I couldn't go further.. Confused how to approach

  2. #2
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Have you seen if sp_MSForEachTable or sp_MSForEachDB will work for you?

    The undocumented sp_MSforeachtable procedure

    The undocumented sp_MSforeachdb procedure

    Also found this on this forum:

    http://www.dbforums.com/microsoft-sq...-tables-2.html
    Last edited by LinksUp; 03-07-13 at 19:47.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •