Export (0) Print
Expand All
Expand Minimize

sp_sproc_columns

SQL Server 2000

  Topic last updated -- July 2003

Returns column information for a single stored procedure or user-defined function in the current environment.

Syntax

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

Arguments

[@procedure_name =] 'name'

Is the name of the procedure used to return catalog information. name is nvarchar(390), with a default of %, which means all tables in the current database. Wildcard pattern matching is supported.

[@procedure_owner =] 'owner'

Is the name of the owner of the procedure. owner is nvarchar(384), with a default of NULL. Wildcard pattern matching is supported. If 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, information about that procedure is returned. If owner is not specified and the current user does not own a procedure with the specified name, sp_sproc_columns looks for a procedure with the specified name that is owned by the database owner. If the procedure exists, information about its columns is returned.

[@procedure_qualifier =] 'qualifier'

Is the name of the procedure qualifier. qualifier is sysname, with a default of NULL. Various DBMS products support three-part naming for tables (qualifier.owner.name). In SQL Server, this parameter represents the database name. In some products, it represents the server name of the table's database environment.

[@column_name =] 'column_name'

Is a single column and is used when only one column of catalog information is desired. column_name is nvarchar(384), with a default of NULL. If column_name is omitted, all columns are returned. Wildcard pattern matching is supported. For maximum interoperability, the gateway client should assume only SQL-92-standard pattern matching (the % and _ wildcard characters).

[@ODBCVer =] 'ODBCVer'

Is the version of ODBC being used. ODBCVer is int, with a default of 2, which indicates ODBC version 2.0. For more information about the difference between ODBC version 2.0 and ODBC version 3.0, refer to the ODBC SQLProcedureColumns specification for ODBC version 3.0

Return Code Values

None

Result Sets
Column name Data type Description
PROCEDURE_QUALIFIER sysname Procedure qualifier name. This column can be NULL.
PROCEDURE_OWNER sysname Procedure owner name. This column always returns a value.
PROCEDURE_NAME nvarchar(134) Procedure name. This column always returns a value.
COLUMN_NAME sysname Column name for each column of the TABLE_NAME returned. This column always returns a value.
COLUMN_TYPE smallint This field always returns a value:

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 Integer code for an ODBC data type. If this data type cannot be mapped to an SQL-92 type, the value is NULL. The native data type name is returned in the TYPE_NAME column.
TYPE_NAME sysname String representation of the data type. This is the data type name as presented by the underlying DBMS.
PRECISION int Number of significant digits. The return value for the PRECISION column is in base 10.
LENGTH int Transfer size of the data.
SCALE smallint Number of digits to the right of the decimal point.
RADIX smallint Is the base for numeric types.
NULLABLE smallint Specifies nullability:

1 = Data type can be created allowing null values
0 = Null values are not allowed

REMARKS varchar(254) Description of the procedure column. SQL Server does not return a value for this column.
COLUMN_DEF nvarchar(4000) Default value of the column.
SQL_DATA_TYPE smallint Value of the SQL data type as it appears in the TYPE field of the descriptor. This column is the same as the DATA_TYPE column, except for the datetime and SQL-92 interval data types. This column always returns a value.
SQL_DATETIME_SUB smallint The datetime SQL-92 interval subcode if the value of SQL_DATA_TYPE is SQL_DATETIME or SQL_INTERVAL. For data types other than datetime and SQL-92 interval, this field is NULL.
CHAR_OCTET_LENGTH int Maximum length in bytes of a character or binary data type column. For all other data types, this column returns a NULL.
ORDINAL_POSITION int Ordinal position of the column in the table. The first column in the table is 1. This column always returns a value.
IS_NULLABLE varchar(254) Nullability of the column in the table. ISO rules are followed to determine nullability. An ISO SQL-compliant DBMS cannot return an empty string.

Displays YES if the column can include NULLS and NO if the column cannot include NULLS.

This column returns a zero-length string if nullability is unknown.

The value returned for this column is different from the value returned for the NULLABLE column.

SS_DATA_TYPE tinyint SQL Server data type used by extended stored procedures. For more information, see Data Types.

Remarks

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

sp_sproc_columns 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.

Permissions

Execute permissions default to the public role.

See Also

System Stored Procedures

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft