Web Analytics Made Easy - Statcounter

Technology with Vivek Johari

DBA

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

Byvivekjohari

Mar 30, 2018 #DBA Scripts, #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

SELECT
sd.[Name] AS [database Name]
,sd.database_id [Database Id]
,mf.name as [File Logical Name]
,case
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

Advantages of SQL Stored Procedure
Trending
Advantages of SQL Stored Procedure

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

By vivekjohari

I am currently working as a Senior Database Professional and have around 18 years of experience in database. Degree:- Master Degree in Computer(MCA) Certification course in Data Science & Machine Learning from Indian Institute of Technology (IIT), Delhi Work experience:- Designing of the database. Database Optimization. Writing Complex Stored Procedures,Functions,Triggers etc. Designing and developing SSIS & DTS packages. Designing SQL Reports using SSRS. Database Server Maintenance. Certification:- MCTS: DA-100: Analysing Data with Microsoft Power BI MCTS: DP-300: Administering Relational Databases on Microsoft Azure Microsoft certified Sql DBA in Sql server 2008 (MCTS). Microsoft certified BI professional in Sql server 2008 (MCTS). Oracle certified profession DBA in ORACLE 10g (OCP) certified profession DBA in ORACLE 9i (OCP) My other publication Technical Blog:- Technologies with Vivek Johari Guest Author and Blogger at sqlservercentral.com

Related Post

DBA SQL Database Restoration

SQL Script: – How to find the database restoring history of SQL Server databases

Mar 30, 2018 vivekjohari
DBA

SQL Script: – How to find the last access date of a database

Mar 29, 2018 vivekjohari
DBA SQL Tips

Learn How to Recover Deleted Records from SQL Server 2014

Aug 8, 2017 Andrew Jackson
31 thought on “SQL Script to find the database & their files details on a SQL Server”

Leave a Reply

Your email address will not be published. Required fields are marked *

You missed

Career Coaching

How to Update Your Resume After a Career Break: A Strategic Approach

February 28, 2025 Megha No Comments
Career Coaching

The Importance of Soft Skills in the Modern Workplace

February 28, 2025 Megha No Comments
Career Coaching

Unlocking the Power of Resume Keywords: Your Guide to Standing Out

February 28, 2025 Megha No Comments
Career Coaching

Top 10 High-Demand Careers in India for 2025: Secure Your Future Today

February 28, 2025 Megha No Comments