Hi All,

Is it possible to get data-type,default-value, etc. (basically schema information) of columns in a particular query.

This is something similar to what we get when we execute the following 

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Employees'

But instead of TABLE_NAME i would like to give a query like

SELECT [EmployeeID],[LastName],[FirstName] from [dbo].[Employees] order by [EmployeeID]

The queries can be a bit complex too :(

It would be greatly helpful if you can scribble down some steps or redirect me to some URL which has such type of implementation.

Hello,

You can use sys.dm_exec_describe_first_result_set (Transact-SQL) for this.

Hi Olaf,

Thanks for the quick reply.

I checked the documentation and it seem to be exactly what i want.  

Unfortunately when I ran the same it gave me the following error.

"Invalid object name 'sys.dm_exec_describe_first_result_set'."

on further investigation I found its only supported by SQL 2012 and above.

Any idea if something similar existed for SQL 2008. (unfortunately one of my data sources is still SQL 2008 R2.)

Thanks once again for the help :)

regards,

Rohan W.

• Edited by Rohan W 19 hours 38 minutes ago wrong name

Actually I don't know a similar solution for older version on SQL Server side.

But on client/application side you can use the SET FMTONLY (Transact-SQL) option, then SQL Server only parse the statement and delivers meta data without executing it.

Hey Olaf,

I had tried this option in my C# calling code (using OleDB)

Unfortunately the data-type returned are OleDb data-types instead of SQL types, which is why i was looking for a SQL only solution.

eg.. 'VarChar' column gets converted to 'VarWChar'

Thanks again for the help :)

Why don't you use SqlClient in C#, it's the better Provider?

Anyway, you can map the types OleDB <=> SQL Server, see Data Type Mapping in Rowsets and Parameters

I would be using other sources as well like for Oracle and Teradata which I would be accessing using OleDb too.

So wanted to maintain consistency. [:(]

Hi Olaf,

Thanks for the quick reply.

I checked the documentation and it seem to be exactly what i want.  

Unfortunately when I ran the same it gave me the following error.

"Invalid object name 'sys.dm_exec_describe_first_result_set'."

on further investigation I found its only supported by SQL 2012 and above.

Any idea if something similar existed for SQL 2008. (unfortunately one of my data sources is still SQL 2008 R2.)

Thanks once again for the help :)

regards,

Rohan W.

• Edited by Rohan W Friday, September 11, 2015 11:26 AM wrong name

This is not pretty, but you can do the following.  Wrap the original query in a cte (commenting out the final ORDER BY if the query returns an ordered result set) and use that cte to do a SELECT INTO into a temp table WHERE 1=0.  Because of the 1=0, the query will run without looking at any data, so it will be very fast.  Then you can use INFORMATION_SCHEMA.COLUMNS on the temp table.  For example, suppose your query is

use AdventureWorks2008R2
go
SELECT        p.BusinessEntityID, p.Title, p.FirstName, p.MiddleName, p.LastName, p.Suffix, pp.PhoneNumber, pnt.Name AS PhoneNumberType, ea.EmailAddress, 
                         p.EmailPromotion, at.Name AS AddressType, a.AddressLine1, a.AddressLine2, a.City, sp.Name AS StateProvinceName, a.PostalCode, 
                         cr.Name AS CountryRegionName
FROM            Person.Person AS p INNER JOIN
                         Person.BusinessEntityAddress AS bea ON bea.BusinessEntityID = p.BusinessEntityID INNER JOIN
                         Person.Address AS a ON a.AddressID = bea.AddressID INNER JOIN
                         Person.StateProvince AS sp ON sp.StateProvinceID = a.StateProvinceID INNER JOIN
                         Person.CountryRegion AS cr ON cr.CountryRegionCode = sp.CountryRegionCode INNER JOIN
                         Person.AddressType AS at ON at.AddressTypeID = bea.AddressTypeID INNER JOIN
                         Sales.Customer AS c ON c.PersonID = p.BusinessEntityID LEFT OUTER JOIN
                         Person.EmailAddress AS ea ON ea.BusinessEntityID = p.BusinessEntityID LEFT OUTER JOIN
                         Person.PersonPhone AS pp ON pp.BusinessEntityID = p.BusinessEntityID LEFT OUTER JOIN
                         Person.PhoneNumberType AS pnt ON pnt.PhoneNumberTypeID = pp.PhoneNumberTypeID
WHERE        (c.StoreID IS NULL)
ORDER BY CountryRegionName, StateProvinceCode, City;

you can do

;WITH cte AS
(SELECT        p.BusinessEntityID, p.Title, p.FirstName, p.MiddleName, p.LastName, p.Suffix, pp.PhoneNumber, pnt.Name AS PhoneNumberType, ea.EmailAddress, 
                         p.EmailPromotion, at.Name AS AddressType, a.AddressLine1, a.AddressLine2, a.City, sp.Name AS StateProvinceName, a.PostalCode, 
                         cr.Name AS CountryRegionName
FROM            Person.Person AS p INNER JOIN
                         Person.BusinessEntityAddress AS bea ON bea.BusinessEntityID = p.BusinessEntityID INNER JOIN
                         Person.Address AS a ON a.AddressID = bea.AddressID INNER JOIN
                         Person.StateProvince AS sp ON sp.StateProvinceID = a.StateProvinceID INNER JOIN
                         Person.CountryRegion AS cr ON cr.CountryRegionCode = sp.CountryRegionCode INNER JOIN
                         Person.AddressType AS at ON at.AddressTypeID = bea.AddressTypeID INNER JOIN
                         Sales.Customer AS c ON c.PersonID = p.BusinessEntityID LEFT OUTER JOIN
                         Person.EmailAddress AS ea ON ea.BusinessEntityID = p.BusinessEntityID LEFT OUTER JOIN
                         Person.PersonPhone AS pp ON pp.BusinessEntityID = p.BusinessEntityID LEFT OUTER JOIN
                         Person.PhoneNumberType AS pnt ON pnt.PhoneNumberTypeID = pp.PhoneNumberTypeID
WHERE        (c.StoreID IS NULL)
--ORDER BY CountryRegionName, StateProvinceCode, City
)
SELECT *
INTO #TempQuery
FROM cte
WHERE 1=0;


