- Making the Connection
- Navigating SQL Server Management Studio
- Creating the Example Tables
- Selecting a Database
- Learning About Databases and Tables
- Summary
This chapter is from the book
This chapter is from the book
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 
sp_databases;
Output
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
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
sp_tables;
Analysis
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
sp_tables NULL, dbo, learnsql, "'TABLE'";
Output
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
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
sp_columns customers;
Output
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
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.