I have multiple object in my database and i would like to list them give some description, accepted parameters and their types. The fastest way I know is to generate a database script and call it a day, but is there an easier way to archive this? I use Microsoft SSMS 2017 with SQL Server 2012.
You can query the table INFORMATION_SCHEMA.PARAMETERS
(or sys.parameters
).
Here is a simple query that extracts some of the data you requested; you can use this as a starting point and elaborate on this to get your desired output:
select P.SPECIFIC_SCHEMA,
P.SPECIFIC_NAME,
R.ROUTINE_TYPE,
P.PARAMETER_MODE,
P.PARAMETER_NAME,
P.DATA_TYPE,
P.CHARACTER_MAXIMUM_LENGTH
from INFORMATION_SCHEMA.PARAMETERS P
inner join INFORMATION_SCHEMA.ROUTINES R ON P.SPECIFIC_CATALOG = R.ROUTINE_CATALOG
AND P.SPECIFIC_SCHEMA = R.ROUTINE_SCHEMA
AND P.SPECIFIC_NAME = R.ROUTINE_NAME
order by P.SPECIFIC_SCHEMA, P.SPECIFIC_NAME, P.PARAMETER_MODE, P.PARAMETER_NAME
Here is the output of this query on a simple database: