The Direct Approach

The sp_help stored procedure is nothing more than a bunch of SQL statements. When the procedure needs information about objects, such as tables, it accesses a system table called sysobjects.

You can look in sysobjects for information, too. To get a list of tables, use this query:

     select name
     from sysobjects
     where type = ‘U’

     name
     ------------------------------
     authors
     publishers
     titles
     titleauthor
     stores
     sales
     roysched
     discounts
     jobs
     pub_info
     employee

     (11 row(s) affected)

The query in this example asks for the names of all objects in the current database that are of type U (or “user table”). Other types in the sysobjects table include views (V), system tables (S), triggers (TR), stored procedures (P), primary keys (K), and foreign keys (F). The WHERE clause limits the rows in the result set to those matching a specific set of conditions—I talk more about that tomorrow. Be sure to use a capital letter in the WHERE clause.

This second method gives more precise results than does sp_help’s shotgun approach. Instead of wading through lots of objects, user defined types, and other stuff that doesn’t concern you, this lets you see just the names of tables.

Listing Columns in a Table

Once you have the name of a table, you will want information on the columns it contains. You can do this in at least a couple of ways.

The easy way is to use the sp_help procedure:

     exec sp_help authors

     Name               Owner             Type          When_created
     --------------     -------------     -----------   --------------
     authors            dbo               user table    Nov 2 1996 12:28PM

     Data_located_on_segment
     ------------------------------
     default
                                                  TrimTrailing FixedLen
   Column_name Type  Length Prec Scale  Nullable Blanks      NullInSource
    --------   ----   ----- ---  -----  -------  ---------   -----------
   au_id      id      11                no       yes          no
   au_lname   varchar 40                no       yes          no
   au_fname   varchar 20                no       yes          no
   phone      char    12                no       yes          no
   address    varchar 40                yes      yes          no
   city       varchar 20                yes      yes          no
   state      char    2                 yes      yes          yes
   zip        char    5                 yes      yes          yes
   contract   bit     1                 no       (n/a)        (n/a)

The sp_help stored procedure, when it is passed the name of a table, gives detailed information about the columns in that table. The name of the column is listed first, followed by the data type of the column, and the length (in bytes) of the column. The id column has a user-defined data type. User-defined types are based on base types and allow you to enforce specific rules on columns (length, null status, and so on).

Using SQL to Get Column Information

If all you want is the names of columns (which is often the case), a simple SQL statement will do the trick:

     select * from authors where 1=2

     au_id  au_lname  au_fname  phone  address  city  state  zip  contract
     -----  ------    ------    ------ -------  ----- -----  ---- -------

The WHERE clause is never true, so no rows in the table will match the condition. As a result, no rows are returned, but the column names are still listed.

Sorting Data with ORDER BY

So far, the data coming back has been in no particular order. When data is requested from the server using a SELECT and a FROM, the order that the rows come back to you is assumed by the server to be unimportant. This example does not sort the result set:

     select au_lname,  au_fname
     from authors

     au_lname                                 au_fname
     ------------------------                 ------------
     White                                    Johnson
     Green                                    Marjorie
     Carson                                   Cheryl
     O’Leary                                  Michael
     Straight                                 Dean
     Smith                                    Meander
     Bennet                                   Abraham
     Dull                                     Ann
     Gringlesby                               Burt
     Locksley                                 Charlene
     Greene                                   Morningstar
     Blotchet-Halls                           Reginald
     Yokomoto                                 Akiko
     del Castillo                             Innes
     DeFrance                                 Michel
     Stringer                                 Dirk
     MacFeather                               Stearns
     Karsen                                   Livia
     Panteley                                 Sylvia
     Hunter                                   Sheryl
     McBadden                                 Heather
     Ringer                                   Anne
     Ringer                                   Albert

All rows in the authors table were requested. The result set is not sorted by last name, and it isn’t sorted by first name. When no order is requested, the data is actually sorted according to whatever index the server used to retrieve the data. If no index exists, the data is returned in whatever order the rows were inserted into the table. Rather than waste time sorting the data, the server did its best to send back rows as fast as possible.