INSERT INTO
才能处理数据。这没问题,但如果不是我编写存储过程的人,除了列出其定义并阅读代码之外,我该如何确定如何定义我的临时表呢?例如,针对`EXEC sp_stored_procedure`的临时表应该是什么样子的?这是一个简单的存储过程,我可能可以猜测数据类型,但似乎有一种方式可以直接从执行过程返回的列中读取类型和长度。
INSERT INTO
才能处理数据。这没问题,但如果不是我编写存储过程的人,除了列出其定义并阅读代码之外,我该如何确定如何定义我的临时表呢?假设您在tempdb中有一个存储过程:
USE tempdb;
GO
CREATE PROCEDURE dbo.my_procedure
AS
BEGIN
SET NOCOUNT ON;
SELECT foo = 1, bar = 'tooth';
END
GO
有一种相当复杂的方法可以确定存储过程将输出的元数据。其中包括存储过程只能输出单个结果集,并且如果无法精确确定数据类型,则会进行最佳猜测。它需要使用OPENQUERY
和一个回环链接服务器,该服务器的'DATA ACCESS'
属性设置为true。您可以检查sys.servers以查看是否已经有有效的服务器,但让我们手动创建一个名为loopback
的服务器:
EXEC master..sp_addlinkedserver
@server = 'loopback',
@srvproduct = '',
@provider = 'SQLNCLI',
@datasrc = @@SERVERNAME;
EXEC master..sp_serveroption
@server = 'loopback',
@optname = 'DATA ACCESS',
@optvalue = 'TRUE';
现在您可以将其作为链接服务器查询,您可以使用任何查询的结果(包括存储过程调用)作为常规 SELECT
。因此,您可以执行以下操作(请注意,数据库前缀很重要,否则将出现错误11529和2812):
SELECT * FROM OPENQUERY(loopback, 'EXEC tempdb.dbo.my_procedure;');
SELECT *
,那么我们也可以执行SELECT * INTO
:SELECT * INTO #tmp FROM OPENQUERY(loopback, 'EXEC tempdb.dbo.my_procedure;');
一旦 #tmp 表存在,我们可以通过以下方式确定元数据(假设是 SQL Server 2005 或更高版本):
SELECT c.name, [type] = t.name, c.max_length, c.[precision], c.scale
FROM sys.columns AS c
INNER JOIN 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');
name type max_length precision scale
--------- ------- ---------- --------- -----
foo int 4 10 0
bar varchar 5 0 0
DECLARE @sql NVARCHAR(MAX) = N'EXEC tempdb.dbo.my_procedure;';
SELECT name, system_type_name
FROM sys.dm_exec_describe_first_result_set(@sql, NULL, 1);
结果:
name system_type_name
--------- ----------------
foo int
bar varchar(5)
在Denali之前,我建议你自己动手找出数据类型会更容易。这不仅因为按照上述步骤是很繁琐的,而且因为你很可能比引擎更能做出正确(或至少更准确)的猜测,因为引擎猜测的数据类型将基于运行时输出,而没有任何关于可能值域的外部知识。在Denali中,这个因素仍然存在,所以不要认为新的元数据发现功能是万能的,它们只是使上述过程稍微不那么繁琐。
另外,有关OPENQUERY
的其他潜在陷阱,请参见Erland Sommarskog的文章:
看起来在 SQL 2012 中有一个新的存储过程可以帮助解决这个问题。
exec sp_describe_first_result_set N'PROC_NAME'
一种不太复杂的方式(在某些情况下可能足够):编辑原始存储过程,在最终SELECT语句之后,FROM子句之前添加INSERT INTO tmpTable以将SP结果保存到tmpTable中。
运行修改后的SP,最好使用有意义的参数以获取实际数据。恢复存储过程的原始代码。
现在,您可以从SQL Server Management Studio获取tmpTable的脚本,或者查询sys.columns以获取字段描述。
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, '')
请注意,根据唯一标识符创建了一个新的表名。 现在找到代码中最后一个“From”单词,假设这是执行选择返回结果集的代码。
(Note:根据一个唯一标识符生成新的表名称;现在假设以下代码执行了select语句并返回结果集,请找出代码中最后一个'From'单词。)Select @SQLReverse = REVERSE(@SQL)
Select @StartPos = CHARINDEX('MORF', UPPER(@SQLReverse), 1)
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)
sys.dm_exec_describe_first_result_set
(适用于SQL Server 2012):SELECT column_ordinal, name, system_type_name
FROM sys.dm_exec_describe_first_result_set(N'EXEC stored_procedure_name', NULL, 0);
然而,这种解决方案有一些限制,例如SP不能使用临时表。
如果您在一个权限受限的环境中工作,像回环链接服务器这样的东西似乎是黑魔法,绝对是“不可能的事情!”但是您对模式有一些权限,并且只有几个存储过程需要处理,那么有一个非常简单的解决方案。
您可以使用非常有用的SELECT INTO语法,它将创建一个新表格,其中包含查询结果集。
假设您的存储过程包含以下Select查询:
SELECT x, y, z
FROM MyTable t INNER JOIN Table2 t2 ON t.id = t2.id...
而应该将其替换为:
SELECT x, y, z
INTO MyOutputTable
FROM MyTable t INNER JOIN Table2 t2 ON t.id = t2.id...
当您执行它时,它将使用查询返回的结果创建一个名为MyOutputTable的新表。
您只需右键单击其名称即可获取表定义。
就是这样!
SELECT INTO仅需要创建新表的能力,并且可以使用临时表(SELECT... INTO #MyTempTable),但是检索定义可能会更困难。
然而,如果您需要检索数千个SP的输出定义,则不是最快的方法:)