Retreive the Schema of a Stored Procedure or Function

,

This script will get the schema for a stored procedure or function from the system tables.

 

Set the @Routine variable to the value of the object whose schema you are looking for.

CREATE Function [dbo].[ftGetRoutineSchema](@RoutineName varchar(200)) returns table as return 
--declare @routineName varchar(100);select @routineName='ftGetBom4Excel'
SELECT ColumnName=Case Is_Result
 When 'YES' then '@RC'
 else Parameter_Name
 end
 ,DataType= case Data_Type
 When 'DECIMAL' then 'Decimal('+convert(varchar,Numeric_precision)+','+Convert(varchar,Numeric_scale)+')'
 When 'numeric' then 'Decimal('+convert(varchar,Numeric_precision)+','+Convert(varchar,Numeric_scale)+')'
 when 'varchar' then 'Varchar('+Convert(varchar,Character_maximum_length)+')'
 ELSE dATA_TYPE 
 end
 ,ColumnOrder=Ordinal_Position 
 ,Direction =Case Parameter_Mode
 when 'INOUT' then 'Out'
 else Parameter_Mode
 end

FROM --INFORMATION_SCHEMA.ROUTINE_cOLUMNS
 Information_schema.Parameters 
--WHERE TABLE_NAME=@routineName --order by columnorder
 Where Specific_name=@ROUTINEnAME

Rate

2 (2)

Share

Share

Rate

2 (2)