The master database in SQL Server contains all of the information about all of the other databases. You can query thee master database to find out things like all of the other databases on a server, tables on those databases, and schemas. I have used this approach before when building application generation/and ORM (object relational modeling) tools. For instance read the databases and list them. Let the user pick a database. Read that database’s tables and list those. Let the user pick the table and generate some kind of form based on this information.
One challenge you may encounter is that the master table has tables in schemas and depending on the approach you use to read the master tables it may or may not work. For example, if you use SQL Server Management Studio Express 2005 then you can query sys.databases in the master table to determine what databases or running on that server.
select * from sys.databases
The preceding query works in SQL Server Management Studio against the master database, but it does not work with ADO.NET in Visual Studio (at least the first time). When I used ADO.NET, the preceding query, and a SqlDataAdapter to fill a DataTable the code responded with an invalid object name sys.databases. By changing the table name to its SQL Server 2000 version (rewriting the query)
select * from master.dbo.sysdatabases
the code works and the exception no longer occurs. What’s really weird is that subsequently changing the query back to sys.databases no longer caused an exception either. I am still looking into whether this is an application caching issue, a connection caching issue, or because I have SQL Server Management Studio open. These flaky errors can consume a lot of time.
For the time being, if you get an invalid object error on ADO.NET code using a namespace other than dbo then try using the SQL 2000 version of the name, prefix the table, dbo, and a consolidated name for the table. (This looks like what the Data Source Wizard in Visual Studio is doing with databases like AdventureWorks.)
After a little examination it looks like the default database to connect to is not master, so even though master was expressed as the catalog argument in my connection string querying sys.databases without the database name caused the invalid object error. When I logged into SQL Server Management Studio and connected to the master database it seemed to resolve the problem in the code.
If you Google this issue—invalid object, sys.databases—you will see there is a very high noise ratio. Although of the hundreds of posts there is no single definitive answer. If you happen to know how to reproduce this error, why it occurs, then respond to this blog posting.