CODEWITHSUNDEEP
sql-serverssms
Shawn
Shawn

Reputation: 225

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

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:

Upvotes: 17

Views: 22792

Answers (8)

Bacon Bits
Bacon Bits

Reputation: 32220

In general for an existing table or view you'd want to use the system catalogs or INFORMATION_SCHEMA.COLUMNS, but if it's an arbitrary query you're looking at it's a bit more difficult.

I would use the sys.dm_exec_describe_first_result_set dynamic management view. Often, the biggest pain is having to single-quote escape your query.

DECLARE @tsql nvarchar(max) = N'SELECT YourFields FROM YourQuery';

SELECT *
FROM sys.dm_exec_describe_first_result_set(@tsql, null, 0);

See the documentation for more information.

Alternatively, you can use the sp_describe_first_result_set stored procedure and get basically the same results. (The dynamic management view is nice because you can more easily filter with a WHERE or sort with an ORDER BY.)

DECLARE @tsql nvarchar(max) = N'SELECT YourFields FROM YourQuery';

EXEC sp_describe_first_result_set @tsql, null, 0;

Again, see the doc for more info.

Upvotes: 4

Dennis C
Dennis C

Reputation: 24747

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

Upvotes: 15

Jason Kleban
Jason Kleban

Reputation: 20818

Here's one, if you can just SELECT ... INTO #Temp (remember #Temp is scoped to the session at worst) with some stolen from https://stackoverflow.com/a/14328779/162273:

