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