[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).
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. 🙂
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
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
decimal. But that’s a start.