System Objects


SQL Server maintains a set of tables that store information about all the objects, data types, constraints, configuration options, and resources available to SQL Server. This set of tables is sometimes called the system catalog. In SQL Server 2005, these tables are called the system base tables. Some of the system base tables exist only in the master database and contain systemwide information, and others exist in every database (including master) and contain information about the objects and resources belonging to that particular database. In SQL Server 2005, the system base tables are not always visible by default, in master or any other database. You won't see them when you expand the tables node in the Object Explorer in SQL Server Management Studio, and unless you are a system administrator, you won't see them when you execute the sp_help system procedure. In addition, if you log in as a system administrator and select from the catalog view (discussed shortly) called sys.objects, you can see the names of all the system tables. For example, the following query returns 51 rows of output on my SQL Server 2005 Service Pack 1 instance:

USE master SELECT name FROM sys.objects WHERE type_desc = 'SYSTEM_TABLE'


But even as a system administrator, if you try to select from one of the tables whose names are returned by the preceding query, you will get a 208 error, indicating that the object name is invalid. The only way to see the data in the system tables is to make a connection using the dedicated administrator connection (DAC), which I told you about in Chapter 2. Keep in mind that the system base tables are used for internal purposes only within the SQL Server 2005 Database Engine and are not intended for general customer use. They are subject to change, and compatibility is not guaranteed. In SQL Server 2005, the recommended way of accessing metadata is to use one of three new types of system objects. One type is dynamic management objects, which I introduced in Chapter 2. We looked at some of the dynamic management views (DMVs) and dynamic management functions (DMFs) that provide information about SQL Server memory and schedulers in Chapter 2, and we looked at other dynamic management objects in subsequent chapters. Remember that these dynamic management objects don't really correspond to physical tablesthey contain information gathered from internal structures to allow you to observe the current state of your SQL Server instance. The other two types of system objects are actual views built on top of the system base tables.

Compatibility Views

Although SQL Server 2000 allows you to see data in the system tables, it doesn't strongly encourage you to do this. Nevertheless, many people have made use of system tables for developing their own troubleshooting and reporting tools and techniques, providing result sets that aren't available using the supplied system procedures. You might assume that due to the inaccessibility of the SQL Server 2005 system tables, you would have to use the DAC to make use of your homegrown tools after you upgrade to SQL Server 2005. However, you still might be disappointed. Many of the names and much of the content of the SQL Server 2000 system tables have changed, so any code that uses them might be completely unusable even with the DAC. The DAC is intended only for emergency access, and no support is provided for any other use of the DAC interface. To save you from this grief, SQL Server 2005 offers a set of compatibility views that allow you to continue to access a subset of the SQL Server 2000 system tables. These views are accessible from any database, although they were created in the hidden resource database.

Here are the names of the compatibility views. Please see SQL Server Books Online for complete details.

  • sysconfigures

  • syscharsets

  • syslanguages

  • syscacheobjects

  • sysaltfiles

  • syssegments

  • sysfiles

  • sysfilegroups

  • sysmembers

  • sysusers

  • systypes

  • sysreferences

  • sysprotects

  • syspermissions

  • sysindexkeys

  • sysindexes

  • sysfulltextcatalogs

  • sysconstraints

  • sysforeignkeys

  • sysdepends

  • syscomments

  • syscolumns

  • sysobjects

If you've upgraded from SQL Server 2000, some of these object names might look familiar, such as sysobjects and sysindexes. Others, like syssegments, are still around only for backward compatibility with much older versions.

For compatibility reasons, the views in SQL Server 2005 have the same names as their SQL Server 2000 counterparts, as well as the same column names, which means that your existing code that uses the SQL Server 2000 system tables won't break. However, when you select from these views, you are not guaranteed to get exactly the same results that you get from the corresponding tables in SQL Server 2000. In addition, the compatibility views do not contain any metadata related to new SQL Server 2005 features, such as partitioning. You should consider the compatibility views to be for backward compatibility only, and going forward, you should consider using other metadata mechanisms, such as the catalog view discussed in the next section. All these compatibility views will be removed in a future version of SQL Server.

SQL Server 2005 also provides compatibility views for the SQL Server 2000 pseudo-tables, such as sysprocesses and syscacheobjects. Pseudo-tables are tables that are not based on data stored on disk, but are built as needed from internal structures and can be queried exactly as if they are tables. SQL Server 2005 replaces the pseudo-tables with dynamic management objects, of which there are more than 80 DMVs and DMFs, as I mentioned earlier. Note that there is not always a one-to-one correspondence between the SQL Server 2000 pseudo-tables and the SQL Server 2005 dynamic management objects. For example, for SQL Server 2005 to retrieve all the information available in sysprocesses, you must access three DMVs: sys.dm_exec_connections, sys.dm_exec_sessions, and sys.dm_exec_requests.

