• Article

Advanced Basics

SQL Server Metadata

Ken Spencer

Code download available at:AdvancedBasics0311.exe(135 KB)

Q Frequently when I am working on a project, I must find the name of a particular field in a table in SQL Server™ 2000. Other times I must find the exact name of a field and table in which it is located. Still other times I need to get the datatype of a particular field. How can I accomplish these tasks easily?

Q Frequently when I am working on a project, I must find the name of a particular field in a table in SQL Server™ 2000. Other times I must find the exact name of a field and table in which it is located. Still other times I need to get the datatype of a particular field. How can I accomplish these tasks easily?

A When I've needed the name of a field or a field's datatype, I've often opened the table I thought it was in and gone poking around. This process is not very efficient because the fields in a table are not in alphabetical order and as a result it's not easy to find a field, especially when the table has a large number of fields. After many rounds of this kind of trial and error searching, it dawned on me—why not build a tool that does the searching? That tool is the subject of this month's column.

A When I've needed the name of a field or a field's datatype, I've often opened the table I thought it was in and gone poking around. This process is not very efficient because the fields in a table are not in alphabetical order and as a result it's not easy to find a field, especially when the table has a large number of fields. After many rounds of this kind of trial and error searching, it dawned on me—why not build a tool that does the searching? That tool is the subject of this month's column.

The first version of the tool's interface is shown in Figure 1, which shows where I search for a field called Region in all tables in the Northwind sample database. Version 1 of the tool allows you to search for a field in either all tables or a specific table. You can also search for part of the field name (the default) or look for an exact match by unchecking the Contains box. This allows you to vary the search, as the output shows, to look for a word in a field name or find an exact field.

Figure 1 SQL Server Search Tool

Figure 1** SQL Server Search Tool **

Let's see how this tool was constructed. The first bit of code for the tool, shown in Figure 2, is used to execute the required SQL. I also added the following global variables at the form level:

Dim localServer, localDB, localUser, localPassword As String Dim localConnectionString As String Dim dsTables As DataSet

Figure 2 SQL Routines

Public Function RunSQLWithDataSet(ByVal sSQL As String, Optional _ ByVal TableName As String = "") As DataSet Dim oDataSet As New DataSet Dim oDataAdapter As SqlClient.SqlDataAdapter Try If TableName = "" Then TableName = "Table1" End If 'Create New DataAdapter oDataAdapter = New _ SqlClient.SqlDataAdapter(sSQL, localConnectionString) 'Fill Data Set from dataadapter oDataAdapter.Fill(oDataSet, TableName) 'Set return value of function Return oDataSet Catch e As Exception Throw New Exception( _ "An exception occured in RunSQLWithDataSet", e) Finally End Try End Function Public Function RunSQLScalar(ByVal sSQL As String) As String Dim oCommand As New SqlClient.SqlCommand Dim oConnection As New _ SqlClient.SqlConnection(localConnectionString) Dim ret As String Try oConnection.Open() oCommand.Connection = oConnection oCommand.CommandText = sSQL ret = oCommand.ExecuteScalar Return ret Catch e As Exception Throw New Exception("An exception occured in RunSQLScalar", e) Finally End Try End Function

To get access to the database, you must provide the user with a way to select the database they want to query. The form named frmDBInfo asks the user for the database login information. To display the form, frmSelectDB calls the SelectDBandLoadTables routine shown in Figure 3. This routine simply displays the form and retrieves the information from the user. Then to actually retrieve the tables, it calls the GetTableNames routine, shown here:

Function GetTableNames() _ As DataSet Dim SQL As String Dim ds As DataSet

Figure 3 SelectDBandLoadTables Routine

Sub SelectDBandLoadTables () 'Allow user to select database Dim frm As New frmDBInfo Me.SuspendLayout() frm.Location = Me.Location If localServer <> "" Then frm.txtServer.Text = localServer End If If localDB <> "" Then frm.txtDatabase.Text = localDB End If If localUser <> "" Then frm.txtUser.Text = localUser End If If localPassword <> "" Then frm.txtPassword.Text = localPassword End If frm.ShowDialog() If frm.DialogResult = DialogResult.OK Then 'load stored procs from database localServer = frm.txtServer.Text localDB = frm.txtDatabase.Text localUser = frm.txtUser.Text localPassword = frm.txtPassword.Text localConnectionString = "server=" & localServer & _ ";database=" & localDB & ";uid=" & _ localUser & ";pwd=" & localPassword & ";" Application.DoEvents() Try dsTables = GetTableNames() cboTables.DataSource = dsTables.Tables(0) cboTables.ValueMember = "Name" cboTables.DisplayMember = "Name" Catch exc As Exception StatusBar1.Text = "An error occurred." End Try Else End If frm.Close() Me.ResumeLayout() End Sub

