patternsqlMinor
Where are SQL Server catalog views persisted?
Viewed 0 times
aresqlwherepersistedviewscatalogserver
Problem
It seems that
Thanks
sys catalog views can be found under every database, either built in or user created. Are they all actual view definitions created under each of the database or just "links" to somewhere?Thanks
Solution
Catalog views do not provide access to replication, SQL Server Agent, or backup metadata. Those are available in MSDB only.
Reference:
https://learn.microsoft.com/en-us/sql/relational-databases/databases/resource-database
https://technet.microsoft.com/en-us/library/ms179503(v=sql.105).aspx
The Resource database is a read-only database that contains all the
system objects that are included with SQL Server. SQL Server system
objects, such as sys.objects, 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 Resource database makes upgrading to a new version of SQL Server
an easier and faster procedure. In earlier versions of SQL Server,
upgrading required dropping and creating system objects. Because the
Resource database file contains all system objects, an upgrade is now
accomplished simply by copying the single Resource database file to
the local server.
The physical file names of the Resource database are
mssqlsystemresource.mdf and mssqlsystemresource.ldf. These files are
located in :\Program Files\Microsoft SQL
Server\MSSQL.\MSSQL\Binn\ and should not be
moved. Each instance of SQL Server has one and only one associated
mssqlsystemresource.mdf file, and instances do not share this file.
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.
Reference:
https://learn.microsoft.com/en-us/sql/relational-databases/databases/resource-database
https://technet.microsoft.com/en-us/library/ms179503(v=sql.105).aspx
The Resource database is a read-only database that contains all the
system objects that are included with SQL Server. SQL Server system
objects, such as sys.objects, 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 Resource database makes upgrading to a new version of SQL Server
an easier and faster procedure. In earlier versions of SQL Server,
upgrading required dropping and creating system objects. Because the
Resource database file contains all system objects, an upgrade is now
accomplished simply by copying the single Resource database file to
the local server.
The physical file names of the Resource database are
mssqlsystemresource.mdf and mssqlsystemresource.ldf. These files are
located in :\Program Files\Microsoft SQL
Server\MSSQL.\MSSQL\Binn\ and should not be
moved. Each instance of SQL Server has one and only one associated
mssqlsystemresource.mdf file, and instances do not share this file.
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.
Context
StackExchange Database Administrators Q#175184, answer score: 4
Revisions (0)
No revisions yet.