{"id":2808,"date":"2012-10-17T19:15:28","date_gmt":"2012-10-17T19:15:28","guid":{"rendered":"http:\/\/craftydba.com\/?p=2808"},"modified":"2012-10-24T14:49:55","modified_gmt":"2012-10-24T14:49:55","slug":"database-files-object-part-2","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=2808","title":{"rendered":"Database Files & Objects – Part 2"},"content":{"rendered":"

\"\"<\/a><\/p>\n

Today, I will be continuing my talk on database files and database objects that can be viewed through the system catalog views<\/a> or system stored procedures<\/a>.<\/p>\n

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.<\/p>\n

If we want to get a simple list of all the databases on the server, one can execute the sp_helpdb<\/a> system stored procedure.<\/p>\n

-- Older system stored proc - all databases\r\nsp_helpdb\r\ngo\r\n<\/span><\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
Detailed information can be obtained by calling the stored procedure with the database name that we are interested in.<\/p>\n
-- Older system stored proc - just one database\r\nsp_helpdb @dbname = 'MATH';\r\ngo\r\n<\/span><\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
If we want to get a simple list of all the files that make up a database, one can execute the sp_helpfile<\/a> system stored procedure.<\/p>\n
-- Must select database\r\nuse [MATH]\r\ngo\r\n\r\n-- Older system stored proc - all files for current db\r\nsp_helpfile\r\n<\/span><\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
Detailed information can be obtained by calling the stored procedure with the file name that we are interested in.<\/p>\n
-- Must select database\r\nuse [MATH]\r\ngo\r\n\r\n-- Older system stored proc - just one file\r\nsp_helpfile @filename = 'FN_MATH_P1'\r\n<\/span><\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
There is an old cliche<\/a> 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.<\/p>\n
A query of the sys.databases<\/a> 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.<\/p>\n
-- Must select database\r\nuse [MATH]\r\ngo\r\n\r\n-- What is the properties all databases\r\nselect * from sys.databases\r\ngo\r\n<\/span><\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
If you query the sys.database_files<\/a> catalog view, you will see each file that makes up the database.<\/p>\n
-- Must select database\r\nuse [MATH]\r\ngo\r\n\r\n-- Where is the properties of my current database\r\nselect * from sys.database_files\r\ngo\r\n<\/span><\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
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<\/a> catalog view lists these internal tables associated with the current database.<\/p>\n
-- hidden tables - current database\r\nselect * from sys.internal_tables\r\ngo\r\n<\/span><\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
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<\/a> contains all the visible system databases as well as the user databases.  I filter this for the MATH and visible system databases.  <\/p>\n
-- Must select database\r\nuse [MATH]\r\ngo\r\n\r\n-- One row in master per every database\r\nselect * from sys.master_files where database_id <= 7\r\ngo\r\n<\/span><\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
The sys.sysaltfiles<\/a> catalog view shows the hidden resource database.  It appears as datbase id 32767 with no name.<\/p>\n
-- Must select database\r\nuse [MATH]\r\ngo\r\n\r\n-- This view includes resource db\r\nselect * from sys.sysaltfiles where dbid <=7 or dbid = 32767\r\ngo\r\n<\/span><\/pre>\n<\/p>\n
\"\"<\/a><\/p>\n
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.  <\/p>\n
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].<\/p>\n","protected":false},"excerpt":{"rendered":"
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…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[31,12,15,583,584,28,320,585,586,587,588,568,567,29],"class_list":["post-2808","post","type-post","status-publish","format-standard","hentry","category-db-dev","tag-database-developer","tag-free-code","tag-john-f-miner-iii","tag-sp_helpdb","tag-sp_helpfile","tag-sql-server","tag-sys-databases","tag-sys-database_files","tag-sys-internal_tables","tag-sys-master_files","tag-sys-sysaltfiles","tag-system-catalog-views","tag-system-stored-procedures","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/2808","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2808"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/2808\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2808"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2808"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2808"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}