formatted code to remove horizontal scroll

Source Link

edited Mar 13, 2012 at 18:35

14.8k
17
76
123

If I want to query all databases on a server to see if a stored procedure is present, I can combine a sp_executesql and run a cursor over a query similar to the following:

SELECT 
     'select ' + '''' + name + '''' + ', name from [' + name 
   + '].sys.procedures WHERE name = ''usp_MyProc'' COLLATE SQL_Latin1_General_CP1_CI_AI ' 
    FROM sys.databases 
    -- I get a collation error from the following
    WHERE name NOT IN ('ReportServer', 'ReportServerTempDb');

Can I do the same thing without dynamic sql and without the cursor?

If I want to query all databases on a server to see if a stored procedure is present, I can combine a sp_executesql and run a cursor over a query similar to the following:

SELECT 'select ' + '''' + name + '''' + ', name from [' + name + '].sys.procedures WHERE name = ''usp_MyProc'' COLLATE SQL_Latin1_General_CP1_CI_AI ' 
    FROM sys.databases 
    -- I get a collation error from the following
    WHERE name NOT IN ('ReportServer', 'ReportServerTempDb')

Can I do the same thing without dynamic sql and without the cursor?

If I want to query all databases on a server to see if a stored procedure is present, I can combine a sp_executesql and run a cursor over a query similar to the following:

SELECT 
     'select ' + '''' + name + '''' + ', name from [' + name 
   + '].sys.procedures WHERE name = ''usp_MyProc'' COLLATE SQL_Latin1_General_CP1_CI_AI ' 
FROM sys.databases 
-- I get a collation error from the following
WHERE name NOT IN ('ReportServer', 'ReportServerTempDb');

Can I do the same thing without dynamic sql and without the cursor?

Tweeted twitter.com/#!/StackDBAs/status/175956166151634945

occurred Mar 3, 2012 at 14:49

added 57 characters in body

Source Link

edited Mar 2, 2012 at 22:10

Justin Dearing

2.7k
6
36
52

If I want to query all databases on a server to see if a stored procedure is present, I can combine a sp_executesqlsp_executesql and run a cursor over a query similar to the following:

SELECT 'select ' + '''' + name + '''' + ', name from [' + name + '].sys.procedures WHERE name = ''usp_MyProc'' COLLATE SQL_Latin1_General_CP1_CI_AI ' 
    FROM sys.databases 
    -- I get a collation error from the following
    WHERE name NOT IN ('ReportServer', 'ReportServerTempDb')

Can I do the same thing without dynamic sql and without the cursor?

If I want to query all databases on a server to see if a stored procedure is present, I can combine a sp_executesql and run a cursor over a query similar to the following:

SELECT 'select ' + '''' + name + '''' + ', name from [' + name + '].sys.procedures WHERE name = ''usp_MyProc'' COLLATE SQL_Latin1_General_CP1_CI_AI ' 
    FROM sys.databases 
    -- I get a collation error from the following
    WHERE name NOT IN ('ReportServer', 'ReportServerTempDb')

Can I do the same thing without dynamic sql and without the cursor?

If I want to query all databases on a server to see if a stored procedure is present, I can combine a sp_executesql and run a cursor over a query similar to the following:

SELECT 'select ' + '''' + name + '''' + ', name from [' + name + '].sys.procedures WHERE name = ''usp_MyProc'' COLLATE SQL_Latin1_General_CP1_CI_AI ' 
    FROM sys.databases 
    -- I get a collation error from the following
    WHERE name NOT IN ('ReportServer', 'ReportServerTempDb')

Can I do the same thing without dynamic sql and without the cursor?

Source Link

asked Mar 2, 2012 at 21:59

Justin Dearing

2.7k
6
36
52

Can I query stored procs in all databases with one query without dynamic sql

If I want to query all databases on a server to see if a stored procedure is present, I can combine a sp_executesql and run a cursor over a query similar to the following:

SELECT 'select ' + '''' + name + '''' + ', name from [' + name + '].sys.procedures WHERE name = ''usp_MyProc'' COLLATE SQL_Latin1_General_CP1_CI_AI ' 
    FROM sys.databases 
    -- I get a collation error from the following
    WHERE name NOT IN ('ReportServer', 'ReportServerTempDb')

Can I do the same thing without dynamic sql and without the cursor?

sql-server stored-procedures

Stack Exchange Network

Return to Question

Can I query stored procs in all databases with one query without dynamic sql