CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
JMP
JMP

Reputation: 7844

How do I return the SQL data types from my query?

I've a SQL query that queries an enormous (as in, hundreds of views/tables with hard-to-read names like CMM-CPP-FAP-ADD) database that I don't need nor want to understand. The result of this query needs to be stored in a staging table to feed a report.

I need to create the staging table, but with hundreds of views/tables to dig through to find the data types that are being represented here, I have to wonder if there's a better way to construct this table.

Can anyone advise how I would use any of the SQL Server 2008 tools to divine the source data types in my SQL 2000 database?

As a general example, I want to know from a query like:

SELECT Auth_First_Name, Auth_Last_Name, Auth_Favorite_Number 
FROM Authors

Instead of the actual results, I want to know that:

Auth_First_Name is char(25)
Auth_Last_Name is char(50)
Auth_Favorite_Number is int

I'm not interested in constraints, I really just want to know the data types.

Upvotes: 102

Views: 446526

Answers (13)

redcalx
redcalx

Reputation: 8657

For SQL Server 2012 and above: If you place the query into a string then you can get the result set data types like so:

DECLARE @query nvarchar(MAX) = 'select 12.1 / 10.1 AS [Column1]';
EXEC sys.sp_describe_first_result_set @query, NULL, 0;

There is also a function version of this, which would be called similarly:

DECLARE @query nvarchar(max) = 'select 12.1 / 10.1 AS [Column1]';
SELECT *
FROM sys.dm_exec_describe_first_result_set(@query, NULL, NULL);

The function has the advantage of that you can chose which columns to return in the SELECT; so may be easier to ingest.

Upvotes: 96

Amankhani MohammadJavad
Amankhani MohammadJavad

Reputation: 714

Checking data types. The first way to check data types for SQL Server database is a query with the SYS schema table. The below query uses COLUMNS and TYPES tables:

    SELECT C.NAME AS COLUMN_NAME,
       TYPE_NAME(C.USER_TYPE_ID) AS DATA_TYPE,
       C.IS_NULLABLE,
       C.MAX_LENGTH,
       C.PRECISION,
       C.SCALE
FROM SYS.COLUMNS C
JOIN SYS.TYPES T
     ON C.USER_TYPE_ID=T.USER_TYPE_ID
WHERE C.OBJECT_ID=OBJECT_ID('your_table_name');

In this way, you can find data types of columns.

Upvotes: 2

Aaron Bertrand
Aaron Bertrand

Reputation: 280570

You can also use...

SQL_VARIANT_PROPERTY()

...in cases where you don't have direct access to the metadata (e.g. a linked server query perhaps?).

In SQL Server 2005 and beyond you are better off using the catalog views (sys.columns) as opposed to INFORMATION_SCHEMA. Unless portability to other platforms is important. Just keep in mind that the INFORMATION_SCHEMA views won't change and so they will progressively be lacking information on new features etc. in successive versions of SQL Server.

Upvotes: 34

R.Alonso
R.Alonso

Reputation: 1075

This easy query return a data type bit. You can use this thecnic for other data types:

select CAST(0 AS BIT) AS OK

Upvotes: 0

rsidebot
rsidebot

Reputation: 21

I use a simple case statement to render results I can use in technical specification documents. This example does not contain every condition you will run into with a database, but it gives you a good template to work with.

SELECT
     TABLE_NAME          AS 'Table Name',
     COLUMN_NAME         AS 'Column Name',
     CASE WHEN DATA_TYPE LIKE '%char'
          THEN DATA_TYPE + '(' + CONVERT(VARCHAR, CHARACTER_MAXIMUM_LENGTH) + ')'
          WHEN DATA_TYPE IN ('bit', 'int', 'smallint', 'date')
          THEN DATA_TYPE
          WHEN DATA_TYPE = 'datetime'
          THEN DATA_TYPE + '(' + CONVERT(VARCHAR, DATETIME_PRECISION) + ')'
          WHEN DATA_TYPE = 'float'
          THEN DATA_TYPE
          WHEN DATA_TYPE IN ('numeric', 'money')
          THEN DATA_TYPE + '(' + CONVERT(VARCHAR, NUMERIC_PRECISION) + ', ' + CONVERT(VARCHAR, NUMERIC_PRECISION_RADIX) + ')'
     END                 AS 'Data Type',
     CASE WHEN IS_NULLABLE = 'NO'
          THEN 'NOT NULL'
          ELSE 'NULL'
     END                 AS 'PK/LK/NOT NULL'
