Retrieve Column Names and Types of a Stored Procedure

Retrieve column names and types of a stored procedure?

[I just realized I've answered this question before]

Doing this for a stored procedure is a lot more complicated than it is for a view or table. One of the problems is that a stored procedure can have multiple different code paths depending on input parameters and even things you can't control like server state, time of day, etc. So for example what you would expect to see as the output for this stored procedure? What if there are multiple resultsets regardless of conditionals?

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

If your stored procedure does not have code paths and you are confident that you will always see the same result set (and can determine in advance what values should be supplied if a stored procedure has non-optional parameters), let's take a simple example:

CREATE PROCEDURE dbo.bar
AS
BEGIN
  SET NOCOUNT ON;

  SELECT a = 'a', b = 1, c = GETDATE();
END
GO

FMTONLY

One way is to do something like this:

SET FMTONLY ON;
GO
EXEC dbo.bar;

This will give you an empty resultset and your client application can take a look at the properties of that resultset to determine column names and data types.

Now, there are a lot of problems with SET FMTONLY ON; that I won't go into here, but at the very least it should be noted that this command is deprecated - for good reason. Also be careful to SET FMTONLY OFF; when you're done, or you'll wonder why you create a stored procedure successfully but then can't execute it. And no, I'm not warning you about that because it just happened to me. Honest. :-)

OPENQUERY

By creating a loopback linked server, you can then use tools like OPENQUERY to execute a stored procedure but return a composable resultset (well, please accept that as an extremely loose definition) that you can inspect. First create a loopback server (this assumes a local instance named 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';

Now we can take the procedure above and feed it into a query like this:

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

This ignores alias types (formerly known as user-defined data types) and also may show two rows for columns defined as, for example, sysname. But from the above it produces:

name   name
----   --------
b      int
c      datetime
a      varchar

Obviously there is more work to do here - varchar doesn't show length, and you'll have to get precision / scale for other types such as datetime2, time and decimal. But that's a start.

SQL Server 2012

There are some new functions in SQL Server 2012 that make metadata discovery much easier. For the above procedure we can do the following:

SELECT name, system_type_name
FROM sys.dm_exec_describe_first_result_set_for_object
(
  OBJECT_ID('dbo.bar'), 
  NULL
);

Among other things this actually provides precision and scale and resolves alias types for us. For the above procedure this yields:

name   system_type_name
----   ----------------
a      varchar(1)
b      int
c      datetime

Not much difference visually but when you start getting into all the different data types with various precision and scale you'll appreciate the extra work this function does for you.

The downside: In SQL Server 2012 at least these functions only work for the first resultset (as the name of the function implies).

Get column names/types returned from a stored procedure

Unless you're prepared to parse the contents of ROUTINE_DEFINITION in INFORMATION_SCHEMA.ROUTINES, then your best bet will be to execute the procedures, and read the column information from the records returned.

In .NET you can do this by reading the results of the stored procedure into a DataTable and querying the Columns property.

The reason there's no easy way to do this is a stored procedure could potentially return different result sets based on the parameters. There's no fixed result set format like there is with user defined functions.

Edit

As mentioned in the other answer, you will need to use SET FMTONLY ON to ensure no data is returned. There are some situations where SET FMTONLY won't work, e.g. when using #temp tables in your stored procedures, but there is a workaround.

Find Data Types and Column Names For Stored Procedure Result in SQL Server

Depending on how your procedure is written you may be able to get what you need from sp_describe_first_result_set.

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

You would need to parse out result set but easier then manually hunting all of the columns.

Retrieve column definition for stored procedure result set

So let's say you have a stored procedure in tempdb:

USE tempdb;
GO

CREATE PROCEDURE dbo.my_procedure
AS
BEGIN
    SET NOCOUNT ON;

    SELECT foo = 1, bar = 'tooth';
END
GO

There is a quite convoluted way you can go about determining the metadata that the stored procedure will output. There are several caveats, including the procedure can only output a single result set, and that a best guess will be made about the data type if it can't be determined precisely. It requires the use of OPENQUERY and a loopback linked server with the 'DATA ACCESS' property set to true. You can check sys.servers to see if you already have a valid server, but let's just create one manually called loopback:

EXEC master..sp_addlinkedserver 
    @server = 'loopback',  
    @srvproduct = '',
    @provider = 'SQLNCLI',
    @datasrc = @@SERVERNAME;

EXEC master..sp_serveroption 
    @server = 'loopback', 
    @optname = 'DATA ACCESS',
    @optvalue = 'TRUE';

Now that you can query this as a linked server, you can use the result of any query (including a stored procedure call) as a regular SELECT. So you can do this (note that the database prefix is important, otherwise you will get error 11529 and 2812):

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

If we can perform a SELECT *, we can also perform a SELECT * INTO:

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

And once that #tmp table exists, we can determine the metadata by saying (assuming SQL Server 2005 or greater):

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

(If you're using SQL Server 2000, you can do something similar with syscolumns, but I don't have a 2000 instance handy to validate an equivalent query.)

Results:

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

In Denali, this will be much, much, much easier. Again there is still a limitation of the first result set but you don't have to set up a linked server and jump through all those hoops. You can just say:

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

Results:

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

Until Denali, I suggest it would be easier to just roll up your sleeves and figure out the data types on your own. Not just because it's tedious to go through the above steps, but also because you are far more likely to make a correct (or at least more accurate) guess than the engine will, since the data type guesses the engine makes will be based on runtime output, without any external knowledge of the domain of possible values. This factor will remain true in Denali as well, so don't get the impression that the new metadata discovery features are a be-all end-all, they just make the above a bit less tedious.

Oh and for some other potential gotchas with OPENQUERY, see Erland Sommarskog's article here:

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

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

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

Is there a way to retrieve the column names and data types a PL/SQL stored procedure returns?

Here's some example code showing how you can call procs from EF code without importing them into your model - essentially you are pulling out the OracleCommand object:

var ctx = new TestContext();
var cmd = ctx.Database.Connection.CreateCommand() as OracleCommand;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "SOMESTOREDPROC";
var p_rc1 = new OracleParameter("p_rc1", OracleDbType.RefCursor, ParameterDirection.Output);
var p_rc2 = new OracleParameter("p_rc2", OracleDbType.RefCursor, ParameterDirection.Output);
cmd.Parameters.Add(p_rc1);
cmd.Parameters.Add(p_rc2);

if (ctx.Database.Connection.State != ConnectionState.Open)
    ctx.Database.Connection.Open();

var reader = cmd.ExecuteReader(); 

Find all stored procedures that reference a specific column in some table

One option is to create a script file.

Right click on the database -> Tasks -> Generate Scripts

Then you can select all the stored procedures and generate the script with all the sps. So you can find the reference from there.

Or

-- Search in All Objects
SELECT OBJECT_NAME(OBJECT_ID),
definition
FROM sys.sql_modules
WHERE definition LIKE '%' + 'CreatedDate' + '%'
GO

-- Search in Stored Procedure Only
SELECT DISTINCT OBJECT_NAME(OBJECT_ID),
object_definition(OBJECT_ID)
FROM sys.Procedures
WHERE object_definition(OBJECT_ID) LIKE '%' + 'CreatedDate' + '%'
GO

Source SQL SERVER – Find Column Used in Stored Procedure – Search Stored Procedure for Column Name

get column names from stored procedure for displaying in html table using php

The question has nothing to do with stored procedures, as it's applicable to any data returned from database.

the most convenient way would be to fetch the data first, get the column names from the first row and then print the data.

besides, use of prepare execute is not justified for this query

$data = $db->query( "CALL spalldetails" )->fetchAll(PDO::FETCH_ASSOC);
$column_names = array_keys($data[0]);

here you have your column names before output which you can make using foreach() instead of while()

---

Related Topics