CODEWITHSUNDEEP
sqlsql-servert-sqlssms
Halcyon
Halcyon

Reputation: 14971

How To Determine Column Types In A SQL Server Results Set

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?

Upvotes: 2

Views: 133

Answers (2)

Stephan
Stephan

Reputation: 6018

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

Upvotes: 1

CindyH
CindyH

Reputation: 3026

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

Upvotes: 4

Related Questions