Sdílet prostřednictvím

Listing space used information in each database files

  • Článek

Listing space used information in each database files

-- SQL Server 2000請將 sys.databases改成sysdatabases
-- 第欄是資料使用大小單位是KB

CREATE TABLE #db_space
(
[DBname] NVARCHAR(50),
[Fileid] NVARCHAR(10),
[Filegroup] NVARCHAR(10),
[TotalExtents] int,
[UsedExtents] int,
[Name] NVARCHAR(50),
[FileName] NVARCHAR(300),
);
GO

DECLARE @name sysname
DECLARE cur cursor for SELECT [name] FROM sys.databases WHERE [name] not in ('master','msdb','tempdb','model')
OPEN cur
FETCH cur INTO @name
WHILE @@fetch_status = 0
BEGIN
--Print @name
BEGIN TRAN
INSERT INTO #db_space([Fileid],[Filegroup],[TotalExtents],[UsedExtents],[Name],[FileName])
EXEC('USE '+@name+' ;DBCC SHOWFILESTATS;');
COMMIT TRAN

BEGIN TRAN
UPDATE #db_space SET [DBname] = @name WHERE [DBname] is NULL;
COMMIT TRAN

FETCH cur INTO @name
END
CLOSE cur
DEALLOCATE cur
go
SELECT
[UsedExtents]*64 as [Used KBytes]
,*
FROM #db_space ORDER BY [DBname]
GO
DROP TABLE #db_space
GO