1

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
3

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.

  • 1
    Trying this on SQL Server 2016. I am getting an error on Set-Variable $MyQuery = "SELECT @@SERVERNAME". A job step received an error at line 2 in a PowerShell script. The corresponding line is 'Set-Variable $MyQuery = "SELECT @@SERVERNAME"'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Cannot bind argument to parameter 'Name' because it is null.' – Shiv Mar 20 '17 at 5:47
  • 1
    I reproduced the error and revised the code. The revised code successfully executes as a SQL Agent job on my local instance of SQL Server 2016 (SP1) Developer Edition. – peterk411 Mar 21 '17 at 18:26
1

This is the loop I use to go through online databases one by one, with your code inserted.

USE master
GO

SET NOCOUNT ON;

DECLARE @db TABLE (Id INT IDENTITY(1,1), DBName NVARCHAR(500))

INSERT INTO @db (DBName)
SELECT Name FROM sys.databases WHERE state = 0 AND name <> 'tempdb'


DECLARE @Pos INT = 1, @MaxID INT, @SqlQuery NVARCHAR(4000)

SELECT @MaxID = MAX(Id)
FROM @db

WHILE @Pos <= @MaxID
BEGIN

SELECT @SqlQuery = 'USE [' + DBName + '];
 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'

FROM @db WHERE Id = @Pos

EXEC sp_executesql @SqlQuery

--PRINT @SqlQuery

SET @Pos = @Pos + 1

END
GO

Secondary to the question, but you can remove the need for looping through executing sp_spaceused for each table, grabbing all the data at once by querying the dmv's like so:

SELECT 
    DB_ID() AS [DatabaseId],
    @@SERVERNAME AS ServerName,
    DB_NAME() AS DatabaseName,
    SCHEMA_NAME(o.schema_id) AS [Schema], 
    o.name AS [Object], 
    CASE o.type WHEN 'U' THEN 'Table' WHEN 'V' THEN 'View' ELSE 'Unknown' END AS [Type],
    ind.row_count,
    CAST((ind.DataSize*8.00/1024) AS DECIMAL(10,2)) AS DataSizeMB,
    CAST((ind.IndexSize*8.00/1024) AS DECIMAL(10,2)) AS IndexSizeMB,
    CAST((ind.ReservedSize*8.00/1024) AS DECIMAL(10,2)) AS ReservedSizeMB

from sys.objects o
inner join (
            SELECT i.object_id, MAX(ps.row_count) AS row_count,
            SUM(CASE WHEN i.index_id <= 1 THEN ps.used_page_count ELSE 0 END) AS DataSize,
            SUM(CASE WHEN i.index_id > 1 THEN ps.used_page_count ELSE 0 END) AS IndexSize,
            SUM(ps.reserved_page_count) AS ReservedSize
            FROM sys.indexes i 
            inner join sys.dm_db_partition_stats ps on ps.object_id = i.object_id and ps.index_id = i.index_id
            GROUP BY i.object_id
            ) 
        ind ON ind.object_id = o.object_id

WHERE o.type IN ('U', 'V');

This still needs to be looped through each database though.

0

Convert what you have into a stored procedure which takes the database name as a parameter (change db_id() and db_name()). Then within your scheduled job call it using

exec sp_msforeachdb 'exec master..mysproc @dbname=''?'''
  • 1
    Does this imply that the SP need to be stored in the master DB? – Paweł Czopowik Nov 21 '13 at 21:42
0

Create an Integration Services package. In the package, use a T-SQL task inside a ForEach container. The T-SQL task has the code above and the ForEach loop runs the task once for every database on a server.

Use a SQL Agent job to schedule the package.

This is how the Maintenance Plans do it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.