Adatbázisok méretének lekérdezése

 1USE msdb;
 2GO
 3 
 4IF EXISTS (SELECT 1 FROM sys.objects WHERE [type] = 'U' AND [object_id] = OBJECT_ID('[dbo].[scsDatabaseSize]'))
 5    DROP TABLE [dbo].[scsDatabaseSize];
 6GO
 7 
 8CREATE TABLE [dbo].[scsDatabaseSize]
 9(
10    [DbName] nvarchar(255),
11    [DataFile] bigint,
12    [LogFile] bigint
13)
14 
15DECLARE @DbName nvarchar(255);
16 
17 
18DECLARE DB CURSOR FOR
19SELECT [name] FROM sys.databases;
20OPEN DB;
21FETCH NEXT FROM DB INTO @DbName;
22WHILE @@FETCH_STATUS = 0
23    BEGIN
24        EXEC ('INSERT INTO [msdb].[dbo].[scsDatabaseSize] ([DbName], [DataFile]) SELECT ''' + @DbName + ''', SUM([size]) FROM ' + @DbName + '.sys.database_files WHERE [type_desc] = ''ROWS'' ');
25         
26        EXEC ('INSERT INTO [msdb].[dbo].[scsDatabaseSize] ([DbName], [LogFile]) SELECT ''' + @DbName + ''', SUM([size]) FROM ' + @DbName + '.sys.database_files WHERE [type_desc] = ''LOG'' ');
27        FETCH NEXT FROM DB INTO @DbName;
28    END
29CLOSE DB;
30DEALLOCATE DB;
31 
32 
33SELECT
34    [DbName] AS [Database Name],
35    SUM([DataFile]) AS [Data size (Kb)],
36    SUM([LogFile]) AS [Log size (Kb)]
37FROM
38    [dbo].[scsDatabaseSize]
39GROUP BY
40    [DbName]
41     
42--cleanup
43DROP TABLE [dbo].[scsDatabaseSize];