How to manage multiple tables, function, stored procedures and views (SQL Server)

Question

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.

I would like to have something like Stored procedure "A": @Input Arguments: Name nvarchar(30), Surname nvarchar(30), ID int TABLE VALUED FUNCTION "B" Input: Name nvarchar(30), Surname nvarchar(30), ID int Output: City nvarchar(25) — shanji97, Jun 06 '17 at 12:43

score 1 · Accepted Answer · answered Jun 06 '17 at 13:13

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:

Wow I've never seen such SQL magic like that, guess I need to study this a lot. — shanji97, Jun 06 '17 at 13:55

How to manage multiple tables, function, stored procedures and views (SQL Server)

1 Answers1