sp_columns

sp_columns returns information about the type of data that can be stored in one or more columns.

Syntax

server_name... sp_columns table_name [, table_owner ]
	[, table_qualifier] [, column_name]

Parameters

table_name – is the name of the table or view. Use wildcard characters to request information about more than one table.
table_owner – is the owner of the table or view. Use wildcard characters to request information about tables owned by more than one user. If you do not specify a table owner, sp_columns looks for tables owned by the current user.
table_qualifier – is ignored. Specify null.
column_name – is the name of the column for which you want information. Use wildcard characters to request information about more than one column.

Examples

Example 1 – displays information about all columns in the publishers table that begin with “p”:

1> DCSERVER...sp_columns "publishers", null, null, "p%"
table_qualifier                  table_owner                    table_name                     column_name                    data_type   type_name                                                                                                 	 precision   length      scale       radix       nullable   
	 remarks                                                                                                                                                                                                                                                       	 ss_data_type colid       remote_data_type 
-------------------------------- ------------------------------ ------------------------------ ------------------------------ ----------- ----------------------------------------------------------------------------------------------------------
	 ----------- ----------- ----------- ----------- -----------
	 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
	 ------------ ----------- ---------------- 
NULL                             SYSTEM                         PUBLISHERS                     PUB_ID                                   1 CHAR                                                                                                      
	        NULL           4        NULL           0           0
	 NULL                                                                                                                                                                                                                                                          	           47           1               96 
NULL                             SYSTEM                         PUBLISHERS                     PUB_NAME                                12 VARCHAR2                                                                                                  	        NULL          40        NULL           0           1
	 NULL                                                                                                                                                                                                                                                          	           39           2                1 

(2 rows affected)
(return status = 0)

Permissions

Any user can execute sp_columns.