'SQL Server : run a script on all databases

I have a SQL script with a function (function drops at the end of the script) and temporary table definitions and variables to process the data that gives me one single line result for a database.

It works but I need to run and get the all results for 1000 databases. It seems impossible to run the script seperately.

I have found sp_MSForEachDB single command line examples but I have a whole script and couldn't make it work with this way.

Is there any way to run a script on all the databases?

Thanks for reply

sql-servertsql


Solution 1:[1]

I'm not saying this is a good idea, since we don't really know what this script you're running does, but this should work. I don't envy you the pain of getting all of the single quotes in the right places, but it's the price we pay for using undocumented, unsupported stored procedures.

DECLARE @sqlText varchar(max); 

SET @sqlText = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') 
  BEGIN 
    USE ? 
    EXEC(''CREATE <functions, tables, what have you>'') 
  END';

EXECUTE sp_MSforeachdb @sqlText;

Solution 2:[2]

Hi In SSMS you can run script for selected database with sqlcmd command. here for example I want to run my script in two different database for it I have save my script in local. And calling it using :r "C:\temp\test1.sql".

Still I am thinking another way to do it using loop.

enable sql mode SSMS > QUERY > SQLCMD Mode

use test
:r  "C:\temp\test1.sql"
use S2VTST6
:r  "C:\temp\test1.sql"

Solution 3:[3]

I was looking for a way to use a separate saved query file to run the script against a certain set of databases that were all under the same server. To do so, I read the contents of the sql file into a variable and then used the sp_executesql command to run it under each iteration of a cursor:

/*
    INSTRUCTIONS:
        * Save the sql file in a place that is accessible by the server
        * Change the path to match path of the directory!
        * Apply filters to the databases you are targeting
        (OPTIONAL) Try printing your command before executing it!
        (OPTIONAL) Inserting into a temp table requires you to define the same columns! (see below)
*/

CREATE TABLE #temp
    (Id INT, FOO DATETIME2(7), BAR VARCHAR(100))

/* !!! Change the file path !!! (must be accessible by the server) */
DECLARE @FileContents VARCHAR(MAX)
SELECT @FileContents = BulkColumn
FROM OPENROWSET(BULK 'E:\PathToYourFile\MyFile.sql', SINGLE_CLOB) x;

/* Instantiate a cursor to loop over each database */
DECLARE @DB_Name VARCHAR(100) 
DECLARE @Command VARCHAR(MAX) 
DECLARE database_cursor CURSOR FOR 
SELECT name 
FROM MASTER.sys.databases
WHERE database_id > 4 AND state = 0 /*Online*/ AND
/* !!! apply a filter to target specific databases !!! */
    name LIKE '%ConsistentNamingConvention%'

/* Open and retrieve the first entry */
OPEN database_cursor 
FETCH NEXT FROM database_cursor INTO @DB_Name 

WHILE @@FETCH_STATUS = 0 
BEGIN 
    /* Use the database from the name on the cursor. Then run the command saved above */
    SELECT @Command = 'USE ' + QUOTENAME(@DB_Name) + ';' + CHAR(13)+CHAR(10) --add 2 newlines
    + CHAR(13)+CHAR(10)
    + @FileContents

    /* View the command (uncomment) */
    --PRINT @Command

    /* run the file contents against the specific database! */
    EXEC sp_executesql @Command 

    /* iterate the cursor to the next database name */
    FETCH NEXT FROM database_cursor INTO @DB_Name 
END 
CLOSE database_cursor;
DEALLOCATE database_cursor;

SELECT * FROM #Temp
DROP TABLE #Temp

This assumes the MyFile.sql is using INSERT INTO #Temp! (SELECT INTO will not work)

Instructions:

  1. Save the SQL file in a place accessible by the sql server
  2. Change the path of the file to point to your file (line 16). Unfortunately this must be a string literal, not a variable
  3. Filter what databases you are working with under the CURSOR declaration (line 26)

Again, this assumes that all of the databases you are interested in are on the same server.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Eric Brandt
Solution 2 Jigar Parekh
Solution 3

Related Questions