In SQL Server 2012, how do I get the column name and data type from a view, function, or stored procedure?

Question

I am able to obtain column names and data types from a database table with:

SELECT COLUMN_NAME, DATA_TYPE 
FROM information_schema.columns
WHERE TABLE_NAME = 'xxx'

How can I get the column name and data type from a view, function, or stored procedure? I imagine I'd have to obtain them using the results of each but I'm unsure.

Thanks for the help

For an SP see http://stackoverflow.com/questions/14574773/retrieve-column-names-and-types-of-a-stored-procedure — Alex K., Apr 25 '16 at 13:34

score 4 · Accepted Answer · edited May 23 '17 at 12:23

Columns for view:

SELECT * FROM sys.columns c where c.object_id = OBJECT_ID('<schema>.<view name>')

Columns for table valued function:

SELECT * FROM INFORMATION_SCHEMA.ROUTINE_COLUMNS rc WHERE rc.TABLE_NAME = '<udf name>'

Columns for stored procedure

For SQL server 2012 and later:

SELECT name, system_type_name
FROM sys.dm_exec_describe_first_result_set_for_object
(
  OBJECT_ID('<shcema>.<sp name>'), 
  NULL
);

Taken from Retrieve column names and types of a stored procedure?. Read answers there for possible ways to do this (for example with pre 2012).

In SQL Server 2012, how do I get the column name and data type from a view, function, or stored procedure?

1 Answers1