The real work is done in the select statement:

SQL = "SELECT Name FROM sysobjects " SQL &= "WHERE (type = 'U') AND (status > 0) " SQL &= "order by Name"

This statement pulls the table names from the sysobjects table in the selected database (later I'll look at another way to do this). There is a tremendous amount of useful data in sysobjects and other system tables that can be extracted for your own purposes. Executing this SQL statement yields a list of all the tables which are returned in a DataSet:

ds = RunSQLWithDataSet(SQL) Return ds

The DataSet's table(0) is then loaded into the combobox. The table is also retained in the dsTables DataSet for later use.

Now, let's turn to another routine that I'll need to complete the search: GetTableDetails (see Figure 4). This routine uses the SQL query shown here to retrieve the details of a particular table, including field names, datatype, and maximum length:

SQL = "select '" & TableName & "' as TableName, " _ SQL &= "COLUMN_NAME, DATA_TYPE, " _ SQL &= "CHARACTER_MAXIMUM_LENGTH from " _ SQL &= "INFORMATION_SCHEMA.COLUMNS where " _ SQL &= "table_name = '" & TableName & "' "

Figure 4 GetTableDetails

Function GetTableDetails(ByVal TableName As String) As DataTable Dim ds As DataSet Dim rw, workrow As DataRow Dim dt As New DataTable("TableInfo") Dim localColumn, localDataType As String Dim localLength As Integer Dim localIdentity As Boolean Dim localOutput As String Dim SQL As String SQL = "select '" & TableName & "' as TableName, " _ SQL &= "COLUMN_NAME, DATA_TYPE, " _ SQL &= "CHARACTER_MAXIMUM_LENGTH from " _ SQL &= "INFORMATION_SCHEMA.COLUMNS where " _ SQL &= "table_name = '" & TableName & "' " ds = RunSQLWithDataSet(SQL, "RawTableInfo") dt.Columns.Add("TableName", Type.GetType("System.String")) dt.Columns.Add("ColumnName",Type.GetType("System.String")) dt.Columns.Add("DataType", Type.GetType("System.String")) dt.Columns.Add("Length", Type.GetType("System.Int64")) dt.Columns.Add("Identity", Type.GetType("System.Boolean")) For Each rw In ds.Tables("RawTableInfo").Rows If Not IsDBNull(rw.Item("Column_Name")) Then localColumn = rw.Item("Column_Name").ToString If Not IsDBNull(rw.Item("DATA_TYPE")) Then localDataType = rw.Item("DATA_TYPE").ToString End If If Not IsDBNull(rw.Item("CHARACTER_MAXIMUM_LENGTH")) Then localLength = CInt( rw.Item("CHARACTER_MAXIMUM_LENGTH") _ .ToString) Else localLength = 0 End If If LCase(localDataType) = "int" Then localIdentity = CheckColumnIdentity(TableName, localColumn) Else localIdentity = False End If workrow = dt.NewRow() workrow("TableName") = TableName workrow("ColumnName") = localColumn workrow("Datatype") = localDataType workrow("Length") = localLength workrow("Identity") = localIdentity dt.Rows.Add(workrow) End If Next Return dt End Function

GetTableDetails is called when the user clicks the Search button (called cmdSearch). More on this soon.

One more function that I need to look at is CheckColumnIdentity.

Function CheckColumnIdentity(ByVal TableName As String, _ ByVal ColumnName As String) As Boolean Dim SQL As String SQL = "SELECT COLUMNPROPERTY( OBJECT_ID('" & " _ SQL &= "TableName & "'),'" & ColumnName & _ SQL &= "','IsIdentity')" If CInt(RunSQLScalar(SQL)) = 1 Then Return True Else Return False End If End Function

This function takes a table name and column name as parameters and returns a Boolean value, indicating whether the column is an identity column. It uses COLUMNPROPERTY to determine if the column is an identity column. You can also use this property to determine if a column allows nulls, and so on.

Now, let's put this together by looking at the cmdSearch Click event code. The first few lines of code define the variables I'll need:

Dim dt, dttemp As DataTable Dim ds As New DataSet Dim tabletosearch As String Dim rw As DataRow Dim IsMatch As Boolean = False

The following two lines set tabletosearch to the name of the table to search and clears the output textbox:

tabletosearch = cboTables.Text txtOutput.ResetText()

The next few lines control how the search is handled. If chkAllTables is True, then all of the details for each table are loaded into the dt table. If chkAllTables is False, then dt is loaded with the details for only the selected table:

If chkAllTables.Checked Then For Each rw In dsTables.Tables(0).Rows dttemp = GetTableDetails(rw("Name").ToString) ds.Merge(dttemp) Next dt = ds.Tables(0) Else dt = GetTableDetails(tabletosearch) End If

Now that the tables are loaded, I can perform the search. The search is handled inside the For Each loop, which moves through all rows in the dt table:

For Each rw In dt.Rows

The next line sets IsMatch to False as the default for each field. IsMatch is a flag that determines whether to output the field:

IsMatch = False

Next, if chkContains is True, then InStr is used to determine if a match exists. If chkContains is False, then the Else clause checks for an exact match. If a match is found, IsMatch is set to True:

If chkContains.Checked Then If InStr(UCase(rw("ColumnName").ToString), _ UCase(txtSearchField.Text)) > 0 Then IsMatch = True End If Else If UCase(rw("ColumnName").ToString) = UCase(txtSearchField.Text) Then IsMatch = True End If End If

Now that the test is complete, I can output the data if IsMatch is True. The values in the various fields in the datatable are output to txtOutput, as shown here:

If IsMatch Then txtOutput.Text &= rw("TableName").ToString _ & " : " & rw("ColumnName").ToString & " - " txtOutput.Text &= rw("DataType").ToString _ & " (" & rw("Length").ToString & ")" If CBool(rw("Identity").ToString) Then txtOutput.Text &= " Identity" End If txtOutput.Text &= vbCrLf End If Next

That's it. There is not a lot of code to this tool, but it sure is handy.

The information schema views provided by SQL Server 2000 are used to retrieve some of the metadata in this application. These views provide a wealth of metadata about SQL Server databases. Just for fun, I tried using a few different views and found some really cool features. The following SQL statement pulls a list of the tables and views in the database, sorted by table name:

select * from INFORMATION_SCHEMA.Tables order by table_type, table_name

This SQL query pulls information about the stored procedures in the database, as shown in the following:

select * from INFORMATION_SCHEMA.ROUTINES where ROUTINE_TYPE='PROCEDURE'

And if that wasn't cool enough, check this out. The following code pulls the names of all the stored procedures and the code to create them:

select routine_name, routine_definition from INFORMATION_SCHEMA.ROUTINES where ROUTINE_TYPE='PROCEDURE'

The first three rows returned for the Northwind sample database are shown in Figure 5.

Figure 5 Sprocs and Code Used to Create Them

Stored Procedure Name (routine_name)

Ten Most Expensive Products

SQL to create it (routine_definition)

CREATE PROCEDURE "Ten Most Expensive Products" AS SET ROWCOUNT 10 SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice FROM Products ORDER BY Products.UnitPrice DESC

Stored Procedure Name (routine_name)

Employee Sales by Country

SQL to create it (routine_definition)

CREATE PROCEDURE "Employee Sales by Country" @Beginning_Date DateTime, @Ending_Date DateTime AS SELECT Employees.Country, Employees.LastName, Employees.FirstName, Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal AS SaleAmount FROM Employee

Stored Procedure Name (routine_name)

dt_removefromsourcecontrol

SQL to create it (routine_definition)

CREATE PROCEDURE dbo.dt_removefromsourcecontrol AS SET nocount ON declare @iPropertyObjectId int SELECT @iPropertyObjectId = (SELECT objectid FROM dbo.dtproperties WHERE property = 'VCSProjectID') EXEC dbo.dt_droppropertiesbyid

Now, let's brainstorm a minute. What can I do with this code? I can put it into my search routine and use InStr to search for particular fields. This would allow me to search not only tables but also stored procedures for the fields. Of course, I could add Views rather easily as well.

One change I would make to the SQL code is shown here:

select routine_name, routine_definition from INFORMATION_SCHEMA.ROUTINES where ROUTINE_TYPE='PROCEDURE' and left(routine_name,3) <> 'dt_' order by routine_name

This version filters out the dt_ procedures system procedures. It also sorts the output by procedure name. You should note that the order by clause here will not work if you run this query against Master db because group by and order by can't handle rows longer than 8060 bytes.

There are quite a few ways you can query a table about itself. I have only scratched the surface of the possibilities here.

Send your questions and comments for Ken to  basics@microsoft.com.

Ken Spencer works for 32X Tech (https://www.32X.com), where he provides training, software development, and consulting services on Microsoft technologies.