SQL Script to find the database & their files details on a SQL Server

SQL Script to find database details

Sometimes we need a SQL Script which can give the below information about the databases created on a SQL Server for house keeping purpose: -1) Database Name
2) Database ID
3) Database Files name with their physical location
4) Database Creation Time
5) Users access type for Database
6) Database recovery model
7) Database State (ON/OFF)
8) Database Collation
9) File size on the Disk

Following SQL Query will help you in finding the above mentioned information for all databases on a SQL Server

sd.[Name] AS [database Name]
,sd.database_id [Database Id]
,mf.name as [File Logical Name]
when type_desc = ‘LOG’ then ‘Log File’
when type_desc = ‘ROWS’ then ‘Data File’
Else type_desc
end as [File type]
,mf.physical_name [Physical Location]
,sd.create_date [Database Creation date]
,sd.collation_name [collation name]
,sd.user_access_desc [User Access Type]
,sd.recovery_model_desc [Database Recovery Model]
,sd.state_desc [Database State]
,size_on_disk_bytes [File Size in bytes]
,size_on_disk_bytes/ 1024 as [File Size in KB]
,size_on_disk_bytes/ 1024 / 1024 as [File Size in MB]
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
inner join sys.databases sd on fs.database_id=sd.database_id
JOIN sys.master_files AS mf ON mf.database_id = fs.database_id
AND mf.file_id = fs.file_id
ORDER BY sd.database_id

I hope that above query will be helpful to you. Don’t forget to give your valuable comments on this article. Also you can send me your valuable feedback on my email id askvivekjohari@gmail.com

About vivekjohari

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.
This entry was posted in DBA and tagged , . Bookmark the permalink.

31 Responses to SQL Script to find the database & their files details on a SQL Server

  1. Sheik Ahmed says:

    Awesome Very useful Article

  2. Pingback: seo plan for website

  3. Pingback: משלוחי סושי מבשרת ציון

  4. Pingback: รักษาหลุมสิว

  5. Pingback: Dentist in Victoria Tx

  6. Pingback: ปล่อยเงินด่วน

  7. Pingback: กู้ ซื้อ บ้าน ไม่ ผ่าน

  8. Pingback: stump removal

  9. Pingback: Dentist in Fresno California

  10. Pingback: onion link directory

  11. Pingback: Top Realtor Tallahassee

  12. Pingback: hair transplant montreal

  13. Pingback: 바카라

  14. Pingback: เสือมังกรออนไลน์

  15. Pingback: 바카라사이트

  16. Pingback: ดูดไขมัน

  17. Pingback: relaxing music

  18. Pingback: ติดฟิล์มคอนโด

  19. Pingback: google analytics wordpress

  20. Ligaz says:

    best casino game on this world Ligaz

  21. Pingback: Dentist in Grand Rapids MI

  22. Pingback: math tutoring beachwood

  23. Pingback: a few of our recent reports

  24. Pingback: ligaz24th.com

  25. Pingback: con heo dat remix

  26. Pingback: homes for sale in Twin Cities MN

  27. Pingback: forum

  28. Pingback: пелетни горелки|пелетни горелки Благоевград|пелетна горелка Грейко|самопочистващи пелетни горелки

  29. Pingback: Samsara Market

  30. Pingback: Darknet

  31. Pingback: https://novushub.com/hindi-gana-video/

Leave a Reply