SQL Script to find database details
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
Awesome Very useful Article
seo plan for website
jxtbksyfc olysj injmmtw ywpr lesrsdtuuufjsdy
… [Trackback]
[…] Read More here on that Topic: techmixing.com/2018/03/sql-script-to-find-database-their-files.html […]
… [Trackback]
[…] Info on that Topic: techmixing.com/2018/03/sql-script-to-find-database-their-files.html […]
… [Trackback]
[…] Read More Info here to that Topic: techmixing.com/2018/03/sql-script-to-find-database-their-files.html […]
… [Trackback]
[…] Find More on that Topic: techmixing.com/2018/03/sql-script-to-find-database-their-files.html […]
… [Trackback]
[…] Find More on that Topic: techmixing.com/2018/03/sql-script-to-find-database-their-files.html […]
… [Trackback]
[…] Info to that Topic: techmixing.com/2018/03/sql-script-to-find-database-their-files.html […]
… [Trackback]
[…] Find More to that Topic: techmixing.com/2018/03/sql-script-to-find-database-their-files.html […]
… [Trackback]
[…] Read More Info here on that Topic: techmixing.com/2018/03/sql-script-to-find-database-their-files.html […]
… [Trackback]
[…] Find More Info here on that Topic: techmixing.com/2018/03/sql-script-to-find-database-their-files.html […]
… [Trackback]
[…] Here you can find 51131 additional Info to that Topic: techmixing.com/2018/03/sql-script-to-find-database-their-files.html […]
… [Trackback]
[…] Read More Information here on that Topic: techmixing.com/2018/03/sql-script-to-find-database-their-files.html […]
… [Trackback]
[…] Read More to that Topic: techmixing.com/2018/03/sql-script-to-find-database-their-files.html […]
[…] Here you will find 13138 more Info on that Topic: techmixing.com/2018/03/sql-script-to-find-database… […]
… [Trackback]
[…] Information to that Topic: techmixing.com/2018/03/sql-script-to-find-database-their-files.html […]
… [Trackback]
[…] Read More on that Topic: techmixing.com/2018/03/sql-script-to-find-database-their-files.html […]
… [Trackback]
[…] Find More Information here to that Topic: techmixing.com/2018/03/sql-script-to-find-database-their-files.html […]
… [Trackback]
[…] Find More on that Topic: techmixing.com/2018/03/sql-script-to-find-database-their-files.html […]
best casino game on this world Ligaz
… [Trackback]
[…] Find More Info here on that Topic: techmixing.com/2018/03/sql-script-to-find-database-their-files.html […]
… [Trackback]
[…] Read More on to that Topic: techmixing.com/2018/03/sql-script-to-find-database-their-files.html […]
… [Trackback]
[…] Read More here to that Topic: techmixing.com/2018/03/sql-script-to-find-database-their-files.html […]
… [Trackback]
[…] Find More on on that Topic: techmixing.com/2018/03/sql-script-to-find-database-their-files.html […]
… [Trackback]
[…] Find More to that Topic: techmixing.com/2018/03/sql-script-to-find-database-their-files.html […]
… [Trackback]
[…] There you can find 28982 more Info to that Topic: techmixing.com/2018/03/sql-script-to-find-database-their-files.html […]
… [Trackback]
[…] Read More here to that Topic: techmixing.com/2018/03/sql-script-to-find-database-their-files.html […]
… [Trackback]
[…] Find More Info here to that Topic: techmixing.com/2018/03/sql-script-to-find-database-their-files.html […]
… [Trackback]
[…] Read More on that Topic: techmixing.com/2018/03/sql-script-to-find-database-their-files.html […]
… [Trackback]
[…] Information to that Topic: techmixing.com/2018/03/sql-script-to-find-database-their-files.html […]
… [Trackback]
[…] Information to that Topic: techmixing.com/2018/03/sql-script-to-find-database-their-files.html […]