DECLARE @Sql NVARCHAR(MAX)
DECLARE @DbName NVARCHAR(128)
-- Курсор для обхода баз данных
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb') AND state_desc = 'ONLINE' -- Исключаем системные базы данных
-- Удаление временных таблиц, если они уже существуют
IF OBJECT_ID('tempdb..#DatabaseFG') IS NOT NULL
BEGIN
DROP TABLE tempdb..#DatabaseFG;
END
IF OBJECT_ID('tempdb..#SysFiles') IS NOT NULL
BEGIN
DROP TABLE tempdb..#SysFiles;
END
-- Создание временных таблиц
CREATE TABLE #DatabaseFG (
DatabaseName NVARCHAR(128),
FG_name NVARCHAR(128),
data_space_id INT,
type NVARCHAR(128),
is_default INT,
is_autogrow_all_files INT
);
CREATE TABLE #SysFiles (
DatabaseName NVARCHAR(128),
FileName NVARCHAR(128),
FileSizeMB DECIMAL(18, 2),
SpaceUsedMB DECIMAL(18, 2),
FreeSpaceMB DECIMAL(18, 2)
);
-- Открытие курсора и обработка баз данных
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DbName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Sql = 'USE [' + @DbName + ']; ' +
'INSERT INTO #DatabaseFG (DatabaseName, FG_name, data_space_id, type, is_default, is_autogrow_all_files) ' +
'SELECT ''' + @DbName + ''' as DatabaseName, name as FG_name, data_space_id, type, is_default, is_autogrow_all_files FROM sys.filegroups; ' +
'INSERT INTO #SysFiles (DatabaseName, FileName, FileSizeMB, SpaceUsedMB, FreeSpaceMB) ' +
'SELECT ''' + @DbName + ''' AS DatabaseName, a.name AS FileName, ' +
'CONVERT(DECIMAL(18, 2), ROUND(a.size / 128.000, 2)) AS FileSizeMB, ' +
'CONVERT(DECIMAL(18, 2), ROUND(FILEPROPERTY(a.name, ''SpaceUsed'') / 128.000, 2)) AS SpaceUsedMB, ' +
'CONVERT(DECIMAL(18, 2), ROUND((a.size - FILEPROPERTY(a.name, ''SpaceUsed'')) / 128.000, 2)) AS FreeSpaceMB ' +
'FROM dbo.sysfiles a;';
EXEC sp_executesql @Sql;
FETCH NEXT FROM db_cursor INTO @DbName
END
CLOSE db_cursor
DEALLOCATE db_cursor
-- Итоговый запрос
SELECT DISTINCT -- Убираем полностью идентичные строки
DBF.DatabaseName,
DBF.FG_name,
DBF.is_default,
DBF.is_autogrow_all_files,
MS.physical_name AS CurrentLocation, -- Добавлено поле CurrentLocation
SF.FileName,
SF.FileSizeMB,
SF.SpaceUsedMB,
SF.FreeSpaceMB,
total_size_gb = CAST(SUM(MS.size) * 8. / 1024 AS DECIMAL(18, 2))
FROM #DatabaseFG AS DBF
LEFT JOIN sys.master_files AS MS WITH (NOWAIT)
ON DBF.DatabaseName = DB_NAME(MS.database_id) AND DBF.data_space_id = MS.data_space_id
LEFT JOIN #SysFiles AS SF
ON DBF.DatabaseName = SF.DatabaseName AND SF.FileName = MS.name -- Уточняем связь по имени файла
WHERE MS.physical_name NOT LIKE 'g:\%' -- Исключение определённых путей
GROUP BY
DBF.DatabaseName, DBF.FG_name, DBF.is_default, DBF.is_autogrow_all_files, MS.physical_name, SF.FileName, SF.FileSizeMB, SF.SpaceUsedMB, SF.FreeSpaceMB
ORDER BY total_size_gb DESC;
-- Удаление временных таблиц
IF OBJECT_ID('tempdb..#DatabaseFG') IS NOT NULL
BEGIN
DROP TABLE tempdb..#DatabaseFG;
END
IF OBJECT_ID('tempdb..#SysFiles') IS NOT NULL
BEGIN
DROP TABLE tempdb..#SysFiles;
END
Similar Posts: