Query Results Column Information

Introduction

I often need to know the data type that is returned from a Stored Procedure or Dynamic query.  The Stored Procedure below will run the query and return the information about the columns.  It can also return the results as well.

Using the code

The code below includes a linked server command that you will need to run only once.  This is necessary in order query a Stored Procedure.  If you drop the code into SQL Server Management Studio and run it, the Stored Procedure will get created and marked as a system object.  This will allow you to run this Stored Procedure from any database. 

To use the Stored Procedure, follow one of the following examples

-- USAGE:  exec sp_GetQueryColumnInformation 'exec AdventureWorks.dbo.uspGetBillOfMaterials 893, ''2000-06-26''', 0, 1
-- USAGE:  exec sp_GetQueryColumnInformation 'SELECT * FROM AdventureWorks.Production.Product', 0, 0

NOTE: Because this is getting marked as a system object, the stored procedure name must start with "sp_", so don't try and change that part of the name.

Code

USE master
GO

/****** Object:  StoredProcedure [dbo].[sp_GetQueryColumnInformation]    Script Date: 07/02/2014 10:08:36 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetQueryColumnInformation]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GetQueryColumnInformation]
GO


--
-- USAGE:  exec sp_GetQueryColumnInformation 'exec AdventureWorks.dbo.uspGetBillOfMaterials 893, ''2000-06-26''', 0, 1
-- USAGE:  exec sp_GetQueryColumnInformation 'SELECT * FROM AdventureWorks.Production.Product', 0, 0
--

-- =============================================
-- Author:       David Elliott
-- Create date:  2014-07-02
-- Description:  Return column info
-- =============================================
CREATE PROCEDURE sp_GetQueryColumnInformation 
   @query        NVARCHAR(MAX)
  ,@orderByName  BIT = 0
  ,@showData     BIT = 0
AS
BEGIN
  DECLARE @sql NVARCHAR(MAX)
  
  
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;


  -- This stored procedure relies on a linked server to itself
  -- This is how to create it
  --
  --  EXEC sp_addlinkedserver 
  --    @server = 'LocalServer'
  --   ,@srvproduct = ''
  --   ,@provider = 'SQLNCLI'
  --   ,@datasrc = @@servername
  --  GO 

  -- 
  -- Table Variable To Hold Column Information
  --
  DECLARE @tableColumns TABLE
  (
     column_id    INT
    ,column_name  VARCHAR(200)
    ,dataType     VARCHAR(200)
    ,max_length   INT
    ,precision    TINYINT
    ,scale        INT
    ,is_nullable  BIT
    ,is_identity  BIT
  )

  
  -- Drop Global Temp Table
  IF  EXISTS (SELECT * FROM tempdb.sys.objects WHERE object_id = OBJECT_ID(N'tempdb..##TempQueryColumns') AND type in (N'U'))
    DROP TABLE ##TempQueryColumns

  -- Run Query And Put In Global Temp Table
  SET @sql = 'SELECT * INTO ##TempQueryColumns FROM OPENQUERY(LocalServer, ''' + REPLACE(@query, '''', '''''') + ''')'
  --SELECT @query, @sql
  EXEC(@sql)

  -- Check Global Temp Table Exists
  IF  EXISTS (SELECT * FROM tempdb.sys.objects WHERE object_id = OBJECT_ID(N'tempdb..##TempQueryColumns') AND type in (N'U'))
  BEGIN
  
    -- Get Column Information about Global Temp Table And Stor in Table Variable
    INSERT INTO @tableColumns (c.column_id, column_name, dataType, max_length, precision, scale, is_nullable, is_identity)
    SELECT c.column_id, c.name AS column_name, ct.name as dataType
      , CASE
        WHEN ct.name = 'nchar'    THEN c.max_length / 2
        WHEN ct.name = 'ntext'    THEN c.max_length / 2
        WHEN ct.name = 'nvarchar' THEN c.max_length / 2
        ELSE c.max_length
        END
      , c.precision, c.scale, c.is_nullable, c.is_identity
    FROM tempdb.sys.columns c
      INNER JOIN tempdb.sys.tables t ON c.object_id = t.object_id
      INNER JOIN tempdb.sys.types ct ON c.system_type_id = ct.system_type_id and c.system_type_id = ct.user_type_id
    WHERE t.name like '##TempQueryColumns%' AND ct.name NOT IN ('sysname')

    -- Return Information About Columns
    SELECT * 
    FROM @tableColumns 
    ORDER BY CASE WHEN @orderByName = 0 
      THEN REPLACE(STR(column_id, 4), SPACE(1), '0') 
      ELSE column_name 
      END

    -- Show Data --> If Requested
    IF (@showData = 1)
      SELECT * FROM ##TempQueryColumns
    
    -- Drop Global Temp Table
    DROP TABLE ##TempQueryColumns
  END

END
GO

GO

EXEC sys.sp_MS_marksystemobject sp_GetQueryColumnInformation
GO

Output