[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
SQL Server 2012+
There are some new functions introduced in SQL Server 2012 that make metadata discovery much easier. For the above procedure we can do the following:
SELECT [column] = 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:
column 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: So far (through SQL Server 2022), this only works for the first resultset (as the name of the function implies).
FMTONLY
On older versions, 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.