List Tables, Columns, and Datatypes

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)