Monday, January 31, 2011

System Base Tables and Internal Views

System base tables are the underlying tables that actually store the metadata for a specific database.
\These tables contain persisted metadata that has server-wide scope. The Resource database contains the same base table schema as any other non-master database. However, because the Resource database is not visible, users cannot use or connect to it.
To bind to a system base table, a user must connect to the instance of SQL Server by using the dedicated administrator connection (DAC). 

sys.sysschobjs Exists in every database. Each row represents an object in the database.

sys.sysidxstats Exists in every database. Contains a row for each index or statistics for tables and indexed views
Every index (except heap) is associated with a statistic that has the same name as the index.
sys.sysiscols Exists in every database. Contains a row for each persisted index and statistics column.
sys.sysdbreg Exists in the master database only. Contains a row for each registered database.
sys.sysdbfiles Exists in every database. If the column dbid is zero, the row represents a file that belongs to this database. In the master database, the column dbid can be nonzero. When this is the case, the row represents a master file.
sys.sysallocunits Exists in every database. Contains a row for each storage allocation unit.

Some good information about each table is here:

Information schema views do not contain metadata specific to SQL Server 2008.

There is some information about System Internal Views

System internal views are system views that make available low-level storage metadata for the SQL Server Database Engine
View name Description
sys.system_internals_partitions Contains a combination of rowset and hobt information. There is a one-to-one correspondence between rowset and hobt.
sys.system_internals_partition_columns Contains both rowset and hobt column information. It also contains type and other information redundantly copied from the catalog of the relational engine.
sys.system_internals_allocation_units Contains a row for each allocation unit in the current database.

Got the above from MSDN:
Connecting to DAC is not a straightforward approach.
After some googling and some frustrated attempts, I found this.

1) Stop sql server
2) in the sql server configuration tool, go to properties of sql server service-> advance tab-> add the startup options "-f" for speedy recovery and "-m" for single mode.
3) start sql server
4) connect to your instance like "Admin:server" in the management studio and now you can run your queries against all the meta data tables and views.

There is however another way to execute commands using DAC connection.
sqlcmd -Sserver1 -A -E -Qselect * from sys.system_internals_allocation_units

No comments:

Post a Comment