FROM INFORMATION_SCHEMA.COLUMNS 
ORDER BY 
     TABLE_NAME, ORDINAL_POSITION

Upvotes: 2

Alias Varghese
Alias Varghese

Reputation: 2170

sp_describe_first_result_set

will help to identify the datatypes of query by analyzing datatypes of first resultset of query

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-describe-first-result-set-transact-sql?view=sql-server-2017

Upvotes: 1

Paul M Sorauer
Paul M Sorauer

Reputation: 747

There MUST be en easier way to do this... Low and behold, there is...!

"sp_describe_first_result_set" is your friend!

Now I do realise the question was asked specifically for SQL Server 2000, but I was looking for a similar solution for later versions and discovered some native support in SQL to achieve this.

In SQL Server 2012 onwards cf. "sp_describe_first_result_set" - Link to BOL

I had already implemented a solution using a technique similar to @Trisped's above and ripped it out to implement the native SQL Server implementation.

In case you're not on SQL Server 2012 or Azure SQL Database yet, here's the stored proc I created for pre-2012 era databases:

CREATE PROCEDURE [fn].[GetQueryResultMetadata] 
    @queryText VARCHAR(MAX)
AS
BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    --SET NOCOUNT ON;

    PRINT @queryText;

    DECLARE
                @sqlToExec NVARCHAR(MAX) = 
                    'SELECT TOP 1 * INTO #QueryMetadata FROM ('
                    +
                    @queryText
                    +
                    ') T;'
                    + '
                        SELECT
                                    C.Name                          [ColumnName],
                                    TP.Name                         [ColumnType],
                                    C.max_length                    [MaxLength],
                                    C.[precision]                   [Precision],
                                    C.[scale]                       [Scale],
                                    C.[is_nullable]                 IsNullable
                        FROM
                                    tempdb.sys.columns              C
                                        INNER JOIN
                                    tempdb.sys.types                TP
                                                                                ON
                                                                                        TP.system_type_id = C.system_type_id
                                                                                            AND
                                                                                        -- exclude custom types
                                                                                        TP.system_type_id = TP.user_type_id
                        WHERE
                                    [object_id] = OBJECT_ID(N''tempdb..#QueryMetadata'');
            '

    EXEC sp_executesql @sqlToExec

END

Upvotes: 26

user2074102
user2074102

Reputation:

This will give you everything column property related.

SELECT * INTO TMP1
FROM ( SELECT TOP 1 /* rest of your query expression here */ );

SELECT o.name AS obj_name, TYPE_NAME(c.user_type_id) AS type_name, c.*  
FROM sys.objects AS o   
JOIN sys.columns AS c  ON o.object_id = c.object_id  
WHERE o.name = 'TMP1';

DROP TABLE TMP1;

Upvotes: 6

Pawel Czapski
Pawel Czapski

Reputation: 1864

SELECT COLUMN_NAME,
       DATA_TYPE,
       CHARACTER_MAXIMUM_LENGTH
FROM information_schema.columns
WHERE TABLE_NAME = 'YOUR_TABLE_NAME'

You can use columns aliases for better looking output.

Upvotes: 16

bop
bop

Reputation: 575

select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH 
from INFORMATION_SCHEMA.COLUMNS 
where TABLE_NAME='yourTable';

Upvotes: 3

Trisped
Trisped

Reputation: 6007

You could also insert the results (or top 10 results) into a temp table and get the columns from the temp table (as long as the column names are all different).

SELECT TOP 10 *
INTO #TempTable
FROM <DataSource>

Then use: 

EXEC tempdb.dbo.sp_help N'#TempTable';

or

SELECT * 
FROM tempdb.sys.columns 
WHERE [object_id] = OBJECT_ID(N'tempdb..#TempTable');

Extrapolated from Aaron's answer here.

Upvotes: 39

LukeH
LukeH

Reputation: 269628

Can you get away with recreating the staging table from scratch every time the query is executed? If so you could use SELECT ... INTO syntax and let SQL Server worry about creating the table using the correct column types etc.

SELECT *
INTO your_staging_table
FROM enormous_collection_of_views_tables_etc

Upvotes: 7

erikkallen
erikkallen

Reputation: 34421

select * from information_schema.columns

could get you started.

Upvotes: 75

Related Questions