locked
loop through multiple databases RRS feed

  • Question

  • User-153404742 posted

    Not sure what the best way is regarding performance.....

    I need to find a user with three columns....have multiple databases with same table and structure.  What's the best query to loop through each database to locate the row with UserID, firstname and lastname.  If it matches, I want query to return the database name to my c# code so I'm thinking a stored procedure where I'll be passing in the information to search.  How do I loop thorough databases in sql and return db name?

    Friday, April 22, 2016 6:23 PM

Answers

All replies

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, April 23, 2016 9:22 AM
  • User-219423983 posted

    Hi inkaln,

    I think you’d better use the Table-Valued Parameters to store the database name information.  Then you could following below thread to loop through this table parameter.

    http://stackoverflow.com/questions/1578198/can-i-loop-through-a-table-variable-in-t-sql

    For each data row, you could combine a query string like “select count(*) from databasename.dbo.tablename where…” to and then refer to below thread to get the result from execute sql. If the value is larger or bigger than 1, just returns current database name from current row of the table parameter.

    http://stackoverflow.com/questions/4130462/return-value-from-execsql

    Best Regards,

    Albert Zhang

    Saturday, April 23, 2016 9:39 AM
  • User-595703101 posted

    Hello inkaln,

    If you want to execute the same SQL script on all databases of a SQL Server instance, you can use undocumented SQL Server stored procedure sp_MsForEachDB

    By modifying your script, you can eliminate databases that you don't want to run your script and execute the main process on selected db's

    Monday, April 25, 2016 6:18 AM
  • User-595703101 posted

    Hello inkaln,

    An other option for running a script on every database is looping through a cursor of databases on your server.

    Please check following SQL Server cursor example

    Please note that I used the schema "sys", your table belongs to "dbo" schema probably. So you might need to change it in your test script.

    Within the cursor, I replace the database name with the db name that I retrieve from the cursor.

    Then I execute the SQL script build dynamically in the cursor. You can refer to SQL tutorial to build dynamic SQL queries for more.

    declare @sql nvarchar(max)
    SET @sql = N'select name from [@DBName].sys.tables'
    
    DECLARE @DBName4Loop sysname
    declare @sql4Loop nvarchar(max)
    
    DECLARE dbcursor CURSOR FAST_FORWARD FOR SELECT name FROM sys.databases
    
    OPEN dbcursor
    
    FETCH NEXT FROM dbcursor INTO @DBName4Loop
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    	print @DBName4Loop
      SET @sql4Loop = REPLACE(@sql, '[@DBName]', '['+@DBName4Loop+']')
      EXEC SP_EXECUTESQL @sql4Loop
      FETCH NEXT FROM dbcursor INTO @DBName4Loop
    END
    
    CLOSE dbcursor
    DEALLOCATE dbcursor

    Monday, April 25, 2016 6:44 AM