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.

Answer 1

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'

Answer 2

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

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

Answer 3

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)

Answer 4

Note that result set metadata is different from table metadata, because SQL queries can include expressions, whose data types may not be the same as those in the tables they query.

Many SQL query interfaces give you some function to retrieve information about the result set metadata (datatypes, etc.).

The specific API functions you need to use depend on what language and query interface you're using. You haven't stated this.

For instance, if you're using ODBC, the SQLDescribeCol() function can give you information about the result set metadata.

Answer 5

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.