sp_sproc_columns Catalog Stored Procedures

For SQL Server 6.5 information, see sp_sproc_columns in What's New for SQL Server 6.5.

Returns column information for a single stored procedure in the current environment.

Syntax

sp_sproc_columns procedure_name [, procedure_owner] [, procedure_qualifier] [, column_name]

where

procedure_name: Is the name of the procedure used to return catalog information. The procedure_name can be a variable character name with a maximum of 42 characters. Wildcard pattern matching is not supported.
procedure_owner: Is the name of the owner of the procedure used to return catalog information. The procedure_owner can be a variable character name with a maximum of 30 characters. Wildcard pattern matching is not supported. If procedure_owner is not specified, the default procedure visibility rules of the underlying DBMS apply.
In Microsoft SQL Server, if the current user owns a procedure with the specified name, that table's columns are returned. If procedure_owner is not specified and the current user does not own a procedure with the specified procedure_name, this procedure looks for a procedure with the specified procedure_name owned by the database owner. If one exists, that table's columns are returned.
procedure_qualifier: Specifies the name of the procedure qualifier. Various DBMS products support three-part naming for tables (qualifier.owner.name). In Microsoft SQL Server, this column represents the database name. In some products, it represents the server name of the table's database environment.
column_name: Specifies a single column and is used when only one column of catalog information is desired. If column_name is not specified, all columns will be returned. In Microsoft SQL Server, this column_name represents the column name as listed in the syscolumns table. The value specified can include wildcard characters using the underlying DBMS's wildcard matching patterns. For maximum interoperability, the gateway client should assume only ANSI-standard SQL pattern matching (the % and _ wildcard characters).

Remarks

The returned columns belong to the parameters or results set of a stored procedure. If the SP_NUM_PARAMETERS and SP_NUM_RESULT_SETS columns returned by sp_stored_procedure for a particular stored procedure are -1 (indeterminate), sp_proc_columns returns no rows for that stored procedure. In SQL Server 6.0, only the column information about input and output parameters for the stored procedure are returned.

The sp_sproc_columns catalog stored procedure is equivalent to SQLProcedureColumns in ODBC. The results returned are ordered by PROCEDURE_QUALIFIER, PROCEDURE_OWNER, PROCEDURE_NAME, and the order that the parameters appear in the procedure definition.

This is the results set:



Column	Datatype	Description

PROCEDURE_QUALIFIER	char(32)	Is the name of the procedure qualifier. Various DBMS products support three-part naming for tables (qualifier.owner.name). In Microsoft SQL Server, this column represents the database name. In some products, it represents the server name of the table's database environment. This field can be NULL.
PROCEDURE_OWNER	char(32)	Is the name of the procedure owner. Various DBMS products support three-part naming for tables (qualifier.owner.name). In Microsoft SQL Server, this column represents the name of the database user that created the table. This field will always return a value.
PROCEDURE_NAME	char(41)	Is the name of the procedure. Various DBMS products support three-part naming for tables (qualifier.owner.name). In Microsoft SQL Server, this column represents the procedure name as listed in the sysobjects table. This field will always return a value.
COLUMN_NAME	char(32)	Is the name of the column, for each column of the TABLE_NAME returned. In Microsoft SQL Server, this column represents the column name as listed in the syscolumns table. This field will always return a value.
COLUMN_TYPE	smallint	This field will always return a value. Can be: 0 SQL_PARAM_TYPE_UNKNOWN 1 SQL_PARAM_TYPE_INPUT 2 SQL_PARAM_TYPE_OUTPUT 3 SQL_RESULT_COL 4 SQL_PARAM_OUTPUT 5 SQL_RETURN_VALUE
DATA_TYPE	smallint	Is an integer code for an ODBC datatype. If this datatype cannot be mapped to an ANSI type, the value will be NULL. The native datatype name is returned in the TYPE_NAME column.
TYPE_NAME	char(30)	Is the string representation of the datatype. This is the datatype name as presented by the underlying DBMS.
PRECISION	int	Is the number of significant digits.
LENGTH	int	Is the transfer size of the data.
SCALE	smallint	Is the number of digits to the right of the decimal point.
RADIX	smallint	Is the base for numeric types.
NULLABLE	smallint	Specifies nullability. 1 means this datatype can be created allowing null values; 0 means null values are not allowed.
REMARKS	varchar(254)	Is a description of the procedure column. SQL Server will not return a value for this column.
SS_DATA_TYPE	tinyint	Is a SQL Server datatype, as defined in Microsoft SQL Server Programming DB-Library for C. The gateway converts the underlying data to this SQL Server datatype as the default, for use by Open Data Services gateway implementations of catalog stored procedures. If the gateway supports configurable datatype conversions, either per installation or per session, the conversion type when the stored procedure is executed is returned.
COLID	tinyint	Is a SQL Server - specific column added to the result set. This column does not need to appear in Open Data Services gateway implementations of catalog stored procedures.