How do I return the SQL data types from my query

Question

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

User · Accepted Answer

select   from information schema columns   could get you started

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 sp describe first result set  query  null  0

User · Answer

select COLUMN NAME  DATA TYPE  CHARACTER MAXIMUM LENGTH  from INFORMATION SCHEMA COLUMNS  where TABLE NAME  yourTable

User · Answer

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

User · Answer

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     SQL VARIANT PROPERTY  Transact-SQL   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

User · Answer

This easy query return a data type bit  You can use this thecnic for other data types   select CAST 0 AS BIT  AS OK

User · Answer

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

User · Answer

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  lt DataSource gt    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

User · Answer

sp describe first result set   will help to identify the datatypes of query by analyzing datatypes of first resultset of query  https   docs microsoft com en-us sql relational-databases system-stored-procedures sp-describe-first-result-set-transact-sql view sql-server-2017

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

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to sql-server-2008