In SQL Server, you can use the Transact-SQL PARAMETERS system information schema view to find the parameters used in a stored procedure or user-defined function.

More specifically, it returns one row for each parameter of a user-defined function or stored procedure that can be accessed by the current user in the current database.

To use this view, specify the fully qualified name of INFORMATION_SCHEMA.PARAMETERS.

Example 1 – Basic Usage

Here’s an example of returning information about the parameters used in all stored procedures and user-defined functions in the current database that the current user has access to.

SELECT 
  SPECIFIC_CATALOG AS [Database],
  SPECIFIC_SCHEMA AS [Schema],
  SPECIFIC_NAME AS [Table],
  PARAMETER_MODE AS [Mode],
  IS_RESULT,
  PARAMETER_NAME,
  DATA_TYPE
FROM INFORMATION_SCHEMA.PARAMETERS;

Result:

+------------+----------+----------------------+--------+-------------+------------------+-------------+
| Database   | Schema   | Table                | Mode   | IS_RESULT   | PARAMETER_NAME   | DATA_TYPE   |
|------------+----------+----------------------+--------+-------------+------------------+-------------|
| Music      | dbo      | ISOweek              | OUT    | YES         |                  | int         |
| Music      | dbo      | ISOweek              | IN     | NO          | @DATE            | datetime    |
| Music      | dbo      | ufn_AlbumsByGenre    | IN     | NO          | @GenreId         | int         |
| Music      | dbo      | spAlbumsFromArtist   | IN     | NO          | @ArtistName      | varchar     |
| Music      | dbo      | uspGetAlbumsByArtist | IN     | NO          | @ArtistId        | int         |
+------------+----------+----------------------+--------+-------------+------------------+-------------+

In this example I chose to return only seven columns from the view. The following example displays all columns.

Example 2 – Return All Columns

In this example I include all columns that the view returns. I also use vertical output so that you aren’t forced to scroll horizontally. Also, for the sake of brevity, I use a WHERE clause in order to return information about just one user-defined function that uses one parameter (therefore only one row is returned).

SELECT * 
FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_NAME = 'ufn_AlbumsByGenre';

Result (using vertical output):

SPECIFIC_CATALOG          | Music
SPECIFIC_SCHEMA           | dbo
SPECIFIC_NAME             | ufn_AlbumsByGenre
ORDINAL_POSITION          | 1
PARAMETER_MODE            | IN
IS_RESULT                 | NO
AS_LOCATOR                | NO
PARAMETER_NAME            | @GenreId
DATA_TYPE                 | int
CHARACTER_MAXIMUM_LENGTH  | NULL
CHARACTER_OCTET_LENGTH    | NULL
COLLATION_CATALOG         | NULL
COLLATION_SCHEMA          | NULL
COLLATION_NAME            | NULL
CHARACTER_SET_CATALOG     | NULL
CHARACTER_SET_SCHEMA      | NULL
CHARACTER_SET_NAME        | NULL
NUMERIC_PRECISION         | 10
NUMERIC_PRECISION_RADIX   | 10
NUMERIC_SCALE             | 0
DATETIME_PRECISION        | NULL
INTERVAL_TYPE             | NULL
INTERVAL_PRECISION        | NULL
USER_DEFINED_TYPE_CATALOG | NULL
USER_DEFINED_TYPE_SCHEMA  | NULL
USER_DEFINED_TYPE_NAME    | NULL
SCOPE_CATALOG             | NULL
SCOPE_SCHEMA              | NULL
SCOPE_NAME                | NULL

See the Microsoft documentation for information about each column returned by this view.