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 include 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 pubs2 dbo salesdetail title_id 12 varchar 6 6 NULL NULL 0 NULL 39 3 (3 rows affected, return status = 0)