Home > Articles

  • Print
  • + Share This
This chapter is from the book

Learning About Databases and Tables

But what if you don’t know the names of the available databases? And for that matter, how do the client applications obtain the list of available databases that are displayed in the drop-down list?

Information about databases, tables, columns, users, privileges, and more, are stored within databases and tables themselves (yes, SQL Server uses SQL Server to store this information). These internal tables are all in the master database (which is why you don’t want to tamper with it), and they are generally not accessed directly. Instead, SQL Server includes a suite of prewritten stored procedures that can be used to obtain this information (information that SQL Server then extracts from those internal tables).

Look at the following example:

Input common.jpg

sp_databases;

Output common.jpg

DATABASE_NAME     DATABASE_SIZE REMARKS
----------------- ------------- -------
coldfusion        9096          NULL
learnsql          3072          NULL
forta             2048          NULL
master            4608          NULL
model             1728          NULL
msdb              5824          NULL
tempdb            8704          NULL

Analysis common.jpg

sp_databases; returns a list of available databases. Included in this list might be databases used by SQL Server internally (such as master and tempdb in this example). Of course, your own list of databases might not look like those shown above.

To obtain a list of tables within a database, make sure you are using the right database, and then use sp_tables;, as seen here:

Input common.jpg

sp_tables;

Analysis common.jpg

sp_tables; returns a list of available tables in the currently selected database, and not just your tables; it also includes all sorts of system tables and other entries (possibly hundreds of entries).

To obtain a list of tables (just tables, not views, and not system tables and so on), you can use this statement:

Input common.jpg

sp_tables NULL, dbo, learnsql, "'TABLE'";

Output common.jpg

TABLE_QUALIFIER TABLE_OWNER TABLE_NAME   TABLE_TYPE REMARKS
--------------- ----------- ------------ ---------- -------
crashcourse     dbo         customers    TABLE      NULL
crashcourse     dbo         orderitems   TABLE      NULL
crashcourse     dbo         orders       TABLE      NULL
crashcourse     dbo         products     TABLE      NULL
crashcourse     dbo         vendors      TABLE      NULL
crashcourse     dbo         productnotes TABLE      NULL
crashcourse     dbo         sysdiagrams  TABLE      NULL

Analysis common.jpg

Here, sp_tables accepts a series of parameters telling it which database to use, as well as what specifically to list ('TABLE' as opposed to 'VIEW' or 'SYSTEM TABLE').

sp_columns can be used to display a table’s columns:

Input common.jpg

sp_columns customers;

Output common.jpg

TABLE_QUALIFIER TABLE_OWNER TABLE_NAME COLUMN_NAME   DATA_TYPE TYPE_NAME
learnsql        dbo         customers  cust_id        4        int identity
learnsql        dbo         customers  cust_name     -8        nchar
learnsql        dbo         customers  cust_address  -8        nchar
learnsql        dbo         customers  cust_city     -8        nchar
learnsql        dbo         customers  cust_state    -8        nchar
learnsql        dbo         customers  cust_zip      -8        nchar
learnsql        dbo         customers  cust_country  -8        nchar
learnsql        dbo         customers  cust_contact  -8        nchar
learnsql        dbo         customers  cust_email    -8        nchar

Analysis common.jpg

sp_columns requires that a table name be specified (customers in this example), and returns a row for each field, containing the field name, its datatype, whether NULL is allowed, key information, default value, and much more.

Lots of other stored procedures are supported, too, including:

  • sp_server_info—Used to display extensive server status information

  • sp_spaceused—Used to display the amount of space used (and unused) by a database

  • sp_statistics—Used to display usage statistics pertaining to database tables

  • sp_helpuser—Used to display available user accounts

  • sp_helplogins—Used to display user logins and what they have rights to

It is worthwhile to note that client applications use these same stored procedures you’ve seen here. Applications that display interactive lists of databases and tables, that allow for the interactive creation and editing of tables, that facilitate data entry and editing, or that allow for user account and rights management, and more, all accomplish what they do using the same stored procedures that you can execute directly yourself.

  • + Share This
  • 🔖 Save To Your Account