SELECT OBJECT_ID(c.TABLE_NAME, 'tempdb'), *
FROM tempdb.INFORMATION_SCHEMA.COLUMNS c
INNER JOIN tempdb.sys.tables t ON c.TABLE_NAME = t.name
WHERE OBJECT_ID('tempdb..#TempQuery') = t.object_id
go

Drop Table #TempQuery;

to get the information about the result columns from your query.

Tom

This is not pretty, but you can do the following.  Wrap the original query in a cte (commenting out the final ORDER BY if the query returns an ordered result set) and use that cte to do a SELECT INTO into a temp table WHERE 1=0.  Because of the 1=0, the query will run without looking at any data, so it will be very fast.  Then you can use INFORMATION_SCHEMA.COLUMNS on the temp table.  For example, suppose your query is

use AdventureWorks2008R2
go
SELECT        p.BusinessEntityID, p.Title, p.FirstName, p.MiddleName, p.LastName, p.Suffix, pp.PhoneNumber, pnt.Name AS PhoneNumberType, ea.EmailAddress, 
                         p.EmailPromotion, at.Name AS AddressType, a.AddressLine1, a.AddressLine2, a.City, sp.Name AS StateProvinceName, a.PostalCode, 
                         cr.Name AS CountryRegionName
FROM            Person.Person AS p INNER JOIN
                         Person.BusinessEntityAddress AS bea ON bea.BusinessEntityID = p.BusinessEntityID INNER JOIN
                         Person.Address AS a ON a.AddressID = bea.AddressID INNER JOIN
                         Person.StateProvince AS sp ON sp.StateProvinceID = a.StateProvinceID INNER JOIN
                         Person.CountryRegion AS cr ON cr.CountryRegionCode = sp.CountryRegionCode INNER JOIN
                         Person.AddressType AS at ON at.AddressTypeID = bea.AddressTypeID INNER JOIN
                         Sales.Customer AS c ON c.PersonID = p.BusinessEntityID LEFT OUTER JOIN
                         Person.EmailAddress AS ea ON ea.BusinessEntityID = p.BusinessEntityID LEFT OUTER JOIN
                         Person.PersonPhone AS pp ON pp.BusinessEntityID = p.BusinessEntityID LEFT OUTER JOIN
                         Person.PhoneNumberType AS pnt ON pnt.PhoneNumberTypeID = pp.PhoneNumberTypeID
WHERE        (c.StoreID IS NULL)
ORDER BY CountryRegionName, StateProvinceCode, City;

you can do

;WITH cte AS
(SELECT        p.BusinessEntityID, p.Title, p.FirstName, p.MiddleName, p.LastName, p.Suffix, pp.PhoneNumber, pnt.Name AS PhoneNumberType, ea.EmailAddress, 
                         p.EmailPromotion, at.Name AS AddressType, a.AddressLine1, a.AddressLine2, a.City, sp.Name AS StateProvinceName, a.PostalCode, 
                         cr.Name AS CountryRegionName
FROM            Person.Person AS p INNER JOIN
                         Person.BusinessEntityAddress AS bea ON bea.BusinessEntityID = p.BusinessEntityID INNER JOIN
                         Person.Address AS a ON a.AddressID = bea.AddressID INNER JOIN
                         Person.StateProvince AS sp ON sp.StateProvinceID = a.StateProvinceID INNER JOIN
                         Person.CountryRegion AS cr ON cr.CountryRegionCode = sp.CountryRegionCode INNER JOIN
                         Person.AddressType AS at ON at.AddressTypeID = bea.AddressTypeID INNER JOIN
                         Sales.Customer AS c ON c.PersonID = p.BusinessEntityID LEFT OUTER JOIN
                         Person.EmailAddress AS ea ON ea.BusinessEntityID = p.BusinessEntityID LEFT OUTER JOIN
                         Person.PersonPhone AS pp ON pp.BusinessEntityID = p.BusinessEntityID LEFT OUTER JOIN
                         Person.PhoneNumberType AS pnt ON pnt.PhoneNumberTypeID = pp.PhoneNumberTypeID
WHERE        (c.StoreID IS NULL)
--ORDER BY CountryRegionName, StateProvinceCode, City
)
SELECT *
INTO #TempQuery
FROM cte
WHERE 1=0;


SELECT OBJECT_ID(c.TABLE_NAME, 'tempdb'), *
FROM tempdb.INFORMATION_SCHEMA.COLUMNS c
INNER JOIN tempdb.sys.tables t ON c.TABLE_NAME = t.name
WHERE OBJECT_ID('tempdb..#TempQuery') = t.object_id
go

Drop Table #TempQuery;

to get the information about the result columns from your query.

Tom

• Marked as answer by Rohan W 9 minutes ago

Wow Tom..

That's a neat way to handles this.. I'm pretty sure this method will suffice my requirements.

Thanks for the elaborate example too :)