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

Question

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

Я использую следующий SQL для получения имен и типов столбцов для таблицы или представления:

DECLARE @viewname varchar (250);

select a.name as colname,b.name as typename 
from syscolumns a, systypes b -- GAH!
where a.id = object_id(@viewname) 
and a.xtype=b.xtype 
and b.name <> 'sysname'

Как сделать что-то подобное для выходных столбцов хранимой процедуры?

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

Answer 2

Вы пытаетесь вернуть все хранимые процедуры и все их параметры? Нечто подобное должно работать для этого.

select * from information_schema.parameters

Если вам нужно получить столбцы, возвращаемые из хранимой процедуры, посмотрите здесь:

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