2

I have a stored procedure that I'm executing using SQL Server Management Studio (SSMS) using SQL Server 2014 and I get a results set back. I'm wondering if it's possible to discover the column types. For example, Let's say the results set has the following columns:

Id
Name
BirthDate
Address
State
ZipCode

I want to discover the type of each column (e.g. "Id" is an integer, "Name" is a string, etc.)

Can I do this in SSMS without having to look inside the stored procedure? Is there some SQL statement I can run to find out the column types?

Halcyon
  • 14,631
  • 17
  • 68
  • 99
  • duplicate question. Check out Aaron Bertrand's answer here http://stackoverflow.com/questions/7368864/retrieve-column-definition-for-stored-procedure-result-set – Abhi Mar 20 '15 at 16:29
  • The question @abhi referenced gives a ton of info on how to do it in older versions of SQL in addition to the nice quick answer for 2012 and up. This question isn't really a duplicate because it asks about a newer version, and has a smaller easier answer. – CindyH Mar 20 '15 at 16:36

2 Answers2

4

For Sql Server 2012 and up:

EXEC sp_describe_first_result_set N'SELECT * from customer', null, 0;

https://msdn.microsoft.com/en-us/library/ff878602.aspx

CindyH
  • 2,986
  • 2
  • 24
  • 38
1

If you only need the datatype then try this out. There is also other data type info you can return. Look at the documentation for SQL_VARIANT_PROPERTY()

https://msdn.microsoft.com/en-us/library/ms178550.aspx

SELECT TOP 1    SQL_VARIANT_PROPERTY(ID,'basetype') AS ID,
                SQL_VARIANT_PROPERTY(Name,'basetype') AS Name,
                SQL_VARIANT_PROPERTY(BirthDate,'basetype') AS BirthDate,
                SQL_VARIANT_PROPERTY([State],'basetype') AS [State],
                SQL_VARIANT_PROPERTY(Zipcode,'basetype') AS Zipcode                             
FROM yourTable

Theoretical Results:

ID     Name      Birthdate    State     Zipcode
-----------------------------------------------
int    varchar   datetime     varchar   int
Stephan
  • 5,891
  • 1
  • 16
  • 24