How to execute the stored procs from a dynamically generated query + sql server

Question

I have a query that is dynamically fetching the stored proc names from all the databases. Now I want to execute the stored procs and store the result in a temptable or table variable.

How to do that. Here is my SP so far

Declare @GetDBNames sysname
Declare @DynSql nvarchar(max)

Declare DBNames cursor for 
Select '['+name+']' from master.dbo.sysdatabases 

open DBNames
FETCH NEXT FROM DBNames into @GetDBNames

WHILE @@FETCH_STATUS=0
BEGIN

SET @DynSql = '
Select Specific_Catalog as Database_Name, Routine_Name as ''Stored Procedure Name''
From '+ @GetDBNames+'.Information_Schema.Routines '

EXEC (@DynSql)
FETCH NEXT FROM DBNames into @GetDBNames
END

Close DBNames
Deallocate DBNames

Please help. Thanks in advance

Alexey A. · Accepted Answer · 2009-11-10 15:06:39Z

1

Here's my soluton. Cursors are evil :)

Declare @DynSql nvarchar(max)
declare @result table ([Database_Name] nvarchar(128), [Stored Procedure Name] sysname)

SET @DynSql = ''
select @DynSql = @DynSql + '
Select SPECIFIC_CATALOG COLLATE DATABASE_DEFAULT as Database_Name, ROUTINE_NAME COLLATE DATABASE_DEFAULT as [Stored Procedure Name]
From ' + NAME + '.INFORMATION_SCHEMA.ROUTINES' + CHAR(13) + 'union all' + CHAR(13) 
from master.dbo.sysdatabases

--remove last "union all"
SET @DynSql = left(@DynSql, LEN(@DynSql) - 10) 

insert @result
exec sp_executesql @DynSql 

select * from @result

answered Nov 10, 2009 at 15:06

Alexey A.

90211 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marc Gravell · Accepted Answer · 2009-11-10 07:09:45Z

1

INSERT [TableName] EXEC (@DynSql)

Note that if you introduce data you may want to use sp_ExecuteSQL, and you probably want to use [/] object-name escaping.

answered Nov 10, 2009 at 7:09

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

3 Comments

priyanka.sarkar Over a year ago

I just did insert into #temp EXEC (@DynSql) I got the error Msg 208, Level 16, State 0, Line 17 Invalid object name '#temp'.

priyanka.sarkar Over a year ago

Also after that I have to execute the stored procs and store the result in a temptable or table variable.

Marc Gravell Over a year ago

Well - does #temp exist? Try using a table variable... it works fine for me.

Collectives™ on Stack Overflow

How to execute the stored procs from a dynamically generated query + sql server

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related