info@techdevops.com
TechDevOps.com
Explore Tools
Experts in Microsoft SQL Server Operations, Performance, Replication, Cloud & Migrations



T-SQL Compare sys.database_files with sys.master_files
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2018-09-15








T-SQL:


Declare @DBFilesCompare Table
(
master_files_dbid NVARCHAR(255), database_files_dbid NVARCHAR(255), master_files_name NVARCHAR(255), database_files_name NVARCHAR(255),
master_files_physical_name NVARCHAR(255), database_files_physical_name NVARCHAR(255)
)
INSERT INTO @DBFilesCompare
EXEC sp_MSforeachdb
@command1= 'use ?; Select m.database_id, db_id(), m.name as ''master_files_name'', d.name as ''database_files_name'',
m.physical_name as ''master_files_physical_name'', d.physical_name as ''database_files_physical_name''
from
sys.master_files m inner join sys.database_files d
on m.database_id = db_id() and m.file_id = d.file_id
where m.name <> d.name Collate Latin1_General_CI_AI'
SELECT
DB_Name(database_files_dbid) as 'database_files_db',
DB_name(master_files_dbid) as 'master_files_db',
database_files_name,
master_files_name,
database_files_physical_name
master_files_physical_name
FROM @DBFilesCompare
order by database_files_db asc