Catalog stored procedures retrieve information from the system tables in tabular form. You can supply wildcard characters for some parameters.
sp_tables lists all user tables in a database when used in the following format:
sp_tables @table_type = "’TABLE’"
sp_columns returns the datatype of any or all columns in one or more tables in a database. You can use wildcard characters to get information about more than one table or column.
For example, the following command returns information about all columns that includes the string “id” in all the tables with “sales” in their name:
sp_columns "%sales%", null, null, "%id%"
table_qualifier table_owner
table_name column_name
data_type type_name precision length scale radix nullable
remarks
ss_data_type colid
--------------- -----------
---------- -----------
--------- --------- --------- ------ ----- ----- --------
-------
------------ -----
pubs2 dbo
sales stor_id
1 char 4 4 NULL NULL 0
NULL
47 1
pubs2 dbo
salesdetail stor_id
1 char 4 4 NULL NULL 0
NULL
4 1
pubs dbo
salesdetail title_id
12 varchar 6 6 NULL NULL 0
NULL
39 3
(3 rows affected, return status = 0)
