sp_describe_first_result_set
@@SERVERNAME
EXEC master..sp_addlinkedserver @server = 'loopback', @srvproduct = '', @provider = 'SQLNCLI', @datasrc = @@SERVERNAME; EXEC master..sp_serveroption @server = 'loopback', @optname = 'DATA ACCESS', @optvalue = 'TRUE';
if object_id('tempdb..#tmp') is not null drop table #tmp go /* TODO: The following line is only an example. Replace 'exec [MyDatabase].[MySchema].[sp_MyStoredProc] 0,0,null' with your own stored procedure call, including parameter values */ select top 10 * into #tmp from openquery(loopback, 'exec [MyDatabase].[MySchema].[sp_MyStoredProc] 0,0,null') SELECT c.name ,[type] = t.name ,c.max_length ,c.[precision] ,c.scale ,c.is_nullable FROM tempdb.sys.columns AS c inner JOIN tempdb.sys.types AS t ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id WHERE c.[object_id] = object_id('tempdb..#tmp')
create procedure dbo.GetStoredProcedureOutputStructure @spName nvarchar(300) as /*--- Begin Testing ---*/ /* declare @spName nvarchar(300) = '[MySchema].[sp_MyStoredProc]' */ /*--- End Testing ---*/ if object_id(@spName) is not null begin /*--- Calculate Param List as All Nulls (separated by commas) ---*/ declare @xml xml = ( select [@v] = 'null' from sys.procedures pr inner join sys.parameters p on pr.object_id = p.object_id inner join sys.schemas s on pr.schema_id = s.schema_id where 1=1 and '[' + s.name + '].[' + pr.name +']' = @spName for xml path ('x') ) declare @spParams nvarchar(4000) = convert(nvarchar(4000), @xml) set @spParams = replace(@spParams, '"/><x v="', ', ') set @spParams = replace(@spParams, '<x v="', '') set @spParams = replace(@spParams, '"/>', '') /*--- Calculate OpenQuery statement and call it ---*/ if object_id(N'tempdb..##tmp') is not null drop table ##tmp set @spName = '[' + db_name() + '].' + @spName declare @openquery nvarchar(4000) = 'select top 10 * into ##tmp from openquery(loopback, ''exec ' + @spName + ' ' + @spParams + ''')' exec (@openquery) /*--- Return Output Structure as XML ---*/ SELECT [@Name] = c.name ,[@DataType] = t.name ,[@MaxLength] = c.max_length ,[@Precision] = c.[precision] ,[@Scale] = c.scale ,[@IsNullable] = c.is_nullable FROM tempdb.sys.columns AS c inner JOIN tempdb.sys.types AS t ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id WHERE c.[object_id] = object_id('tempdb..##tmp') for xml path ('ColumnDef') end