Recuperar definição de coluna para o conjunto de resultados do procedimento armazenado

Estou trabalhando com stored procedures no SQL Server 2008 e descobri que preciso INSERT INTO uma tabela temporária que foi predefinida para trabalhar com os dados. Tudo bem, exceto como eu descubro como definir minha tabela temporária, se eu não sou o único que escreveu o procedimento armazenado além de listar sua definição e ler o código?

Por exemplo, qual seria a aparência da minha tabela temporária para `EXEC sp_stored_procedure ‘? Esse é um procedimento armazenado simples, e eu provavelmente poderia adivinhar os tipos de dados, mas parece que deve haver uma maneira de apenas ler o tipo e o comprimento das colunas retornadas da execução do procedimento.

Então, digamos que você tenha um procedimento armazenado em tempdb:

 USE tempdb; GO CREATE PROCEDURE dbo.my_procedure AS BEGIN SET NOCOUNT ON; SELECT foo = 1, bar = 'tooth'; END GO 

Há uma maneira bastante complicada de determinar os metadados que o procedimento armazenado produzirá. Existem várias ressalvas, incluindo o procedimento que só pode produzir um único conjunto de resultados, e que um melhor palpite será feito sobre o tipo de dados, se não puder ser determinado com precisão. Ele requer o uso de OPENQUERY e um servidor vinculado de loopback com a propriedade 'DATA ACCESS' definida como true. Você pode verificar sys.servers para ver se você já tem um servidor válido, mas vamos apenas criar um chamado manualmente de loopback :

 EXEC master..sp_addlinkedserver @server = 'loopback', @srvproduct = '', @provider = 'SQLNCLI', @datasrc = @@SERVERNAME; EXEC master..sp_serveroption @server = 'loopback', @optname = 'DATA ACCESS', @optvalue = 'TRUE'; 

Agora que você pode consultar isso como um servidor vinculado, você pode usar o resultado de qualquer consulta (incluindo uma chamada de procedimento armazenado) como um SELECT regular. Então você pode fazer isso (note que o prefixo do database é importante, senão você obterá os erros 11529 e 2812):

 SELECT * FROM OPENQUERY(loopback, 'EXEC tempdb.dbo.my_procedure;'); 

Se pudermos executar um SELECT * , também podemos executar um SELECT * INTO :

 SELECT * INTO #tmp FROM OPENQUERY(loopback, 'EXEC tempdb.dbo.my_procedure;'); 

E uma vez que a tabela #tmp existe, podemos determinar os metadados dizendo (assumindo o SQL Server 2005 ou superior):

 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'); 

(Se você estiver usando o SQL Server 2000, poderá fazer algo semelhante com syscolumns, mas não tenho uma instância 2000 à mão para validar uma consulta equivalente.)

Resultados:

 name type max_length precision scale --------- ------- ---------- --------- ----- foo int 4 10 0 bar varchar 5 0 0 

Em Denali, isso será muito, muito mais fácil. Novamente, ainda há uma limitação do primeiro conjunto de resultados, mas você não precisa configurar um servidor vinculado e passar por todos esses arcos. Você pode apenas dizer:

 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); 

Resultados:

 name system_type_name --------- ---------------- foo int bar varchar(5) 

Até o Denali, eu sugiro que seria mais fácil simplesmente arregaçar as mangas e descobrir os tipos de dados por conta própria. Não apenas porque é tedioso passar pelas etapas acima, mas também porque é muito mais provável que você faça uma suposição correta (ou pelo menos mais precisa) do que o mecanismo, já que o tipo de dados supõe que o mecanismo será baseado no tempo de execução. saída, sem qualquer conhecimento externo do domínio de valores possíveis. Esse fator também será verdadeiro no Denali, então não fique com a impressão de que os novos resources de descoberta de metadados são um todo, tudo o que eles fazem é um pouco menos tedioso.

Ah, e para algumas outras pegadinhas em potencial com o OPENQUERY , veja o artigo de Erland Sommarskog aqui:

http://www.sommarskog.se/share_data.html#OPENQUERY

Uma maneira menos sofisticada (que pode ser suficiente em alguns casos): edite seu SP original, depois do SELECT final e antes da cláusula FROM, adicione INSERT INTO tmpTable para salvar o resultado do SP em tmpTable.

Execute o SP modificado, de preferência com parâmetros significativos para obter dados reais. Restaure o código original do procedimento.

Agora você pode obter o script de tmpTable do SQL Server Management Studio ou consultar sys.columns para obter as descrições dos campos.

Aqui está algum código que eu escrevi. A idéia é (como alguém afirmou) é pegar o código SP, modificá-lo e executá-lo. No entanto, meu código não altera o SP original.

Primeiro passo, pegue a definição do SP, retire a parte ‘Create’ e se livre do ‘AS’ após a declaração de parâmetros, se existir.

 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, '') 

(Observe a criação de um novo nome de tabela com base em um identificador exclusivo) Agora encontre a última palavra ‘De’ no código, assumindo que este é o código que faz o select que retorna o conjunto de resultados.

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

Altere o código para selecionar o conjunto de resultados em uma tabela (a tabela baseada no uniqueidentifier)

 Select @StartPos = LEN(@SQL) - @StartPos - 2 Select @SQL = STUFF(@SQL, @StartPos, 5, ' INTO ' + @TableName + ' FROM ') EXEC (@SQL) 

O conjunto de resultados está agora em uma tabela, não importa se a tabela está vazia!

Vamos pegar a estrutura da mesa

 Select * from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName 

Agora você pode fazer sua mágica com isso

Não se esqueça de deixar cair essa mesa única

 Select @SQL = 'drop table ' + @TableName Exec (@SQL) 

Espero que isto ajude!

Parece que no SQL 2012 existe um novo SP para ajudar nisso.

 exec sp_describe_first_result_set N'PROC_NAME' 

https://docs.microsoft.com/pt-br/sql/relational-databases/system-stored-procedures/sp-describe-first-result-set-transact-sql

Se você está trabalhando em um ambiente com direitos restritos, onde coisas como o servidor vinculado loopback parece magia negra e são definitivamente “de jeito nenhum!”, Mas você tem alguns direitos sobre o esquema e apenas alguns stored procedures para processar. solução.

Você pode usar a syntax muito útil do SELECT INTO , que criará uma nova tabela com o conjunto de resultados de uma consulta.

Digamos que seu procedimento contenha a seguinte consulta Select:

 SELECT x, y, z FROM MyTable t INNER JOIN Table2 t2 ON t.id = t2.id... 

Em vez disso, substitua-o por:

 SELECT x, y, z INTO MyOutputTable FROM MyTable t INNER JOIN Table2 t2 ON t.id = t2.id... 

Quando você vai executá-lo, ele irá criar uma nova tabela MyOutputTable com os resultados retornados pela consulta.

Você só precisa clicar com o botão direito no nome para obter a definição da tabela.

Isso é tudo !

SELECT INTO requer apenas a capacidade de criar novas tabelas e também funciona com tabelas temporárias (SELECT … INTO #MyTempTable), mas pode ser mais difícil recuperar a definição.

No entanto, claro, se você precisar recuperar a definição de saída de um SP de milhares, não é o caminho mais rápido 🙂