How to determine the datatypes of the results of a SQL?

Question

We have a SQL query that pulls a large number of fields from many tables/views from a database. We need to put a spec together for integration with a 3rd party, what is the quickest way to compile the data types of the result set?

Clarifications:

• There are 25+ tables/views involved, so functions at the table level will still be cumbersome.
• All work is currently being done in Microsoft SQL Server Management Studio.

Solution 1

You can run the query with SET FMTONLY ON, but that might not help you to easily determine the data types returned, since you're working in management studio only. If it was me, I think I'd create a view temporarily with the same body as the stored procedure (you may have to declare variables for any parameters). You can then look at the columns returned by the view with the INFORMATION_SCHEMA queries already discussed.

Solution 2

You might use few quick SQL statements to view result column type, by using temp table.

Temp tables is a little better then a view, as they are connection-local scope and will be cleared once disconnect.

All you need is inject few keyword as follow

SELECT
TOP 0 -- to speed up without access data
your,original,columns
INTO #T -- temp table magic
FROM originalTablesJoins
Order by anything
exec tempdb.sys.sp_columns #T
drop table #T

or;

SELECT TOP 0 *
INTO #T
FROM (
  select your,original,columns from originalTablesJoins -- remove order by if any
) x
exec tempdb.sys.sp_columns #T
drop table #T

Note: inspired by View schema of resultset in SQL Server Management Studio

Solution 3

If you're using SQL Server, metadata from various tables is available in the information_schema table. For instance, to get column metadata for table Foo, issue this query:

SELECT * FROM information_schema.columns WHERE table_name = 'Foo'

Solution 4

If you were using C# you could access it right from the field in the DataRow object:

Type columnNameType = row["columnName"].GetType();

Solution 5

And for an additional alternative you can use

sp_help  'Table_Name'

EDIT: Also, sp_help can be used for any object ( i.e. it would indicate the return type of stored procedure input and output variables)

View more solutions

Comments

[Shawn]

Creating the view to use the NFORMATION_SCHEMA queries was the missing step I needed!