Also look at
USE master
GO
IF OBJECT_ID('sp_dbinfo') IS NOT NULL
DROP PROCEDURE sp_dbinfo
GO
CREATE PROCEDURE sp_dbinfo @sort CHAR(1) = 'n'
,@include_instance_name CHAR(1) = 'n'
AS
SET NOCOUNT ON
DECLARE @sql NVARCHAR(2000)
,@db_name SYSNAME
,@recovery_model VARCHAR(12)
,@crlf CHAR(2)
SET @crlf = CHAR(13) + CHAR(10)
CREATE TABLE #logspace (
database_name SYSNAME NOT NULL
,log_size REAL NOT NULL
,log_percentage_used REAL NOT NULL
)
CREATE TABLE #dbcc_showfilestats (
database_name SYSNAME NULL
,file_id_ INT NOT NULL
,file_group INT NOT NULL
,total_extents BIGINT NOT NULL
,used_extents BIGINT NOT NULL
,name_ SYSNAME NOT NULL
,file_name_ NVARCHAR(3000) NOT NULL
)
CREATE TABLE #final_output (
database_name SYSNAME
,data_allocated INT
,data_used INT
,log_allocated INT
,log_used INT
,is_sum BIT
)
INSERT INTO #logspace (
database_name
,log_size
,log_percentage_used
)
SELECT instance_name AS 'Database Name'
,MAX(CASE
WHEN counter_name = 'Log File(s) Size (KB)'
THEN cntr_value / 1024.
ELSE 0
END) AS 'Log Size (MB)'
,MAX(CASE
WHEN counter_name = 'Percent Log Used'
THEN cntr_value
ELSE 0
END) AS 'Log Space Used (%)'
FROM master..sysperfinfo
WHERE counter_name IN (
'Log File(s) Size (KB)'
,'Percent Log Used'
)
AND instance_name != '_total'
GROUP BY instance_name
DECLARE db CURSOR
FOR
SELECT NAME
FROM sys.databases
WHERE state_desc = 'ONLINE'
OPEN db
WHILE 1 = 1
BEGIN
FETCH NEXT
FROM db
INTO @db_name
IF @@FETCH_STATUS <> 0
BREAK
SET @sql = 'USE ' + QUOTENAME(@db_name) + ' DBCC SHOWFILESTATS WITH NO_INFOMSGS'
INSERT INTO #dbcc_showfilestats (
file_id_
,file_group
,total_extents
,used_extents
,name_
,file_name_
)
EXEC (@sql)
UPDATE #dbcc_showfilestats
SET database_name = @db_name
WHERE database_name IS NULL
END
CLOSE db
DEALLOCATE db
INSERT INTO #final_output (
database_name
,data_allocated
,data_used
,log_allocated
,log_used
,is_sum
)
SELECT CASE
WHEN d.database_name IS NOT NULL
THEN d.database_name
ELSE '[ALL]'
END AS database_name
,ROUND(SUM(CAST((d.data_alloc * 64.00) / 1024 AS DECIMAL(18, 2))), 0) AS data_allocated
,ROUND(SUM(CAST((d.data_used * 64.00) / 1024 AS DECIMAL(18, 2))), 0) AS data_used
,ROUND(SUM(CAST(log_size AS NUMERIC(18, 2))), 0) AS log_allocated
,ROUND(SUM(CAST(log_percentage_used * 0.01 * log_size AS NUMERIC(18, 2))), 0) AS log_used
,GROUPING(d.database_name) AS is_sum
FROM (
SELECT database_name
,SUM(total_extents) AS data_alloc
,SUM(used_extents) AS data_used
FROM #dbcc_showfilestats
GROUP BY database_name
) AS d
INNER JOIN #logspace AS l ON d.database_name = l.database_name
INNER JOIN sys.databases AS sd ON d.database_name = sd.NAME
GROUP BY d.database_name
WITH ROLLUP
SET @sql = '
SELECT f.database_name, f.data_allocated, f.data_used, f.log_allocated, f.log_used, d.recovery_model_desc' + CASE @include_instance_name
WHEN 'y'
THEN ', @@SERVERNAME AS instance_name'
ELSE ''
END + @crlf + 'FROM #final_output AS f LEFT OUTER JOIN sys.databases AS d ON f.database_name = d.name' + @crlf + CASE
WHEN @sort = 'r'
THEN 'WHERE f.database_name = ''[ALL]'''
ELSE ''
END + @crlf + 'ORDER BY is_sum' + @crlf + CASE
WHEN @sort = 'n'
THEN ', database_name'
WHEN @sort = 'd'
THEN ', data_allocated DESC'
WHEN @sort = 'l'
THEN ', log_allocated DESC'
ELSE ''
END
EXEC (@sql)
GO
EXEC sp_MS_marksystemobject 'sp_dbinfo'
Also look at
