Sql-server – SQL Server, how to execute a job against all databases on server

sql serverstored-procedures

I'm trying to find the best way to execute a query against all databases on the server using SQLServerAgent or maintenance plan.

I have a stored procedure that I'd like to run on these databases, but I cannot create the stored procedure in these databases.

I tried experimenting with using exec sp_msforeachdb and DECLARE @command = '<my sp>' but I ran into the problem with escaping too many single quotes '.

So in the end, I'd like to say something like:
For all databases, run this block of code. Then schedule this job with SQLServerAgent.

    BEGIN
    SET NOCOUNT ON

    --DECLARE VARIABLES
    DECLARE
    @max INT,
    @min INT,
    @table_name NVARCHAR(256),
    @table_schema NVARCHAR(256),
    @sql NVARCHAR(4000)

    --DECLARE TABLE VARIABLE
    DECLARE @table TABLE(
    id INT IDENTITY(1,1) PRIMARY KEY,
    table_name NVARCHAR(256),
    table_schema NVARCHAR(256))

    --CREATE TEMP TABLE THAT STORES INFORMATION FROM SP_SPACEUSED
    IF (SELECT OBJECT_ID('tempdb..#results')) IS NOT NULL
    BEGIN
    DROP TABLE #results
    END

    CREATE TABLE #results
    (
    [database_id] [int] NULL,
    [server_name] [nvarchar](256)  NULL, --- new, server name
    [database_name] [nvarchar](256)  NULL, --- new, db name
    [table_schema] [nvarchar](256) NULL,
    [table_name] [nvarchar](256) NULL,
    [table_rows] [int] NULL,
    [reserved_space] [nvarchar](55) NULL,
    [data_space] [nvarchar](55) NULL,
    [index_space] [nvarchar](55) NULL,
    [unused_space] [nvarchar](55) NULL
    )

    --LOOP THROUGH STATISTICS FOR EACH TABLE
    INSERT @table(table_schema, table_name)
    SELECT  
    table_schema, table_name
    FROM
    information_schema.tables 

    SELECT
    @min = 1,
    @max = (SELECT MAX(id) FROM @table)

    WHILE @min <= @max
    BEGIN
    SELECT 
    @table_name = table_name,
    @table_schema = table_schema
    FROM
    @table
    WHERE
    id = @min

    --DYNAMIC SQL
    SELECT @sql = 'EXEC sp_spaceused ''[' + @table_schema + '].[' + @table_name + ']''' --get dataA

    --INSERT RESULTS FROM SP_SPACEUSED TO TEMP TABLE
    INSERT #results(table_name, table_rows, reserved_space, data_space, index_space, unused_space)
    EXEC (@sql) --set dataA^

    --UPDATE SCHEMA NAME
    UPDATE #results
    SET table_schema = @table_schema WHERE table_name = @table_name
    UPDATE #results
    SET 
    database_id = (DB_ID()),
    server_name = (select @@SERVERNAME),
    database_name = (SELECT DB_NAME() AS [Current Database]);

    SELECT @min = @min + 1
    END

    --REMOVE "KB" FROM RESULTS FOR REPORTING (GRAPH) PURPOSES
    UPDATE #results SET data_space = SUBSTRING(data_space, 1, (LEN(data_space)-3))
    UPDATE #results SET reserved_space = SUBSTRING(reserved_space, 1, (LEN(reserved_space)-3))
    UPDATE #results SET index_space = SUBSTRING(index_space, 1, (LEN(index_space)-3))
    UPDATE #results SET unused_space = SUBSTRING(unused_space, 1, (LEN(unused_space)-3))

    --INSERT RESULTS INTO TABLESIZEGROWTH
    INSERT INTO TableSizes.dbo.TableSizeGrowth (database_id, server_name, database_name, table_schema, table_name, table_rows, reserved_space, data_space, index_space, unused_space)
    SELECT * FROM #results

    DROP TABLE #results
    END

Best Answer

Create a SQL Agent Job with a Job Step of type "Powershell" on the SQL Server instance where your databases reside.

# Define the query to be run against each database
Set-Variable -Name MyQuery -Value "SELECT @@SERVERNAME"

# Access SQL Server information by using a drive and file system analogy
Set-Location "SQLSERVER:\SQL\localhost\DEFAULT\Databases"

# Get the list of databases
Get-ChildItem | Set-Variable -Name DBList

# Iterate through the list, execute your query
ForEach ( $DB in $DBList) {
    Invoke-Sqlcmd -query $MyQuery -database $DB.Name -ServerInstance 'localhost' -ErrorAction Stop
}

Schedule it like any other job.

Related Question