Could someone please define what is meant by a schema provider? I'm familiar with DB schema (in this context, I take them to mean a named, logical container for DB objects). Why would a schema provider be useful? From a high level, how do they operate?
The context here is SQL Server 2008.
A schema provider is something external to RDBMS. Schema provider is API or class that gives you ability to retrieve DB schema from your code. SqlConnection.GetSchema can be considered as schema provider for SQL Server. Schema provider is the code that you need if you want to navigate db schema in your app.
http://msdn.microsoft.com/en-us/library/aa833285.aspx
A database schema provider implements all the services that are specific to a particular brand and version of a database (such as SQL Server 2008). This includes the parser that reads and writes the scripts for that database; the Script Domain Object Model (Script DOM) that represents the scripts; and the Schema Model that models the objects, relationships, and properties of the database objects.
I have not used a schema provider, but it seems to be a service that can provide metadata about your database. It's separate from the database itself.
I infer from the discussion that a different schema provider must be implemented for each brand of database (MS SQL Server, Oracle, PostgreSQL, MySQL, etc.). A schema provider can provide generic, database-independent metadata through a common API, and also a schema provider for a given brand can be extended to provide database-specific metadata. Sort of like ODBC for metadata.
Another way of looking at a schema provider is like INFORMATION_SCHEMA, but implemented as a service, with an API.