SQL Server 2005: Say goodbye to the system table

Microsoft SQL Server database administrator, think fast! Without using any documents, write a query that extracts the list of indexes from the SQL Server 2000 system tables, then enumerates the fields in each index, and determines whether the field is sorted in ascending or descending order. You have two minutes. Fast!

If you really stop reading now and start thinking about this thankless task with an essential two minutes, then you're in a big trouble, which involves system indexes, System index keywords, system fields, and some metadata functions, including similar object_name and indexkey_property such as standby information. So far, it's obviously going to take far more than 2 minutes to write such a query.

Unfortunately, SQL Server 2000 database administrators need to browse esoteric system tables, which is the worst part of the work of using a database management system. system tables, which are usually efficient, but in my mind, they are never designed for user friendliness.

Fortunately, there is a savior in front of you. In SQL Server 2005, the system table is missing. Yes. It's gone. There is no need to operate on unfamiliar bits, nor do we need to find a cryptic coding scheme--which was necessary in the past. For those of you who need to take a look at the remaining references, I know what you're thinking: an endless mechanical upgrade to ensure compatibility with SQL Server 2005. But don't be disgusted with it. There are still objects similar to system tables in the system for backward-compatible purposes. But the table itself-or indeed should be-is forgotten, like 8-tracks and tab, into the dustbin of history.

So where did these watches go? system data in SQL Server 2005 is now stored in the Hidden resources table, which can only be accessed directly by the server itself. Lower-level users (and database administrators) must use a new set of categorized views that show the data obtained from hidden tables and various hidden functions that we cannot see or call. Systems in previous versions of SQL Server were implemented in the form of a series of so-called (and reasonably correct) "compatible views".

Category views and their partners, dynamic management views (explained below) represent a way to process metadata that is completely redesigned and rethinking. Without the mysterious tables that only give the database administrator a little sense of the underlying data, SQL Server now provides a wealth of resources: there are more than 200 categories and management views in SQL Server 2005, replacing approximately 50 system tables in previous versions.

All of these views can be found in the system plan. (The plan is a significantly expanded security feature in SQL Server 2005.) But this is another tip of the story. To see a full list of available views, SQL Server Management Studio extends the system view tree for all databases. Or select a list from the view itself through T-SQL and find friendly, Easy-to-understand names:

SELECT Name

From Sys.all_views

WHERE is_ms_shipped = 1

You'll also find no need to browse through the documentation to find clues about what's going on with the data on a relational system. These views have a clear explanation of self.

Some clues about the name of the view are as follows: The prefixes are dm_ dynamic management view, representing the changing state of the server through information similar to the current session, lock, and system resources. Other views can be considered categorized views. Those prefixes are all_ that contain information about system objects, such as views, and user-defined objects. Information that contains only user-defined objects that do not have the All_ prefix. In those views that include system objects, the Is_ms_shipping field can be used to differentiate between user-defined objects and system objects. If the value of the is_ms_shipped field is 1, the row represents a system object, otherwise the user-defined object.