Executing SQL query on multiple databases

by

You can use WHILE loop over all database names and inside loop execute query with EXECUTE. I think that statement SET @dbname = ... could be better, but this works too.

DECLARE @rn INT = 1, @dbname varchar(MAX) = '';
WHILE @dbname IS NOT NULL 
BEGIN
    SET @dbname = (SELECT name FROM (SELECT name, ROW_NUMBER() OVER (ORDER BY name) rn 
        FROM sys.databases WHERE name NOT IN('master','tempdb')) t WHERE rn = @rn);

    IF @dbname <> '' AND @dbname IS NOT NULL
        EXECUTE ('use '+QUOTENAME(@dbname)+';

            /* Your script code here */
            UPDATE some_table SET ... ;

        ');
    SET @rn = @rn + 1;
END;

More Related Contents:

  1. Is it possible to use greater than equal to operator in group by? [closed]
  2. How can I schedule a job to run a SQL query daily?
  3. What’s the best practice for primary keys in tables? [closed]
  4. Reset identity seed after deleting records in SQL Server
  5. How to create multiple one to one’s
  6. Querying data by joining two tables in two database on different servers
  7. Generate a resultset of incrementing dates in TSQL
  8. What is best tool to compare two SQL Server databases (schema and data)? [duplicate]
  9. How do I create a step in my SQL Server Agent Job which will run my SSIS package?
  10. What is the difference between using a cross join and putting a comma between the two tables?
  11. Pivot Table and Concatenate Columns
  12. How would you implement sequences in Microsoft SQL Server?
  13. SQL server query to get the list of columns in a table along with Data types, NOT NULL, and PRIMARY KEY constraints
  14. What does =* mean?
  15. Correct use of transactions in SQL Server
  16. Splitting the string in sql server
  17. Need to list all triggers in SQL Server database with table name and table’s schema
  18. How can I do a BEFORE UPDATED trigger with sql server?
  19. Favourite performance tuning tricks [closed]
  20. The backend version is not supported to design database diagrams or tables
  21. How to check which locks are held on a table
  22. How to sum up time field in SQL Server
  23. Can SQL Server Express LocalDB be connected to remotely?
  24. Using SQL Server as a DB queue with multiple clients
  25. How do I list all the columns in a table?
  26. The ALTER TABLE statement conflicted with the FOREIGN KEY constraint
  27. How do I change db schema to dbo
  28. The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions
  29. Functions vs Stored Procedures
  30. Single or multiple databases

Leave a Comment