SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

Sometimes we need a SQL Script which can gives 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

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

Vivek's SQL Notes

Vivek johari is currently a Analyst and have more that 5.5 yeras of experience in database. He has Master degree in Computer and also he is Microsoft certified Sql DBA (MCTS)& Microsoft certified SQl BI professional(MCTS). He is also Oracle certified profession(OCP)DBA in ORACLE 10g and ORACLE 9i.He has the experience of working in PL/SQL, T-SQL and SSIS/SSRS. His work basically involved designing and optimization of the Database.He has also published many database articles on his blog Technologies with Vivek Johari.

Comments

Leave a comment on the original post [feedproxy.google.com, opens in a new window]

Loading comments...