The ANSI SQL standard doesn't support individual users as owners in a database; it uses a schema, which is a collection of objects (tables and views) that one user owns and the permissions on those objects. These schemas have names, which ANSI SQL specifies are to be used as the middle part of a three-part name. SQL Server uses the owner name in that middle position, so in SQL Server you can fully qualify an object name by using database_name.owner_name.object_name. If the object is in your current database, you can omit the database name. If the owner is you or DBO, you can omit the owner. But in this case, the owner is INFORMATION_SCHEMA, which appears as a row in the sysusers table. So, to get the information about tables from the TABLES information schema view, you need to use
SELECT * FROM INFORMATION_SCHEMA.TABLES
Now you'll get results. But say I want just the names of the user tables. I notice that the object names return in the column called table_name, so I run this query:
SELECT table_name FROM INFORMATION_SCHEMA.TABLES
Now I get not only the tables but also all the views, including the information schema views, which I don't want to see. I can see another column called table_type, which seems to have a value of either VIEW or BASE TABLE. So I rewrite my query once more:
SELECT table_name FROM INFORMATION_SCHEMA.TABLES
WHERE table_type = 'BASE TABLE'
This query gives me the information I want. But I could easily get the same result by accessing sysobjects directly:
SELECT name FROM sysobjects
WHERE type = 'U'
Microsoft strongly recommends that you not access the system tables directly. But if I want to find the names of all my user tables as quickly and easily as possible, which of the two preceding queries will I probably type into Query Analyzer? (Hint: It's the one with the fewest keystrokes.)
You can combine property functions and information schema views to get information that's difficult to obtain in SQL Server 6.x: a list of all the databases that have the SELECT INTO/BULKCOPY option turned on. To get this information in SQL Server 6.x and earlier, not only must you access the sysdatabases table directly, you also need to do some bitwise arithmetic to check whether a particular bit in the sysdatabases status field is set to 1.
Determining which bit to check is another tricky problem. In SQL Server 7.0, you can execute the following query:
SELECT catalog_name FROM information_schema.schemata
WHERE DatabaseProperty(catalog_name, 'IsBulkCopy') = 1
In most cases, you can get the same information by executing sp_helpdb for a list of all the databases on your server and examining the string in the status column to find out whether it lists SELECT INTO/BULKCOPY. However, this approach isn't as neat as the specific query, and it gives you more information than you might need. Also, if you have any databases that are inaccessible because of corruption or because they're set to single-user mode, sp_helpdb won't return any information for those databases. But you can use the DatabaseProperty function even if the database is inaccessible.
It's Your Choice
I've demonstrated four methods for extracting meta data from the system tables: system stored procedures, system functions, property functions, and information schema views. Microsoft guarantees that it will continue to support and document these methods fully, but this promise doesn't hold true for the system tables themselves. Any code you write that uses these tables might stop working with any upgrade or even a service-pack installation. I recommend not accessing system tables directly in stored application code if you can get what you need by using the supplied, supported alternative mechanisms. Next month, I'll show some examples of code for obtaining information that you can't get without directly accessing the system tables.