Получить определение столбца для набора результатов хранимой процедуры

Ответ 1

Итак, скажем, у вас есть хранимая процедура в 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;');

Если мы можем выполнить a SELECT *, мы также можем выполнить a 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');

(Если вы используете SQL Server 2000, вы можете сделать что-то подобное с syscolumns, но у меня нет экземпляра 2000 для проверки эквивалентного запроса.)

Результаты:

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, так что не создавайте впечатление, что новые функции обнаружения метаданных - это все-все-все, они просто делают вышеизложенное немного менее утомительным.

О, и для некоторых других потенциальных ошибок с OPENQUERY см. статью Эрланда Соммарскога здесь:

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

Ответ 2

Менее сложный способ (который может быть достаточным в некоторых случаях): отредактируйте исходный SP после окончательного SELECT и перед предложением FROM добавьте INSERT INTO tmpTable, чтобы сохранить результат SP в tmpTable.

Запустите модифицированный SP, желательно со значимыми параметрами, чтобы получить фактические данные. Восстановите исходный код процедуры.

Теперь вы можете получить script tmpTable из студии управления SQL-сервером или запросить sys.columns, чтобы получить описания полей.

Ответ 3

Вот код, который я написал. Идея состоит в том, чтобы (как кто-то еще сказал) получить код 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, '')

(Обратите внимание на создание нового имени таблицы на основе уникального идентификатора) Теперь найдите последнее слово "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)

Надеюсь, это поможет!

Ответ 4

Похоже, что в SQL 2012 для этого есть новый SP.

exec sp_describe_first_result_set N'PROC_NAME'

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

Ответ 5

Если вы работаете в среде с ограниченными правами, где такие вещи, как loopback-связанный сервер, кажутся черной магией и определенно "нет!", но у вас есть несколько прав на схему и только пара хранимых процедур для обработки очень простое решение.

Вы можете использовать очень полезный синтаксис 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, это не самый быстрый способ:)