Catalog Views

SQL Server 2005 introduces a set of catalog views as a general interface to the persisted system metadata. All the catalog views (as well as the dynamic management objects and compatibility views) are in the sys schema, and you must reference the schema name when you access the objects. Some of the names are easy to remember because they are similar to the SQL Server 2000 system table names. For example, there is a catalog view called objects in the sys schema, so to reference the view we can execute the following:

SELECT * FROM sys.objects


Similarly, there are catalog views called sys.indexes and sys.databases, but the columns displayed for these catalog views are very different from the columns in the compatibility views. Because the output from these types of queries is too wide to reproduce, let me just suggest that you run these two queries yourself and observe the difference.

SELECT * FROM sys.databases SELECT * FROM sysdatabases


Note that you must be in the master database to access the sysdatabases compatibility view directly, just as you have to be in the master database in SQL Server 2000 to access the sysdatabases table directly. The sysdatabases compatibility view is in the sys schema, so you can reference it as sys.sysdatabases. You can also reference it using dbo.sysdatabases. But again, for compatibility reasons, the schema name is not required, as it is for the catalog views. (That is, you cannot simply select from a view called databases; you must use the schema sys as a prefix.)

When you compare the output from the two preceding queries, you might notice that there are a lot more columns in the sys.databases catalog view. Instead of a bitmap status field that needs to be decoded, each possible database property has its own column in sys.databases. With SQL Server 2000, running the system procedure sp_helpdb decodes all these database options, but because sp_helpdb is a procedure, it is difficult to filter the results. As a view, sys.databases can be queried and filtered. For example, if we want to know which databases are in SIMPLE recovery mode, we can run the following:

SELECT name FROM sys.databases WHERE recovery_model_desc = 'SIMPLE'


The catalog views are built on an inheritance model, so sets of attributes common to many objects don't have to be redefined internally. For example, sys.objects contains all the columns for attributes common to all types of objects, and the views sys.tables and sys.views contain all the same columns as sys.objects, as well as some additional columns that are relevant only to the particular type of objects. If you select from sys.objects, you get 12 columns, and if you then select from sys.tables, you get exactly the same 12 columns in the same order, plus 12 additional columns that aren't applicable to all types of objects but are meaningful for tables. In addition, although the base view sys.objects contains a subset of columns compared to the derived views such as sys.tables, it contains a superset of rows compared to a derived view. For example, the sys.objects view shows metadata for procedures and views in addition to that for tables, whereas the sys.tables view shows only rows for tables. So we can summarize the relationship between the base view and the derived views as follows: "The base views contain a subset of columns and a superset of rows, and the derived views contain a superset of columns and a subset of rows."

Just like in SQL Server 2000, some of the metadata appears only in the master database, and it keeps track of systemwide data, such as databases and logins. Other metadata is available in every database, such as objects and permissions. The SQL Server Books Online topic "Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views" categorizes its objects into two liststhose appearing only in master and those appearing in all databases. Note that metadata appearing only in the msdb database is not available through catalog views but is still available in system tables, in the schema dbo. This includes metadata for replication, backup, Database Maintenance Plans, and SQL Server Agent jobs and alerts.

As views, these metadata objects are based on an underlying Transact-SQL definition. The most straightforward way to see the definition of these views is by using the object_definition function. (You can also see the definition of these system views by using sp_helptext or by selecting from the catalog view sys.system_sql_modules.) So to see the definition of sys.objects, we can execute the following:

SELECT object_definition (object_id('sys.tables'))


If you execute the preceding SELECT statement, you'll see that the definition of sys.tables references several completely undocumented system objects. On the other hand, some system object definitions refer only to objects that are documented. For example, the definition of the compatibility view syscacheobjects refers only to three dynamic management objects (two views, sys.dm_exec_cached_plans and sys.dm_exec_plan_attributes, and one function, sys.dm_exec_sql_text) that are fully documented.

Other Metadata

Although the catalog views are the recommended interface for accessing SQL Server 2005 metadata, other tools are available as well.

Information Schema Views

Information schema views were the original system tableindependent view of the SQL Server metadata, introduced in SQL Server 7.0. The information schema views included in SQL Server 2005 comply with the SQL-92 standard definition for the INFORMATION_SCHEMA, and all these views are in a schema called INFORMATION_SCHEMA. Most of the information available through the catalog views is available through the information schema views, and if you need to write a portable application that accesses the metadata, you should consider using these objects. However, the information schema views only show objects compatible with the SQL-92 standard. This means there is no information schema view for certain features, such as indexes, that are not defined in the standard. (Indexes are an implementation detail.) If your code does not need to be strictly portable, or if you need metadata about non-standard features such as indexes, filegroups, the CLR, and SQL Server Service Broker, I suggest using the Microsoft-supplied catalog views. Most of the examples in the documentation, as well in as this and other reference books, are based on the catalog view interface.

