Enumerating and Maintaining Database Objects
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:
- SQL Server Distributed Management Objects (SQL-DMO) is a collection of objects that encapsulate SQL Server database and replication management. You can use SQL-DMO to automate SQL Server tasks , create and administer SQL Server objects, and install and configure replication. You can use SQL-DMO from a .NET application through COM interop. Recipe 10.1 shows how to use SQL-DMO to get a list of SQL Servers on your network.
- 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. Recipe 10.2 shows how to use information schema views to retrieve database schema information. Recipe 10.11 uses information schema views to create relationships in a DataSet at runtime based on the relationships defined in the SQL server.
- System stored procedures can be used to get database schema information. Recipe 10.3 shows how to use the sp_helpconstraint system stored procedure to get default values for columns . Recipe 10.4 shows how to use the sp_help system stored procedure to get the length of non-string columns.
The Connection object in the OLE DB .NET data provider has a GetOleDbSchemaTable( ) method that returns schema information from an OLE DB data source. Recipe 10.2 shows how to use this method to return SQL server database schema information. Recipe 10.12 shows how to list the tables in a Microsoft Access database using this method.
Recipe 10.5 shows how to use the ExecuteScalar( ) method of the Command object to efficiently determine how many records are in a result set matching specified criteria.
Data Definition Language (DDL) statements are used to manage objects in a SQL Server databasefor example, adding or modifying objects such as databases, tables, indices, and views. You can execute a DDL statement through a .NET data provider to manipulate the database or catalog schema. Since DDL commands do not return a result set as a query does, these statements are executed using the ExecuteNonQuery( ) method of the Command object. Recipe 10.7 shows how to execute a DDL statement to create a new SQL Server database. Recipe 10.8 shows how to create a new table in a SQL Server database. Recipe 10.15 creates a method CreateTableFromSchema( ) that dynamically constructs a DLL statement from a DataTable schema and executes that DDL to create a table in a SQL Server database.
ADO Extensions for DDL and Security (ADOX) extend the ADO objects and programming model with objects for schema creation and maintenance and for security. Recipe 10.6 shows how to use ADOX programmatically from .NET though COM interop to create a new Microsoft Access database. Recipe 10.14 shows how to use ADOX to list the tables in a Microsoft Access database.
The Jet OLE DB Provider and Replication Objects (JRO) library was created to isolate Jet-specific functionality from the generic ADO library. Recipe 10.10 shows how to use JRO to compact a Microsoft Access database.
The SQL SET statements alter session handling of current information. Recipe 10.9 shows how to retrieve the execution plan for a query. Recipe 10.12 shows how to retrieve only column metadata when a query is executed.
Every Windows system has ODBC drivers and OLE DB providers installed on it. You can examine the registry to get a list of which are installed. Recipe 10.16 does this for ODBC drivers while Recipe 10.13 does it for OLE DB Providers. Recipe 10.16 also uses a SQL Server extended stored procedure to enumerate the OLE DB providers.