SELECT
sp_name =OBJS.name ,
sp_sql =asm.definition ,
sp_pmindex =PM.[ORDINAL_POSITION] ,
sp_paramname= PM.PARAMETER_NAME ,
sp_pmdatatype = PM.DATA_TYPE ,
sp_pmbitcount= ISNULL(PM.CHARACTER_OCTET_LENGTH ,ISNULL(PM.NUMERIC_PRECISION,sap.max_length )),
sp_pmlenght = ISNULL(PM.CHARACTER_MAXIMUM_LENGTH ,ISNULL(PM.NUMERIC_PRECISION_RADIX ,[sap.precision])),
sp_pmscale = sap.scale ,
sp_pmisout =PM.IS_RESULT ,
sp_pmmod= PM.PARAMETER_MODE
FROM sys.all_objects as OBJS
join [sys].[all_sql_modules] as asm on asm.object_id = OBJS.object_id
join INFORMATION_SCHEMA.PARAMETERS PM on PM.SPECIFIC_NAME =OBJS.name join sys.all_parameters as sap on sap.object_id =OBJS.object_id
where
PM.ORDINAL_POSITION =sap.parameter_id OBJS.type = 'P' OR OBJS.type = 'X' OR OBJS.type = 'PC' and OBJS.is_ms_shipped = 0
SELECT
sp_name =OBJS.name ,
sp_sql =asm.definition ,
sp_pmindex =PM.[ORDINAL_POSITION] ,
sp_paramname= PM.PARAMETER_NAME ,
sp_pmdatatype = PM.DATA_TYPE ,
sp_pmbitcount= ISNULL(PM.CHARACTER_OCTET_LENGTH ,ISNULL(PM.NUMERIC_PRECISION,sap.max_length )),
sp_pmlenght = ISNULL(PM.CHARACTER_MAXIMUM_LENGTH ,ISNULL(PM.NUMERIC_PRECISION_RADIX ,[sap.precision])),
sp_pmscale = sap.scale ,
sp_pmisout =PM.IS_RESULT ,
sp_pmmod= PM.PARAMETER_MODE
FROM sys.all_objects as OBJS
join [sys].[all_sql_modules] as asm on asm.object_id = OBJS.object_id
join INFORMATION_SCHEMA.PARAMETERS PM on PM.SPECIFIC_NAME =OBJS.name join sys.all_parameters as sap on sap.object_id =OBJS.object_id
where
PM.ORDINAL_POSITION =sap.parameter_id AND ( OBJS.type = 'P' OR OBJS.type = 'X' OR OBJS.type = 'PC)' and OBJS.is_ms_shipped = 0