Accessing SQL Server Metadata

System Tables
This is probably the most common method that DBA's use to access metadata. I am a fan of this method because it's quick, straight forward, and can be accessed using TSQL 'Select' statements. This is a good way to access metadata if you need a one-time look at the data, i.e. your TSQL code will not persist past your current user session in Query Analyzer. But because the structure of system tables can change from release to release it is not a recommended method to use within persistant code. If you are looking for a simple TSQL way to access metadata for a project such as a software application or Internet application you will be better off using INFORMATION_SCHEMA Views instead, which are described below.

INFORMATION_SCHEMA Views
Retrieves database schema information using familiar TSQL 'Select' constructs. The schema information that is returned is based on the current user's permissions, in other words, the resultset will only contain data about database objects that you have access to. INFORMATION_SCHEMA enables you to retrieve metadata about Tables, Stored Procedures, Views, User Defined Functions, User Defined Data Types, Databases, and more. Support for INFORMATION_SCHEMA began with SQL Server 7.0 and carried over to both 2000 and 2005. Because these views are system table-independent, Microsoft has been encouraging users to use INFORMATION_SCHEMA instead of basing code on system tables directly if possible (Note: some usage statistics that are available through the system tables are not available through INFORMATION_SCHEMA).

--Get Information About Tables
Select * from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'-- returns information for each table
Select * from INFORMATION_SCHEMA.TABLES st 
Inner Join INFORMATION_SCHEMA.COLUMNS sc On st.TABLE_NAME = sc.TABLE_NAME where TABLE_TYPE = 'BASE TABLE' --returns column info for each table

--Get Information About Stored Procedures
Select * from INFORMATION_SCHEMA.Routines --returns stored procedures and functions
Select * from INFORMATION_SCHEMA.PARAMETERS --returns parameter information for user-defined functions and stored procedures

--Get Information About Views
Select * from INFORMATION_SCHEMA.VIEWS --returns information for each view
Select * from INFORMATION_SCHEMA.VIEW_TABLE_USAGE --returns each view and the tables it is using

SQL Server Distributed Management Objects (SQLDMO)
If you are building a software application that needs metadata from a SQL Server 7.0 or 2000 database this is the way to go. In fact, SQL Server 2000 Enterprise Manager uses SQLDMO for most of its actions. Database information is exposed nicely through an easy to use COM object library that can be used in any COM aware language such as Visual Basic, and can also be used in .Net - although it requires using Interop. While using SQLDMO you work in a hierarchical manner to access the data that you want - for example, a Server contains a collection of databases, a database contains a collection of tables, each table contains a collection of columns. Microsoft SQLDMO documentation states that applications using SQLDMO can perform all functions performed by SQL Server 2000 Enterprise Manager, making this a powerful but easy to use way to access metadata. SQLDMO is supported by SQL Server 7.0 and 2000, it can be used with SQL Server 2005 but was not updated to support 2005 specific features. SQLDMO is bundled up in sqldmo.dll so you simply need to add a reference to this object in your application. If you installed your SQL Server files to the default location on your PC the file should be located in: C:\Program Files\Microsoft SQL Server\80\Tools\Binn. Below is a SQLDMO example in Visual Basic, it should be simple enough to follow though regardless of your language of choice:

'***Before starting you must reference the SQLDMO Object Library in your project
'***From menu select Project -> References -> Microsoft SQLDMO Object Library

Dim objDMO As SQLServer
Set objDMO = New SQLDMO.SQLServer
Dim strServer As String
strServer = "My Server"

'---connect to server using nt authentication
objDMO.LoginSecure = True
objDMO.Connect strServer

Dim objDb As Database
Dim dbName As String
dbName = "MyDatabase"

'---get a reference to our database
Set objDb = objDMO.Databases(dbName)

'---loop through our tables
For Each oTable In objDb.Tables
	'---print out name and date of creation
	Debug.Print "Table Name = " & oTable.Name & " Date Created = " & oTable.CreateDate       
Next

For Each oView In objDb.Views
	'---print names of views
	Debug.Print "View Name = " & oView.Name
Next

'---clean up
Set objDb = Nothing
objDMO.DisConnect
Set objDMO = Nothing

SQL Management Objects (SMO)
SMO replaces SQLDMO in SQL Server 2005. As noted above, SQLDMO is compatible with SQL Server 2005 but no new features have been added. SMO was modeled with SQLDMO in mind and Microsoft has called it a "logical continuation" to SQLDMO and explicitly stated that they have incorporated objects similar to SQLDMO objects when possible. SMO makes 150 new classes available to users. SMO is backwards compatible with SQL Server 2000 and 7.0 and is 100% managed code. Like SQLDMO, SMO is very easy to work with, especially if you are using Visual Studio 2005.

Unfortunately, if you want the convenience of intellisense and IDE debugging while writing your SMO you will need to use Visual Studio 2005. The Visual Studio 2003 IDE will not let you add a reference to the SMO managed library through its GUI (you can still use a 'Using' statement and include SMO but you lose intellisense and debugging from within the IDE). You can still build an SMO application with VS 2003 or even notepad but you will need to make your references while using the command line compiler (you will need to use the compiler in your Framework 2.0 folder, not your Framework 1.1 compiler).