Listing Tables and Columns

When you don’t know what tables are in a database, you can make use of a system stored procedure to get the information for you. To get information on the tables in a database, use the sp_help procedure to list all the tables (and other objects) in the database:

     exec   sp_help

   Name                             Owner            Object_type
   ------------------------         ----------        ------------
   titleview                        dbo             view
   discounts                        dbo             user table
   employee                         dbo             user table
   jobs                             dbo             user table
   pub_info                         dbo             user table
   publishers                       dbo             user table
   roysched                         dbo             user table
   sales                            dbo             user table
   stores                           dbo             user table
   titleauthor                      dbo             user table
   titles                           dbo             user table
   employee_insupd                  dbo             trigger
   sysalternates                    dbo             system table
   sysarticles                      dbo             system table
   syscolumns                       dbo             system table
   syscomments                      dbo             system table
   sysconstraints                   dbo             system table
   sysdepends                       dbo             system table
   sysindexes                       dbo             system table
   syskeys                          dbo             system table
   syslogs                          dbo             system table
   sysobjects                       dbo             system table
   sysprocedures                    dbo             system table
   sysprotects                      dbo             system table
   syspublications                  dbo             system table
   sysreferences                    dbo             system table
   syssegments                      dbo             system table
   syssubscriptions                 dbo             system table
   systypes                         dbo             system table
   sysusers                         dbo             system table
   byroyalty                        dbo             stored procedure
   reptq1                           dbo             stored procedure
   reptq2                           dbo             stored procedure
   reptq3                           dbo             stored procedure
   DF__authors__phone__03D09CBB     dbo             default
   DF__employee__hire_d__3473D416   dbo             default
   DF__employee__job_id__2FAF1EF9   dbo             default
   DF__employee__job_lv__3197676B   dbo             default
   DF__employee__pub_id__328B8BA4   dbo             default
   DF__jobs__job_desc__25319086     dbo             default
   DF__publisher__count__09897611   dbo             default
   DF__titles__pubdate__0F424F67    dbo             default
   DF__titles__type__0D5A06F5       dbo             default

User_type Storage_type Length  Prec  Scale Nullable  Default_name Rule_name
-------   -----------  -----   ---   ----  --------  ---------    -----
empid      char        9                   no        none          none
id         varchar     11                  no        none          none
tid        varchar     6                   no        none          none

The sp_help stored procedure produces output that displays the objects in the current database. We’re concerned with “user tables” at this point, so ignore the other objects listed here. (See the following note for a brief description of the other information that sp_help is providing here.) The first column, Name, lists the name of the table. The second column, Owner, lists the user that created the table. Most of the time, to keep things simple, only the database owner is allowed to create tables in a production database. The database owner is always referred to by the system as dbo. The third column lists the type of object.

---

Note:  Here is a brief breakdown of the different types of objects sp_help lists in the pubs database:

View: Accessed like a table, a view acts as a filter or lens by providing an alternate way to view data in tables. Views are discussed on Day 10.

User Table: So far, these are the only objects I’ve talked about. They are tables that contain data and were created by a user, as opposed to created by the system. Table creation is discussed on Day 9.

System Table: A table, created by the server, that the server uses to store information about itself. System tables are discussed briefly on Day 4.

Stored Procedure: There are two kinds of stored procedures. System stored procedures like sp_help are stored in the master database and are used to provide information or to manage the SQL server. They are collections of SQL code written by the database vendor. Ordinary stored procedures, like the ones listed here, are collections of SQL code, written by a user, which are stored and executed on the server. Stored procedures are covered on Day 16.

Trigger: A trigger is a special kind of stored procedure that executes when data is modified in a table. Triggers are an advanced topic that is covered on Day 19.

Default: A default is a value that gets inserted into a column when an explicit value is not provided. Defaults are discussed on Day 9, in the INSERT section.

At the very bottom of the screen, in a new result set, is a list of user-defined types in the pubs database. SQL Server allows special, user-defined datatypes, based on the primary datatypes, to be used in tables. These are discussed on Day 9.

---