In this post we will write a
query that will return the file sizes of all the databases present on a SQL
Server.
Code below :
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
OPEN
CUR
FETCH
CUR
INTO
@name
WHILE
@@fetch_status
=
0
BEGIN
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
DBName,
[TotalExtents]/16
AS [Total
Size (in MB)],
[UsedExtents]/16
AS [Used
Size (in MB)],
FileName
FROM
#db_space
ORDER
BY
[DBname]
GO
DROP
TABLE
#db_space
GO
Running this query on SQL
Server will return an output similar to the below screen.