In SQL Server, the sp_sproc_columns system stored procedure enables you to get column information for a given stored procedure or user-defined function in the current system.

It’s equivalent to SQLProcedureColumns in ODBC.

Syntax

The syntax goes like this:

sp_sproc_columns [[@procedure_name = ] 'name']   
    [ , [@procedure_owner = ] 'owner']   
    [ , [@procedure_qualifier = ] 'qualifier']   
    [ , [@column_name = ] 'column_name']  
    [ , [@ODBCVer = ] 'ODBCVer']  
    [ , [@fUsePattern = ] 'fUsePattern']

All arguments are optional. See the Microsoft documentation for a detailed explanation of these.

Example 1 – Specify No Arguments

You can execute the stored procedure without providing any arguments. Like this:

EXEC sp_sproc_columns

Or even like this:

sp_sproc_columns

This returns all stored procedures and user-defined functions in the current database. When I run that against the WideWorldImporters sample database I get 9077 rows. In the next example I’ll narrow it down to just one.

Example 2 – Specify All Arguments

Here’s what it looks like if you include all arguments.

EXEC sp_sproc_columns 
  @procedure_name = 'GetCityUpdates',
  @procedure_owner = 'Integration',
  @procedure_qualifier = 'WideWorldImporters',
  @column_name = '@RETURN_VALUE',
  @ODBCVer = 2,
  @fUsePattern = 1;

Result (using vertical output):

PROCEDURE_QUALIFIER | WideWorldImporters
PROCEDURE_OWNER     | Integration
PROCEDURE_NAME      | GetCityUpdates;1
COLUMN_NAME         | @RETURN_VALUE
COLUMN_TYPE         | 5
DATA_TYPE           | 4
TYPE_NAME           | int
PRECISION           | 10
LENGTH              | 4
SCALE               | 0
RADIX               | 10
NULLABLE            | 0
REMARKS             | NULL
COLUMN_DEF          | NULL
SQL_DATA_TYPE       | 4
SQL_DATETIME_SUB    | NULL
CHAR_OCTET_LENGTH   | NULL
ORDINAL_POSITION    | 0
IS_NULLABLE         | NO
SS_DATA_TYPE        | 56

In this case, I return info about the return value of the GetCityUpdates stored procedure.

Example 3 – Specify Just the Stored Procedure (or Function)

Rather than including all arguments, you can include just the name of the stored procedure. However, if you don’t specify the @column_name, doing this will return a row for each column.

Also, you can make your code more concise by omitting the argument name (i.e. only including its value).

EXEC sp_sproc_columns GetCityUpdates;

Result (using vertical output):

-[ RECORD 1 ]-------------------------
PROCEDURE_QUALIFIER | WideWorldImporters
PROCEDURE_OWNER     | Integration
PROCEDURE_NAME      | GetCityUpdates;1
COLUMN_NAME         | @RETURN_VALUE
COLUMN_TYPE         | 5
DATA_TYPE           | 4
TYPE_NAME           | int
PRECISION           | 10
LENGTH              | 4
SCALE               | 0
RADIX               | 10
NULLABLE            | 0
REMARKS             | NULL
COLUMN_DEF          | NULL
SQL_DATA_TYPE       | 4
SQL_DATETIME_SUB    | NULL
CHAR_OCTET_LENGTH   | NULL
ORDINAL_POSITION    | 0
IS_NULLABLE         | NO
SS_DATA_TYPE        | 56
-[ RECORD 2 ]-------------------------
PROCEDURE_QUALIFIER | WideWorldImporters
PROCEDURE_OWNER     | Integration
PROCEDURE_NAME      | GetCityUpdates;1
COLUMN_NAME         | @LastCutoff
COLUMN_TYPE         | 1
DATA_TYPE           | -9
TYPE_NAME           | datetime2
PRECISION           | 27
LENGTH              | 54
SCALE               | 7
RADIX               | NULL
NULLABLE            | 1
REMARKS             | NULL
COLUMN_DEF          | NULL
SQL_DATA_TYPE       | -9
SQL_DATETIME_SUB    | 3
CHAR_OCTET_LENGTH   | NULL
ORDINAL_POSITION    | 1
IS_NULLABLE         | YES
SS_DATA_TYPE        | 0
-[ RECORD 3 ]-------------------------
PROCEDURE_QUALIFIER | WideWorldImporters
PROCEDURE_OWNER     | Integration
PROCEDURE_NAME      | GetCityUpdates;1
COLUMN_NAME         | @NewCutoff
COLUMN_TYPE         | 1
DATA_TYPE           | -9
TYPE_NAME           | datetime2
PRECISION           | 27
LENGTH              | 54
SCALE               | 7
RADIX               | NULL
NULLABLE            | 1
REMARKS             | NULL
COLUMN_DEF          | NULL
SQL_DATA_TYPE       | -9
SQL_DATETIME_SUB    | 3
CHAR_OCTET_LENGTH   | NULL
ORDINAL_POSITION    | 2
IS_NULLABLE         | YES
SS_DATA_TYPE        | 0

Example 4 – Other Variations

You can provide any variation of arguments to return just the information you require.

For example, you could return all information from a particular procedure owner:

EXEC sp_sproc_columns @procedure_owner = 'Integration'

Or you could get info on all return values from all procedures and user-defined functions:

EXEC sp_sproc_columns @column_name = '@RETURN_VALUE'

Of course, you can always include the procedure qualifier if you wish. In SQL Server, this parameter represents the database name. In some products, it represents the server name of the table’s database environment.

EXEC sp_sproc_columns 
  @column_name = '@RETURN_VALUE',
  @procedure_qualifier = 'WideWorldImporters';

Example 5 – Wildcard Characters

The @fUsePattern argument enables you to specify whether or not underscore (_), percent (%), and bracket ([ ]) characters are interpreted as wildcard characters.

For example, running the following code will return all procedures and user-defined functions that start with Get.

EXEC sp_sproc_columns 
  @procedure_name = 'Get%',
  @fUsePattern = 1;

So in my case, this returned procedures such as GetCityUpdates, GetCustomerUpdates, GetMovementUpdates, etc.

If I disable wildcard characters:

EXEC sp_sproc_columns 
  @procedure_name = 'Get%',
  @fUsePattern = 0;

I get no results.