Today let us discus more on how SQL Server stores metadata (Data about Data) inside SQL Server:
The metadata is stored in special System Tables. Starting with SQL 2005, System Tables are hidden and they cannot be directly queried. Even with full DBA rights, System Tables are restricted. Although not directly accessible, there are built in views and procedures for extracting metadata.
a. System Views
System Views are predefined Microsoft created views for extracting SQL Server metadata.
Let us discuss these views in a order
1. Information Schema
These System Views belongs to the Information Schema which is an ANSI specification for obtaining metadata. There about twenty different views for displaying most physical aspects of a database, such as table, column, and view information.
To View these System views use the below TSQL Statement:
select name,SCHEMA_NAME(schema_id) as SchemaName
from sys.all_views
where SCHEMA_NAME(schema_id) like '%inform%'
2. Catalog Views
These Views are new in SQL 2005 are called Catalog Views. Catalog views provide access to metadata that is stored in every database on the server. Microsoft recommends them as the most general interface to the catalog metadata. They are efficient and all user available catalog metadata is exposed. All of the Catalog Views are self-describing.
There are different Catalog Views as given below:
• Partition Function Catalog Views
• Server-wide Configuration Catalog Views
• Data Spaces and Fulltext Catalog Views
• Databases and Files Catalog Views
• CLR Assembly Catalog Views
• Schemas Catalog View
• Scalar Types Catalog Views
• Security Catalog Views
• Objects Catalog Views
• Database Mirroring Catalog Views
• Messages (For Errors) Catalog Views
• XML Schemas (XML Type System) Catalog Views
• Service Broker Catalog Views
• Linked Servers Catalog Views
• HTTP Endpoints Catalog Views
• Extended Properties Catalog Views
3. Sys_All
There are four views in a Sys_All group which contain information about the System Views as well as user created objects as given below:
sys.all_columns, sys.all_objects, sys.all_parameters, and sys.all_views.
4. Dynamic Management Views
These views are in Sys Schema and they are used to gather statistics stored in memory.
They are not persistent on disk such as thread information, memory usage, and connection details. These offer administrators a fast and reliable method for obtaining performance numbers.
To find different DMV’s by using below query:
select name,SCHEMA_NAME(schema_id) as SchemaName
from sys.all_views
where name like ‘%dm%’
b. System Stored Procedures
In addition to the System Views as described above, there are many System Stored Procedures. These are also useful for administrative purposes.
As the name indicates, these procedures which belong to Sys Schema and these SP’s are present on each Database. These SP’s which will return results similar to System Views.
Now the next question arises? Difference between INFORMATION_SCHEMA and sys views.
There is some overlap but the INFORMATION SCHEMA views are an ANSI standard so it is not just SQL Server that provides them. The sys.xxx views are more specific to SQL Server and are probably the preferred choice when querying SQL Servers metadata etc.
Now comes the other question as what is Resource Database?
The Resource database is a read-only database that contains all the system objects that are included with SQL Server(object id is 32767). SQL Server system objects, such as sys.objects(system Views), are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata. The physical file names of the Resource database are mssqlsystemresource.mdf and mssqlsystemresource.ldf. These files are located in
If you like to see it yourself, use a dedicated Admin Connection to new query window by using Admin:[Full instance name] after enabling remote admin connections
Use TSQL below to see the database in Admin Connection made above:
SELECT id, name FROM master.sys.sysdbreg
Now going into more depth, what are System Base-Tables and System Base-table Metadata?
System base tables are the underlying tables that actually store the metadata for a specific database. The master database is special in this respect because it contains some additional tables that are not found in any of the other databases. 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.
Example of system Base Table
sys.sysschobjs Exists in every database. Each row represents an object in the database.
Please note: Access to system base tables by using DAC is designed only for Microsoft personnel, and it is not a supported customer scenario.