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.
sp_sproc_columns procedure_name [, procedure_owner] [, procedure_qualifier] [, column_name]
where
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.
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 |
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. |