ADO.NET 3.5 Cookbook, 2nd Edition

Chapter 11. Enumerating and Maintaining Database Objects

11.0. Introduction

This chapter describes techniques to get schema information and metadata from databases, manage database objects, and enumerate installed database providers and drivers.

There are many ways to get schema information and other information from a SQL Server database. Some of these techniques are:

• Catalog views were introduced in SQL Server 2005 and return information used by the SQL Server Database Engine. Microsoft recommends using catalog views because they provide the most general interface to catalog metadata and the most efficient way to get and present this information. All user-available catalog metadata is exposed through catalog views. Catalog views don’t expose information about replication, backup, Database Maintenance Plan, or SQL Server Agent.

• SQL Server 2000 introduced information schema views that provide system-table independent access to SQL Server metadata. They provide an alternative to system stored procedures and conform to the SQL-92 Standard and are less tightly bound to the underlying database.

• System stored procedures can be used to get information about a SQL Server database and to perform a variety of administrative tasks.

• SQL Server Management Objects (SMO) was introduced in SQL Server 2005 and lets you programmatically manage and retrieve information about a SQL Server instance. SMO extends and supersedes SQL-DMO.

• SQL Server Distributed Management Objects (SQL-DMO) is a collection of ...