Lesson 3: Working with SQL Server
August 14, 2007
In this lesson, you'll learn how to connect and log into SQL Server, how to issue SQL Server statements, and how to obtain information about databases and tables.
Making the Connection
Now that you have a SQL Server DBMS and client software to use with it, it would be worthwhile to briefly discuss connecting to the database.
SQL Server, like all client/server DBMSs, requires that you log into the DBMS before being able to issue commands. SQL Server can authenticate users and logins using its own user list, or using the Windows user list (the logins used to start using Windows). As such, depending on how SQL Server is configured, it may log you in automatically using whatever login you used for Windows itself, or it may prompt you for a login name and password.
When you first installed SQL Server, you were probably prompted for an administrative login (often named sa for system administrator) and a password. If you are using your own local server and are simply experimenting with SQL Server, using this login is fine. In the real world, however, the administrative login is closely protected because access to it grants full rights to create tables, drop entire databases, change logins and passwords, and more.
To connect to SQL Server, you need the following pieces of information:
If you're using one of the client applications discussed in the previous lesson, a dialog box will be displayed to prompt you for this information.
After you are connected, you have access to whatever databases and tables your login name has access to. (Logins, access control, and security are revisited in Lesson 29, "Managing Security.")
Selecting a Database
When you first connect to SQL Server, a default database is opened for you. This will usually be a database named master (which as a rule you should never play with). Before you perform any database operations, you need to select the appropriate database. To do this, you use the USE keyword.
For example, to use the crashcourse database, you would enter the following (in a query window):
Command(s) completed successfully.
The USE statement does not return any results. Depending on the client used, some form of notification might be displayed (as seen here).
Remember, you must always USE a database before you can access any data in it.
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:
DATABASE_NAME DATABASE_SIZE REMARKS ----------------- ------------- ------- coldfusion 9096 NULL crashcourse 3072 NULL forta 2048 NULL master 4608 NULL model 1728 NULL msdb 5824 NULL tempdb 8704 NULL
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, use sp_tables;, as seen here:
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:
sp_tables NULL, dbo, crashcourse, "'TABLE'";
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
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:
TABLE_QUALIFIER TABLE_OWNER TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME crashcourse dbo customers cust_id 4 int identity crashcourse dbo customers cust_name -8 nchar crashcourse dbo customers cust_address -8 nchar crashcourse dbo customers cust_city -8 nchar crashcourse dbo customers cust_state -8 nchar crashcourse dbo customers cust_zip -8 nchar crashcourse dbo customers cust_country -8 nchar crashcourse dbo customers cust_contact -8 nchar crashcourse dbo customers cust_email -8 nchar
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:
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.
In this lesson, you learned how to connect and log into SQL Server, how to select databases using USE, and how to introspect SQL databases, tables, and internals using stored procedures. Armed with this knowledge, you can now dig into the all-important SELECT statement.