CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
TechGuy
TechGuy

Reputation: 347

Need to run a stored procedure on all non system databases

I'm trying to loop through all non system databases and run a stored procedure. This stored procedure exists in all of the user databases.

This is what I have found so far:

DECLARE @command varchar(1000) 
SELECT @command = 'USE ? SELECT name FROM sysobjects WHERE xtype = ''U'' ORDER BY name' 

USE @command;
GO

EXECUTE Support.CleanIndiciesAndShrinkDatabase;
GO

I get an error this error:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '@command'.

Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'Support.CleanIndiciesAndShrinkDatabase'.

Any suggestions on fixing this?

Upvotes: 0

Views: 366

Answers (3)

gotqn
gotqn

Reputation: 43666

Try something like this:

DECLARE @DynamicTSQLStatement NVARCHAR(MAX);

SELECT @DynamicTSQLStatement = STUFF
(
    (
        SELECT 'USE [' + [name] + ']; EXECUTE Support.CleanIndiciesAndShrinkDatabase;' 
        FROM [sys].[databases]
        WHERE [name] NOT IN ('master', 'tempdb', 'model', 'msdb')
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
    ,1
    ,1
    ,''
);

EXECUTE sp_executesql @DynamicTSQLStatement;

Upvotes: 0

Red Devil
Red Devil

Reputation: 2403

You can try this:

Create PROC PROC_NAME 
AS
BEGIN
DECLARE @name nvarchar(50)
declare @cursor cursor
set @cursor = CURSOR FAST_FORWARD FOR select name from sys.databases where database_id > 4

open @cursor

FETCH NEXT FROM @cursor INTO @name 

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE @use nvarchar(50) = 'USE '

SET @use = @use + @name


Declare @query nvarchar(max) = @use + ' exec Your_PROC'


EXEC sp_executesql  @query

FETCH NEXT FROM @cursor INTO @name 

END

CLOSE @cursor

DEALLOCATE @cursor

END

Upvotes: 0

Michail Papadakis
Michail Papadakis

Reputation: 89

Try the following


DECLARE @command varchar(1000) 
DECLARE @spName VARCHAR(50)
SET @spName = 'Support.CleanIndiciesAndShrinkDatabase'

SELECT @command = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') 
BEGIN 
    USE ? 
        IF EXISTS(SELECT TOP 1 1 FROM sys.procedures AS P WHERE p.name = ''' + @spName + ''')
        BEGIN
            PRINT ''running '+ @spName + ' on '' + DB_NAME()
            EXEC ' +@spName+' 
        END
        ELSE
        BEGIN 
            PRINT ''' + @spName + ' was on found on database '' + DB_NAME()''
        END

END ' 

EXEC sp_MSforeachdb @command

It will run on all non-system databases. Now, the error you get means that SQL Server cannot find the stored procedure. You could fix this by creating the stored procedure on any database that does not have it yet and then running it. So a better query would be


DECLARE @command varchar(1000) 
DECLARE @schemaName VARCHAR(50)
DECLARE @spName VARCHAR(50)
SET @schemaName  = 'Support'
SET @spName = 'CleanIndiciesAndShrinkDatabase'

SELECT @command = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') 
BEGIN 
    USE ? 

        IF NOT EXISTS(SELECT TOP 1 1 
                        FROM sys.procedures AS P 
                            INNER JOIN sys.schemas AS S ON S.schema_id = P.schema_id
                        WHERE p.name = ''' + @spName + '''
                            AND s.name = ''' + @schemaName + ''')
        BEGIN
            PRINT ''creating '+ @spName + ' on '' + DB_NAME()
            IF NOT EXISTS (   SELECT TOP 1 1
                  FROM   sys.schemas AS S
                  WHERE  S.name = ''' + @schemaName + ''' )
                BEGIN
                    PRINT ''CREATING SCHEMA ' + @schemaName + '''
                    EXEC ( '' CREATE SCHEMA ' + @schemaName + ''' );
                END;

                EXEC ( ''
                    CREATE PROCEDURE ' + @schemaName + '.' + @spName + '
                    AS
                    BEGIN  
                        -- SP CODE GOES HERE
                        -- SELECT COUNT(*) FROM SYS.TABLES --uncomment this for check
                    END 
                '' );
        END

        PRINT ''running '+ @spName + ' on '' + DB_NAME()
        EXEC ' + @schemaName + '.' + @spName +' 

END ' 

EXEC sp_MSforeachdb @command

Upvotes: 1

Related Questions