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
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
CREATE PROCEDURE sp_GetQueryColumnInformation
@query NVARCHAR(MAX)
,@orderByName BIT = 0
,@showData BIT = 0
AS
BEGIN
DECLARE @sql NVARCHAR(MAX)
SET NOCOUNT ON;
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
)
IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE object_id = OBJECT_ID(N'tempdb..##TempQueryColumns') AND type in (N'U'))
DROP TABLE ##TempQueryColumns
SET @sql = 'SELECT * INTO ##TempQueryColumns FROM OPENQUERY(LocalServer, ''' + REPLACE(@query, '''', '''''') + ''')'
EXEC(@sql)
IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE object_id = OBJECT_ID(N'tempdb..##TempQueryColumns') AND type in (N'U'))
BEGIN
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')
SELECT *
FROM @tableColumns
ORDER BY CASE WHEN @orderByName = 0
THEN REPLACE(STR(column_id, 4), SPACE(1), '0')
ELSE column_name
END
IF (@showData = 1)
SELECT * FROM ##TempQueryColumns
DROP TABLE ##TempQueryColumns
END
END
GO
GO
EXEC sys.sp_MS_marksystemobject sp_GetQueryColumnInformation
GO
Output