Today, I will be continuing my talk on database files and database objects that can be viewed through the system catalog views or system stored procedures.
A Microsoft SQL Server database is composed of at least two operating system files: a primary data file and a transaction log file. A secondary data files can be used to implement table partitioning or storage of data on different disks by filegroup.
If we want to get a simple list of all the databases on the server, one can execute the sp_helpdb system stored procedure.
|
1 2 3 4 |
<span style="color: #008000; font-size: small;">-- Older system stored proc - all databases sp_helpdb go </span> |
Detailed information can be obtained by calling the stored procedure with the database name that we are interested in.
|
1 2 3 4 |
<span style="color: #008000; font-size: small;">-- Older system stored proc - just one database sp_helpdb @dbname = 'MATH'; go </span> |
If we want to get a simple list of all the files that make up a database, one can execute the sp_helpfile system stored procedure.
|
1 2 3 4 5 6 7 |
<span style="color: #008000; font-size: small;">-- Must select database use [MATH] go -- Older system stored proc - all files for current db sp_helpfile </span> |
Detailed information can be obtained by calling the stored procedure with the file name that we are interested in.
|
1 2 3 4 5 6 7 |
<span style="color: #008000; font-size: small;">-- Must select database use [MATH] go -- Older system stored proc - just one file sp_helpfile @filename = 'FN_MATH_P1' </span> |
There is an old cliche that states ‘There are many ways to skin a cat’. There are system catalog views in newer versions of SQL Server that supply the same or more information than the system stored procedures.
A query of the sys.databases catalog view will show all databases on the server. Please note that this view contains more information than the stored procedure. Information on database attributes can be used to determine what databases do not your standard operating procedures (SOP) for your company.
|
1 2 3 4 5 6 7 8 |
<span style="color: #008000; font-size: small;">-- Must select database use [MATH] go -- What is the properties all databases select * from sys.databases go </span> |
If you query the sys.database_files catalog view, you will see each file that makes up the database.
|
1 2 3 4 5 6 7 8 |
<span style="color: #008000; font-size: small;">-- Must select database use [MATH] go -- Where is the properties of my current database select * from sys.database_files go </span> |
Every database has hidden tables used by service broker messaging, change data capture, file stream, table file, full text indexing, extended indexes, etc. Some of these tables appear when advance features are turned on. The sys.internal_tables catalog view lists these internal tables associated with the current database.
|
1 2 3 4 |
<span style="color: #008000; font-size: small;">-- hidden tables - current database select * from sys.internal_tables go </span> |
Last but not least, there are two catalog views that have detailed information on all files that are part of the SQL database server. The view sys.master_files contains all the visible system databases as well as the user databases. I filter this for the MATH and visible system databases.
|
1 2 3 4 5 6 7 8 |
<span style="color: #008000; font-size: small;">-- Must select database use [MATH] go -- One row in master per every database select * from sys.master_files where database_id <= 7 go </span> |
The sys.sysaltfiles catalog view shows the hidden resource database. It appears as datbase id 32767 with no name.
|
1 2 3 4 5 6 7 8 |
<span style="color: #008000; font-size: small;">-- Must select database use [MATH] go -- This view includes resource db select * from sys.sysaltfiles where dbid <=7 or dbid = 32767 go </span> |
To recap this discussion, system stored procedures can be used grab information about the logical databases and physical files. Use system catalog views instead to get detailed information on both. In the future, I am sure I will be writing TSQL scripts using these commands to do maintenance tasks where third party vendors come up short.
Next time, I will be talking about using system stored procedures and catalog views to look at the objects inside my user defined (sample) database named [MATH].
