检索存储过程结果集的列定义

Question

我正在使用SQL Server 2008中的存储过程，并且已经了解到INSERT INTO必须使用已预定义的临时表才能使用数据。很好，除非我不是写存储过程的人（除了列出存储过程的定义和阅读代码），否则我如何弄清楚如何定义临时表？例如，EXEC sp_stored_procedure的临时表是什么样的？那是一个简单的存储过程，我可能会猜到数据类型，但是似乎必须有一种方法可以读取从执行该过程返回的列的类型和长度。

Answer 1

一种不太复杂的方法（在某些情况下可能已足够）：在原始SELECT之后和FROM子句之前添加原始SP，然后在tmpTable中添加INSERT INTO以将SP结果保存到tmpTable中。

运行修改后的SP，最好使用有意义的参数以获取实际数据。恢复该过程的原始代码。

现在，您可以从SQL Server Management Studio获取tmpTable的脚本，或查询sys.columns以获取字段描述。

Answer 2

这是我写的一些代码。想法是（如其他人所述）是获取SP代码，对其进行修改并执行。但是，我的代码不会更改原始SP。

第一步，获取SP的定义，去除“创建”部分，并在参数声明（如果存在）之后去除“ AS”。

Declare @SPName varchar(250)

Set nocount on

Declare @SQL Varchar(max), @SQLReverse Varchar(MAX), @StartPos int, @LastParameterName varchar(250) = '', @TableName varchar(36) = 'A' + REPLACE(CONVERT(varchar(36), NewID()), '-', '')

Select * INTO #Temp from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_NAME = 'ADMIN_Sync_CompareDataForSync'

if @@ROWCOUNT > 0

    BEGIN

        Select @SQL = REPLACE(ROUTINE_DEFINITION, 'CREATE PROCEDURE [' + ROUTINE_SCHEMA + '].[' + ROUTINE_NAME + ']', 'Declare') 

        from INFORMATION_SCHEMA.ROUTINES 

        where ROUTINE_NAME = @SPName

        Select @LastParameterName = PARAMETER_NAME + ' ' + DATA_TYPE + 

            CASE WHEN CHARACTER_MAXIMUM_LENGTH is not null THEN '(' + 

                CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN 'MAX' ELSE CONVERT(varchar,CHARACTER_MAXIMUM_LENGTH) END + ')' ELSE '' END 

        from #Temp 

        WHERE ORDINAL_POSITION = 

            (Select MAX(ORDINAL_POSITION) 

            From #Temp)

        Select @StartPos = CHARINDEX(@LastParameterName, REPLACE(@SQL, '  ', ' '), 1) + LEN(@LastParameterName)

    END

else

    Select @SQL = REPLACE(ROUTINE_DEFINITION, 'CREATE PROCEDURE [' + ROUTINE_SCHEMA + '].[' + ROUTINE_NAME + ']', '') from INFORMATION_SCHEMA.ROUTINES where ROUTINE_NAME = @SPName

DROP TABLE #Temp

Select @StartPos = CHARINDEX('AS', UPPER(@SQL), @StartPos)

Select @SQL = STUFF(@SQL, @StartPos, 2, '')

（请注意，基于唯一标识符创建了新的表名）现在，假设这是执行返回结果集的select的代码，请在代码中找到最后一个“ From”字。

Select @SQLReverse = REVERSE(@SQL)

Select @StartPos = CHARINDEX('MORF', UPPER(@SQLReverse), 1)

更改代码以将结果集选择到表中（该表基于uniqueidentifier）

Select @StartPos = LEN(@SQL) - @StartPos - 2

Select @SQL = STUFF(@SQL, @StartPos, 5, ' INTO ' + @TableName + ' FROM ')

EXEC (@SQL)

结果集现在在表中，表是否为空都没有关系！

让我们获取表的结构

Select * from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName

您现在可以用这个来做魔术了

别忘了删除该唯一表

Select @SQL = 'drop table ' + @TableName

Exec (@SQL)

希望这可以帮助！