Gather DB Info on the Fly

Use SQL Select statements to find information about your database.

DataStream

LanguageS: VB .NET

ASP.NET VERSIONS: 1.0 | 1.1

 

Gather DB Info on the Fly

Use SQL Select statements to find information about your database.

 

By Wayne S. Freeze

 

One thing that fascinates me about relational databases is all the information about the database is stored in the database itself using normal database tables. This means you can use a SQL Select statement to gather information about the database, which can useful if your application needs to determine information about the database system on the fly.

 

Collectively, the tables used by the database to store information about itself are named system tables. System tables vary from server to server and they even can vary from one version of the database system to the next. Although this means any code you write to access system tables might not work on the next version of the database, you always can isolate the code in a stored procedure or class library.

 

In this article, I'll show you some of the key system tables in SQL Server, how to get a list of tables in a database, and how to identify the columns and data type information for each table.

 

Find the System Tables

The system tables in SQL Server have names that begin with "sys." Information about the various objects in the database (including tables) are stored in the sysobjects table. Besides tables, this table contains several different types of constraints, stored procedures, views, functions, and keys.

 

The xtype column in the sysobjects table identifies the type of database object. A value of "U" indicates that the object is a user table, while a value of "S" indicates that the object is a system table. The object's name is stored in the name column. The id column contains an integer value that identifies the object uniquely; this value is used as a foreign key for several other system tables that contain information about a table.

 

You can retrieve an alphabetized list of tables in the database using this Select statement:

 

Select name

From sysobjects

Where xtype='U'

Order By name

 

Uncover Column Info and Data Types

Information about columns is stored in the syscolumns table. The name of the column is stored in the name field, and the colid field contains the relative position of the column within the table. The data type of the column is stored in the xtype column. Additional information about the column's data type is stored in the length, prec, and scale columns.

 

To translate the xtype field from the syscolumns table into something more meaningful, you need to use the systypes table. For the most part, the systypes table contains default information for the data type that may be overridden by the corresponding columns in the syscolumns table. The name field contains, however, the text string associated with the data type.

 

You can use this query to retrieve the name and data type information for each column in the Artists table:

 

Select c.Name, t.name 'Type', c.length, c.prec, c.scale, c.colid

From syscolumns c, sysobjects o, systypes t

Where o.name='Customers' and o.id = c.id and t.xtype = c.xtype

Order By c.colid

 

Use Stored Procedures

Although you could incorporate these statements into your application directly, using stored procedures provides a level of isolation between your program and the specific version of SQL Server. This means if Microsoft changes how the information is stored in the system tables for a future version of SQL Server, you won't have to change your application. All you have to do is update the stored procedures you use to get the information.

 

Figure 1 shows a simple program that collects information necessary to build a connection string, then calls the SysGetTables stored procedure to populate a dropdown list with all the user tables in the database. Pressing the Login button triggers the code shown in Figure 2.

 


Figure 1. The Database Query program prompts the user for database login information and retrieves a list of user tables from the database.

 

Sub Button1_Click(sender As Object, e As EventArgs)

 

Dim ds As New DataSet

 

Dim conn As New SqlConnection( _

   "Data source=" & DatabaseServer.Text & _

   ";User id=" & Userid.text & _

   ";Password=" & Password.Text & _

   ";Initial catalog=" & Database.Text)

 

Dim cmd As New SqlCommand("SysGetTables", conn)

 

Dim adpt As New SqlDataAdapter(cmd)

 

adpt.Fill(ds, "Tables")

 

Tables.DataSource = ds.Tables("Tables")

Tables.DataTextField = "Name"

Tables.DataBind()

 

End Sub

Figure 2. This code retrieves a list of tables from the database based on the database server, database name, user id, and password values input by the user.

 

This routine begins by building a SqlConnection object using the information from the DatabaseServer, Userid, Password, and Database textboxes. Then it creates a SqlCommand object that calls the SysGetTables stored procedure and a SqlDataAdapter object to retrieve the information from the database. After calling the Fill method, the resulting table is bound to the Tables dropdown list to make the list of tables available to the user.

 

Get Column Information

Another stored procedure call using the currently selected table is all that's required to retrieve the list of columns that comprise the table (see Figure 3). The only significant differences between this routine and the previous one is that the call to the SysGetColumns stored procedure requires the @Tables parameter, and the results of the query are bound to a datagrid. Figure 4 shows the structure of the Customers table in the Music database.

 

Sub Button3_Click(sender As Object, e As EventArgs)

 

Dim ds As New DataSet

 

Dim conn As New SqlConnection( _

   "Data source=" & DatabaseServer.Text & _

   ";User id=" & Userid.text & _

   ";Password=" & Password.Text & _

   ";Initial catalog=" & Database.Text)

 

Dim cmd As New SqlCommand("SysGetColumns", conn)

 

Dim adpt As New SqlDataAdapter(cmd)

 

cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add("@Table", SqlDbType.Varchar, 255).Value = _

   Tables.SelectedItem.Value

 

adpt.Fill(ds, "Columns")

 

DataGrid1.DataSource = ds.Tables("Columns")

DataGrid1.DataBind()

 

End Sub

Figure 3. This code displays the columns associated with a particular table.

 


Figure 4. A DataGrid control is used to display the columns contained in the Customers table.

 

The sample code in this article is available for download.

 

Wayne S. Freeze is a full-time computer book author with more than a dozen titles to his credit, including Windows Game Programming with Visual Basic and DirectX (Que) and Unlocking OLAP with SQL Server and Excel 2000 (Hungry Minds). He has more than 25 years of experience using all types of computers, from small, embedded microprocessor control systems to large-scale IBM mainframes. Freeze has a master's degree in management information systems as well as degrees in computer science and engineering. You can visit his Web site at http://www.JustPC.com and send e-mail to mailto:[email protected]. He loves reading e-mail from his readers, whose ideas, questions, and insights often provide inspiration for future books and articles.

 

 

 

 

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish