Ответ 1
[Я только понял, что ответил на этот вопрос раньше]
Выполнение этого для хранимой процедуры намного сложнее, чем для представления или таблицы. Одна из проблем заключается в том, что хранимая процедура может иметь несколько разных кодовых путей в зависимости от входных параметров и даже вещей, которые вы не можете контролировать, таких как состояние сервера, время суток и т.д. Так, например, что вы ожидаете увидеть в качестве вывода для эта хранимая процедура? Что делать, если имеется множество результирующих наборов независимо от условных выражений?
CREATE PROCEDURE dbo.foo
@bar INT
AS
BEGIN
SET NOCOUNT ON;
IF @bar = 1
SELECT a, b, c FROM dbo.blat;
ELSE
SELECT d, e, f, g, h FROM dbo.splunge;
END
GO
Если ваша хранимая процедура не имеет кодов, и вы уверены, что всегда будете видеть один и тот же набор результатов (и можете заранее определить, какие значения должны быть предоставлены, если хранимая процедура имеет необязательные параметры), простой пример:
CREATE PROCEDURE dbo.bar
AS
BEGIN
SET NOCOUNT ON;
SELECT a = 'a', b = 1, c = GETDATE();
END
GO
FMTONLY
Один из способов - сделать что-то вроде этого:
SET FMTONLY ON;
GO
EXEC dbo.bar;
Это даст вам пустой набор результатов, и ваше клиентское приложение может взглянуть на свойства этого набора результатов, чтобы определить имена столбцов и типы данных.
Теперь у SET FMTONLY ON; есть много проблем, о которых я не буду входить, но по крайней мере следует отметить, что это команда устарела - по уважительной причине. Также будьте осторожны с SET FMTONLY OFF;, когда вы закончите, или вы задаетесь вопросом, почему вы успешно создали хранимую процедуру, но затем не можете ее выполнить. И нет, я не предупреждаю вас об этом, потому что это случилось со мной. Честный.: -)
OPENQUERY
Создав связанный с петлей сервер, вы можете использовать такие инструменты, как OPENQUERY, чтобы выполнить хранимую процедуру, но верните композитный результирующий набор (ну, пожалуйста, примите это как очень свободное определение), которое вы можете проверить. Сначала создайте loopback-сервер (это предполагает локальный экземпляр с именем FOO):
USE master;
GO
EXEC sp_addlinkedserver @server = N'.\FOO', @srvproduct=N'SQL Server'
GO
EXEC sp_serveroption @server=N'.\FOO', @optname=N'data access',
@optvalue=N'true';
Теперь мы можем выполнить описанную выше процедуру и передать ее в такой запрос:
SELECT * INTO #t
FROM OPENQUERY([.\FOO], 'EXEC dbname.dbo.bar;')
WHERE 1 = 0;
SELECT c.name, t.name
FROM tempdb.sys.columns AS c
INNER JOIN sys.types AS t
ON c.system_type_id = t.system_type_id
WHERE c.[object_id] = OBJECT_ID('tempdb..#t');
Это игнорирует типы псевдонимов (ранее называемые пользовательскими типами данных), а также может показывать две строки для столбцов, определенных как, например, sysname. Но из вышесказанного это дает:
name name
---- --------
b int
c datetime
a varchar
Очевидно, что здесь больше работы - varchar не показывает длину, и вам нужно будет получить точность/масштаб для других типов, таких как datetime2, time и decimal. Но это начало.
SQL Server 2012
В SQL Server 2012 есть несколько новых функций, которые значительно упрощают обнаружение метаданных. Для вышеуказанной процедуры мы можем сделать следующее:
SELECT name, system_type_name
FROM sys.dm_exec_describe_first_result_set_for_object
(
OBJECT_ID('dbo.bar'),
NULL
);
Помимо всего прочего, это обеспечивает точность и масштабирование и разрешает типы псевдонимов для нас. Для вышеприведенной процедуры это дает:
name system_type_name
---- ----------------
a varchar(1)
b int
c datetime
Не так много различий визуально, но когда вы начинаете проникать во все разные типы данных с различной точностью и масштабированием, вы оцените дополнительную работу, которую эта функция делает для вас.
Недостаток: в SQL Server 2012 по крайней мере эти функции работают только для первого набора результатов (как следует из названия функции).