SELECT 
    c.name AS UsefulRawName,
    ',' + c.name + ' ' + UPPER(t.name) + 
        CASE 
            WHEN t.name IN ('char', 'nchar', 'varchar', 'nvarchar') THEN '(' + CAST(c.max_length AS VARCHAR(3) ) + ')'
            WHEN t.name IN ('decimal', 'numeric') THEN '(' + CAST(c.[precision] AS VARCHAR(3) ) + ', ' + CAST(c.[scale] AS VARCHAR(3) ) + ')'
            ELSE '' END + CASE WHEN c.Is_Nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END AS SQLColumnType,
    'public ' + 
        CASE 
            WHEN t.name IN ('varchar', 'nvarchar', 'text', 'ntext', 'char', 'nchar', 'xml', 'sysname') THEN 'string'
            WHEN t.name IN ('binary', 'varbinary', 'image') THEN 'byte[]' + CASE WHEN c.Is_Nullable = 1 THEN '?' ELSE '' END
            WHEN t.name IN ('uniqueidentifier') THEN 'Guid' + CASE WHEN c.Is_Nullable = 1 THEN '?' ELSE '' END
            WHEN t.name IN ('datetimeoffset') THEN 'DateTimeOffset' + CASE WHEN c.Is_Nullable = 1 THEN '?' ELSE '' END
            WHEN t.name IN ('date', 'time', 'datetime2', 'smalldatetime', 'datetime') THEN 'DateTime' + CASE WHEN c.Is_Nullable = 1 THEN '?' ELSE '' END
            WHEN t.name IN ('numeric', 'decimal', 'real', 'money', 'smallmoney') THEN 'decimal' + CASE WHEN c.Is_Nullable = 1 THEN '?' ELSE '' END
            WHEN t.name IN ('float') THEN 'float' + CASE WHEN c.Is_Nullable = 1 THEN '?' ELSE '' END
            WHEN t.name IN ('tinyint', 'smallint') THEN 'short' + CASE WHEN c.Is_Nullable = 1 THEN '?' ELSE '' END
            WHEN t.name IN ('int') THEN 'int' + CASE WHEN c.Is_Nullable = 1 THEN '?' ELSE '' END
            WHEN t.name IN ('bit') THEN 'bool' + CASE WHEN c.Is_Nullable = 1 THEN '?' ELSE '' END
            WHEN t.name IN ('bigint') THEN 'long' + CASE WHEN c.Is_Nullable = 1 THEN '?' ELSE '' END
            WHEN t.name IN ('timestamp') THEN 'ulong'
            ELSE 'object' END + ' ' + c.name + ' { get; set; }' AS CSColumnType,
    c.name + ' = ' +
        CASE 
            WHEN t.name IN ('varchar', 'nvarchar', 'text', 'ntext', 'char', 'nchar', 'xml', 'sysname') THEN 'reader["' + c.name + '"] as string,'
            WHEN t.name IN ('binary', 'varbinary', 'image') THEN CASE 
                                                        WHEN c.Is_Nullable = 1 THEN 'reader["' + c.name + '"] as byte[]?,'
                                                        ELSE '(byte[])reader["' + c.name + '"],' END
            WHEN t.name IN ('uniqueidentifier') THEN CASE 
                                                        WHEN c.Is_Nullable = 1 THEN 'reader["' + c.name + '"] as Guid?,'
                                                        ELSE '(Guid)reader["' + c.name + '"],' END
            WHEN t.name IN ('datetimeoffset') THEN CASE 
                                                        WHEN c.Is_Nullable = 1 THEN 'reader["' + c.name + '"] as DateTimeOffset?,'
                                                        ELSE '(DateTimeOffset)reader["' + c.name + '"],' END
            WHEN t.name IN ('date', 'time', 'datetime2', 'smalldatetime', 'datetime') THEN CASE 
                                                        WHEN c.Is_Nullable = 1 THEN 'reader["' + c.name + '"] as DateTime?,'
                                                        ELSE '(DateTime)reader["' + c.name + '"],' END
            WHEN t.name IN ('numeric', 'decimal', 'real', 'money', 'smallmoney') THEN CASE 
                                                        WHEN c.Is_Nullable = 1 THEN 'reader["' + c.name + '"] as decimal?,'
                                                        ELSE '(decimal)reader["' + c.name + '"],' END
            WHEN t.name IN ('float') THEN CASE 
                                                        WHEN c.Is_Nullable = 1 THEN 'reader["' + c.name + '"] as float?,'
                                                        ELSE '(float)reader["' + c.name + '"],' END
            WHEN t.name IN ('tinyint', 'smallint') THEN CASE 
                                                        WHEN c.Is_Nullable = 1 THEN 'reader["' + c.name + '"] as short?,'
                                                        ELSE '(short)reader["' + c.name + '"],' END
            WHEN t.name IN ('int') THEN CASE 
                                                        WHEN c.Is_Nullable = 1 THEN 'reader["' + c.name + '"] as int?,'
                                                        ELSE '(int)reader["' + c.name + '"],' END
            WHEN t.name IN ('bit') THEN CASE 
                                                        WHEN c.Is_Nullable = 1 THEN 'reader["' + c.name + '"] as bool?,'
                                                        ELSE '(bool)reader["' + c.name + '"],' END
            WHEN t.name IN ('bigint') THEN CASE 
                                                        WHEN c.Is_Nullable = 1 THEN 'reader["' + c.name + '"] as long?,'
                                                        ELSE '(long)reader["' + c.name + '"],' END
            WHEN t.name IN ('timestamp') THEN '(ulong)reader["' + c.name + '"],'
            ELSE 'reader["' + c.name + '"] == DBNull.Value ? null : reader["' + c.name + '"],' END AS ReaderStatements
FROM tempDb.sys.columns c
INNER JOIN sys.types AS t ON c.system_type_id = t.system_type_id AND t.system_type_id   = t.user_type_id
WHERE [object_id] = OBJECT_ID('tempdb..#Temp')
ORDER BY column_id

A more motivated person could do some smarter mapping of the types to .Net equivalents automatically but this isn't so bad and keeps the snippet short.

Converting these types manually was enough of a pain in the butt that extending the script was worthwhile. Not sure my conversions are all correct.

Upvotes: 1

Dave Cluderay
Dave Cluderay

Reputation: 7426

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.

Upvotes: 8

cmsjr
cmsjr

Reputation: 59225

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)

Upvotes: 3

Bill Karwin
Bill Karwin

Reputation: 562871

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.

Upvotes: 0

Ricardo Villamil
Ricardo Villamil

Reputation: 5107

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

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

Upvotes: 1

Brian Clapper
Brian Clapper

Reputation: 26230

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'

Upvotes: 2

Related Questions