System Functions

Most SQL Server system functions are property functions, which were introduced in SQL Server 7.0 and greatly enhanced in SQL Server 2000. SQL Server 2005 has enhanced these functions still further. Property functions give us individual values for many SQL Server objects and also for SQL Server databases and the SQL Server instance itself. The values returned by the property functions are scalar as opposed to tabular, so they can be used as values returned by SELECT statements and as values to populate columns in tables. Here is the list of property functions available in SQL Server 2005:

  • SERVERPROPERTY

  • COLUMNPROPERTY

  • DATABASEPROPERTY (deprecated in favor of the following function)

  • DATABASEPROPERTYEX

  • INDEXPROPERTY

  • INDEXKEY_PROPERTY

  • OBJECTPROPERTY (deprecated)

  • OBJECTPROPERTYEX

  • SQL_VARIANT_PROPERTY

  • FILEPROPERTY

  • FILEGROUPPROPERTY

  • TYPEPROPERTY

The only way to find out what the possible property values are for the various functions is to check Books Online.

Some of the information returned by the property functions can also be seen using the catalog views. For example, the DATABASEPROPERTYEX function has a property called Recovery that returns the recovery model of a database. To view the recovery model of a single database, we can use the property function:

SELECT DATABASEPROPERTYEX('AdventureWorks', 'Recovery').


To view the recovery models of all our databases, we can use the sys.databases view:

SELECT name, recovery_model_desc FROM sys.databases


Note

Columns with names ending in_desc are the so-called "friendly name" columns, and they are always paired with another column that is much more compact, but cryptic. In this case, the sys.databases.recovery_model column is a tinyint with a value of 1, 2, or 3. Both columns are available in the view because different consumers have different needs. For example, internally at Microsoft, the teams building the internal interfaces wanted to bind to more compact columns, whereas DBAs running ad hoc queries might prefer the friendly names.


Tip

If your application doesn't need the friendly name columns, don't include them in your SELECT list. As a result, fewer underlying objects will need to be accessed and your queries will run faster.


In addition to the property functions, the system functions also include functions that are merely shortcuts for catalog view access. For example, to find out the database ID for the AdventureWorks database, we can either query the sys.databases catalog view or use the DB_ID() function. Both of the following SELECT statements should return the same result:

SELECT database_id FROM sys.databases WHERE name = 'AdventureWorks' SELECT DB_ID('AdventureWorks')


System Stored Procedures

System stored procedures are the original metadata access tool, in addition to the system tables themselves. Most of the system stored procedures introduced in the very first version of SQL Server are still available. However, catalog views are a big improvement: you have control over how much of the metadata you see because you can query the views as if they were tables. With the system stored procedures, you basically have to accept the data it returns. Some of the procedures allow parameters, but they are very limited. So for the sp_helpdb procedure, we can pass a parameter to see just one database's information or not pass a parameter and see information for all databases. However, if we want to see only databases that the login sue owns, or just see databases that are in a lower compatibility level, we cannot do it using the supplied stored procedure. Using the catalog views, these queries are straightforward:

SELECT name FROM sys.databases WHERE suser_sname(owner_sid) ='sue' SELECT name FROM sys.databases WHERE compatibility_level < 90


Metadata Wrap-Up

Figure 6-1 shows the multiple layers of metadata available in SQL Server 2005, with the lowest layer being the actual system tables. Any interface that accesses the information contained in the system tables is subject to the metadata security policies. For SQL Server 2005, that means nobody can see any metadata that they don't need to see or to which they haven't specifically been granted permissions. The "other metadata" refers to system information not contained in system tables, such as the internal information provided by the dynamic management objects. Remember that the preferred interface to the system metadata is the catalog views and system functions. Although not all the compatibility views, INFORMATION_SCHEMA views, and system procedures are actually defined in terms of the catalog views, conceptually it is useful to think of them as another layer on top of the catalog view interface.

Figure 6-1. Layers of metadata in SQL Server 2005


Keep in mind that the reason for providing metadata is really to give you information about the objects you have created. So for the rest of this chapter, we'll look at the most important object you can create in a database: the table.



Inside MicrosoftR SQL ServerT 2005. The Storage Engine
Inside Microsoft SQL Server 2005: The Storage Engine (Solid Quality Learning)
ISBN: 0735621055
EAN: 2147483647
Year: 2004
Pages: 115
Authors: Kalen Delaney

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net