Thursday, June 2, 2016

Get Column Name and DataType in Stored Procedure(Sql Sever 2012 Version Support)

//Column Alias Name and DataType Display

==>First Create Proc
create Proc SP_Demo
as
select 1 as Col1,2 Col2,'A' Col3

=======================================================
After Query Execute Stored Procedure

SELECT p.name,r.name,row_number() over (order by p.Name) Ord,
case when system_type_name  in ('int','smallint') then
       '####'
 when system_type_name like 'numeric%' then '######0.00' else '' end [Format] ,
 case when system_type_name in ('int','smallint') then
       '3'
 when system_type_name like 'numeric%' then '3'
 when system_type_name ='money' then '3'
 else '1' end [HALIGN],

 r.*
FROM sys.procedures AS p
CROSS APPLY sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS r WHERE p.name = 'SP_Demo'

=============================================================
==>Result :=        Name | system_type_name
                             Col1   | Int
                             Col2   | Int
                             Col3   | Varchar(1)


No comments: