The Automated DBA: Space Usage Snapshotter (sysadmin; central DB)

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
GO

--Create Admin schema if it doesn't exist
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name='Admin') EXECUTE ('CREATE SCHEMA Admin')

IF OBJECT_ID('Admin.Util_BuildSpaceLog', 'P') IS NOT NULL DROP PROCEDURE Admin.Util_BuildSpaceLog
GO

/*
*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
Util_BuildSpaceLog
By Jesse Roberge - YeshuaAgapao@Yahoo.com

The SpaceLog builder
Records database space usage and buffer cache usage statistics into the SpaceLog_* tables.
Keeps a record of database and table growth for reporting of growth trends.
Stats include Reserved, Used, Data, and buffer cache page counts for row, lob (VarChar-Max, text etc), and overflow data (data rows with varchar columns going over 8000 bytes).
Stats also include some computed columns such as BTree (Used-Data), unused (Reserved-Used), and sums of row+lob+overflow for each of reserved, used, data, and buffer.
The database, dataspace, schema, and table levels give stats for the nonclustered index and the table itself (heap or clustered index).
Designed to run and record to a central 'admin' database location.
Requires VIEW_SERVER_STATE due to the querying of sys.dm_os_buffer_descriptors for buffer cache usage stats.  DB-owner does not have this permission.
Sysadmin does have this permission. VIEW_SERVER_STATE can be granted as a separate permission to some or all dbo users.

Required Input Parameters
None

Optional Input Parameters
@GatherDatabaseName sysname='',Name of the database to gather the space usage stats from.  If omitted, the name will be looked up from the ID.  If both ID and name are omitted, the ID/Name of the current database is used.
@GatherDatabaseID int=0,ID of the database to gather the space usage stats from.  If omitted, the ID will be looked up from the name.  If both ID and name are provided, the ID takes precedence.
@RecordDatabaseName sysname='',Name of the database to record the space usage stats to.  If omitted, the name will be looked up from the ID.  If both ID and name are omitted, the ID/Name of the current database is used.
@RecordDatabaseID int=0,ID of the database to record the space usage stats to.  If omitted, the ID will be looked up from the name.  If both ID and name are provided, the ID takes precedence.
@RecordSchemaName sysname='',Name of the schema to record the space usage stats to.  If omitted, the name will be looked up from the ID.  If both ID and name are omitted, the 'dbo' (ID=1) schema is used.
@RecordSchemaID int=0,ID of the schema to record the space usage stats to.  If omitted, the ID will be looked up from the name.  If both ID and name are provided, the ID takes precedence.
@UpdateUsage tinyint=0Default and recommended to be off.  Use only if you must have the most accurate and up to date numbers.  Will run DBCC UpdateUsage to scan every table in the database to re-count all allocations and rows, which can hog disk IO for serveral hours.

Usage
EXECUTE Admin.Util_BuildSpaceLog
@GatherDatabaseName='Baseball',
@RecordDatabaseName='Admin',
@RecordSchemaName='Admin',

EXECUTE Admin.Util_BuildSpaceLog
@GatherDatabaseID=10,
@RecordDatabaseID=7,
@RecordSchemaID=2,
@UpdateUsage=1

DECLARE @SQL nVarChar(4000)
SET @SQL=''
SELECT @SQL=@SQL+'EXECUTE Admin.Util_BuildSpaceLog @GatherDatabaseName=''' + name + ''', @RecordDatabaseName=''Admin'', @RecordSchemaName=''Admin''' + CHAR(13)+CHAR(10)
FROM sys.databases
WHERE database_id<>2
EXECUTE (@SQL)

*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
*/
--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

IF OBJECT_ID('Admin.SpaceLog_Database', 'U') IS NULL
BEGIN
CREATE TABLE Admin.SpaceLog_Database
(
Date DateTime NOT NULL,
DatabaseID int NOT NULL,
DatabaseName sysname NOT NULL,
Rows BigInt NOT NULL,
RowReserved BigInt NOT NULL,
RowUsed BigInt NOT NULL,
RowData BigInt NOT NULL,
RowBTree AS RowUsed-RowData,
RowUnused AS RowReserved-RowUsed,
RowBuffer BigInt NOT NULL,
IndexReserved BigInt NOT NULL,
IndexUsed BigInt NOT NULL,
IndexData BigInt NOT NULL,
IndexBuffer BigInt NOT NULL,
IndexBTree AS IndexUsed-IndexData,
IndexUnused AS IndexReserved-IndexUsed,
LOBReserved BigInt NOT NULL,
LOBUsed BigInt NOT NULL,
LOBUnused AS LOBReserved-LOBUsed,
LOBBuffer BigInt NOT NULL,
OverflowReserved BigInt NOT NULL,
OverflowUsed BigInt NOT NULL,
OverflowUnused AS OverflowReserved-OverflowUsed,
OverflowBuffer BigInt NOT NULL,
TotalReserved AS RowReserved+LOBReserved+OverflowReserved,
TotalUsed AS RowUsed+LOBUsed+OverflowUsed,
TotalBuffer AS RowBuffer+LOBBuffer+OverflowBuffer,
TotalUnused AS (RowReserved+LOBReserved+OverflowReserved+IndexReserved)-(RowUsed+LOBUsed+OverflowUsed+IndexUsed),
CONSTRAINT PK_C_IX__Admin__SpaceLog_DataBase__Date_DatabaseID PRIMARY KEY NONCLUSTERED (Date, DatabaseID) WITH FILLFACTOR=90,
CONSTRAINT U_UX__Admin__SpaceLog_Database__Date_DatabaseName UNIQUE CLUSTERED (Date, DatabaseName) WITH FillFactor=90
)
END

--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

IF OBJECT_ID('Admin.SpaceLog_DataSpace', 'U') IS NULL
BEGIN
CREATE TABLE Admin.SpaceLog_DataSpace
(
Date DateTime NOT NULL,
DatabaseID int NOT NULL,
DatabaseName sysname NOT NULL,
DataSpaceID int NOT NULL,
DataSpaceName sysname NOT NULL,
DataSpaceType Char(2) NOT NULL,
DataSpaceTypeDesc AS CASE DataSpaceType WHEN 'FG' THEN 'Filegroup' WHEN 'PS' THEN 'Partition Scheme' WHEN 'FD' THEN 'FILESTREAM data filegroup' ELSE '' END,
Rows BigInt NOT NULL,
RowReserved BigInt NOT NULL,
RowUsed BigInt NOT NULL,
RowData BigInt NOT NULL,
RowBTree AS RowUsed-RowData,
RowUnused AS RowReserved-RowUsed,
RowBuffer BigInt NOT NULL,
IndexReserved BigInt NOT NULL,
IndexUsed BigInt NOT NULL,
IndexData BigInt NOT NULL,
IndexBTree AS IndexUsed-IndexData,
IndexUnused AS IndexReserved-IndexUsed,
IndexBuffer BigInt NOT NULL,
LOBReserved BigInt NOT NULL,
LOBUsed BigInt NOT NULL,
LOBBuffer BigInt NOT NULL,
LOBUnused AS LOBReserved-LOBUsed,
OverflowReserved BigInt NOT NULL,
OverflowUsed BigInt NOT NULL,
OverflowUnused AS OverflowReserved-OverflowUsed,
OverflowBuffer BigInt NOT NULL,
TotalReserved AS RowReserved+LOBReserved+OverflowReserved+IndexReserved,
TotalUsed AS RowUsed+LOBUsed+OverflowUsed+IndexUsed,
TotalBuffer AS RowBuffer+LOBBuffer+OverflowBuffer+IndexBuffer,
TotalUnused AS (RowReserved+LOBReserved+OverflowReserved+IndexReserved)-(RowUsed+LOBUsed+OverflowUsed+IndexUsed),
CONSTRAINT PK_C_IX__Admin__SpaceLog_DataSpace__Date_DatabaseID_DataSpaceID PRIMARY KEY NONCLUSTERED (Date, DatabaseID, DataSpaceID) WITH FILLFACTOR=90,
CONSTRAINT U_UX__Admin__SpaceLog_DataSpace__Date_DatabaseName_DataSpaceName UNIQUE CLUSTERED (Date, DatabaseName, DataSpaceName) WITH FillFactor=90
)
END

--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

IF OBJECT_ID('Admin.SpaceLog_Schema', 'U') IS NULL
BEGIN
CREATE TABLE Admin.SpaceLog_Schema
(
Date DateTime NOT NULL,
DatabaseID int NOT NULL,
DatabaseName sysname NOT NULL,
SchemaID int NOT NULL,
SchemaName sysname NOT NULL,
Rows BigInt NOT NULL,
RowReserved BigInt NOT NULL,
RowUsed BigInt NOT NULL,
RowData BigInt NOT NULL,
RowBTree AS RowUsed-RowData,
RowUnused AS RowReserved-RowUsed,
RowBuffer BigInt NOT NULL,
IndexReserved BigInt NOT NULL,
IndexUsed BigInt NOT NULL,
IndexData BigInt NOT NULL,
IndexBTree AS IndexUsed-IndexData,
IndexUnused AS IndexReserved-IndexUsed,
IndexBuffer BigInt NOT NULL,
LOBReserved BigInt NOT NULL,
LOBUsed BigInt NOT NULL,
LOBUnused AS LOBReserved-LOBUsed,
LOBBuffer BigInt NOT NULL,
OverflowReserved BigInt NOT NULL,
OverflowUsed BigInt NOT NULL,
OverflowUnused AS OverflowReserved-OverflowUsed,
OverflowBuffer BigInt NOT NULL,
TotalReserved AS RowReserved+LOBReserved+OverflowReserved+IndexReserved,
TotalUsed AS RowUsed+LOBUsed+OverflowUsed+IndexUsed,
TotalBuffer AS RowBuffer+LOBBuffer+OverflowBuffer+IndexBuffer,
TotalUnused AS (RowReserved+LOBReserved+OverflowReserved+IndexReserved)-(RowUsed+LOBUsed+OverflowUsed+IndexUsed),
CONSTRAINT PK_C_IX__Admin__SpaceLog_Scehma__Date_DatabaseID_SchemaID PRIMARY KEY NONCLUSTERED (Date, DatabaseID, SchemaID) WITH FILLFACTOR=90,
CONSTRAINT U_UX__Admin__SpaceLog_Scehma__Date_DatabaseName_SchemaName UNIQUE CLUSTERED (Date, DatabaseName, SchemaName) WITH FillFactor=90
)
END

--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

IF OBJECT_ID('Admin.SpaceLog_Table', 'U') IS NULL
BEGIN
CREATE TABLE Admin.SpaceLog_Table
(
Date DateTime NOT NULL,
DatabaseID int NOT NULL,
DatabaseName sysname NOT NULL,
SchemaID int NOT NULL,
SchemaName sysname NOT NULL,
TableID int NOT NULL,
TableName sysname NOT NULL,
TableDataSpaceID int NOT NULL,
TableDataSpaceName sysname NOT NULL,
TableDataSpaceType Char(2) NOT NULL,
TableDataSpaceTypeDesc AS CASE TableDataSpaceType WHEN 'FG' THEN 'Filegroup' WHEN 'PS' THEN 'Partition Scheme' WHEN 'FD' THEN 'FILESTREAM data filegroup' ELSE '' END,
LOBDataSpaceID int NOT NULL,
LOBDataSpaceName sysname NOT NULL,
LOBDataSpaceType Char(2) NOT NULL,
LOBDataSpaceTypeDesc AS CASE LOBDataSpaceType WHEN 'FG' THEN 'Filegroup' WHEN 'PS' THEN 'Partition Scheme' WHEN 'FD' THEN 'FILESTREAM data filegroup' ELSE '' END,
Rows BigInt NOT NULL,
RowReserved BigInt NOT NULL,
RowUsed BigInt NOT NULL,
RowData BigInt NOT NULL,
RowBTree AS RowUsed-RowData,
RowUnused AS RowReserved-RowUsed,
RowBuffer BigInt NOT NULL,
IndexReserved BigInt NOT NULL,
IndexUsed BigInt NOT NULL,
IndexData BigInt NOT NULL,
IndexBTree AS IndexUsed-IndexData,
IndexUnused AS IndexReserved-IndexUsed,
IndexBuffer BigInt NOT NULL,
LOBReserved BigInt NOT NULL,
LOBUsed BigInt NOT NULL,
LOBUnused AS LOBReserved-LOBUsed,
LOBBuffer BigInt NOT NULL,
OverflowReserved BigInt NOT NULL,
OverflowUsed BigInt NOT NULL,
OverflowUnused AS OverflowReserved-OverflowUsed,
OverflowBuffer BigInt NOT NULL,
TotalReserved AS RowReserved+LOBReserved+OverflowReserved+IndexReserved,
TotalUsed AS RowUsed+LOBUsed+OverflowUsed+IndexUsed,
TotalBuffer AS RowBuffer+LOBBuffer+OverflowBuffer+IndexBuffer,
TotalUnused AS (RowReserved+LOBReserved+OverflowReserved+IndexReserved)-(RowUsed+LOBUsed+OverflowUsed+IndexUsed),
CONSTRAINT PK_C_IX__Admin__SpaceLog_Table__Date_DatabaseID_TableID PRIMARY KEY NONCLUSTERED (Date, DatabaseID, TableID) WITH FILLFACTOR=90,
CONSTRAINT U_UX__Admin__SpaceLog_Table__Date_DatabaseName_TableName_SchemaName UNIQUE CLUSTERED (Date, DatabaseName, TableName, SchemaName) WITH FillFactor=90
)
END

--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

IF OBJECT_ID('Admin.SpaceLog_Index', 'U') IS NULL
BEGIN
CREATE TABLE Admin.SpaceLog_Index
(
Date DateTime NOT NULL,
DatabaseID int NOT NULL,
DatabaseName sysname NOT NULL,
SchemaID int NOT NULL,
SchemaName sysname NOT NULL,
TableID int NOT NULL,
TableName sysname NOT NULL,
IndexID int NOT NULL,
IndexName sysname NOT NULL,
IndexDataSpaceID int NOT NULL,
IndexDataSpaceName sysname NOT NULL,
IndexDataSpaceType Char(2) NOT NULL,
IndexDataSpaceTypeDesc AS CASE IndexDataSpaceType WHEN 'FG' THEN 'Filegroup' WHEN 'PS' THEN 'Partition Scheme' WHEN 'FD' THEN 'FILESTREAM data filegroup' ELSE '' END,
PrimaryKey bit NOT NULL,
UniqueConstraint bit NOT NULL,
UniqueIndex bit NOT NULL,
IgnoreDuplicateKey bit NOT NULL,
IndexFillFactor TinyInt NOT NULL,
Rows BigInt NOT NULL,
RowReserved BigInt NOT NULL,
RowUsed BigInt NOT NULL,
RowData BigInt NOT NULL,
RowBTree AS RowUsed-RowData,
RowUnused AS RowReserved-RowUsed,
RowBuffer BigInt NOT NULL,
LOBReserved BigInt NOT NULL,
LOBUsed BigInt NOT NULL,
LOBUnused AS LOBReserved-LOBUsed,
LOBBuffer BigInt NOT NULL,
OverflowReserved BigInt NOT NULL,
OverflowUsed BigInt NOT NULL,
OverflowUnused AS OverflowReserved-OverflowUsed,
OverflowBuffer BigInt NOT NULL,
TotalReserved AS RowReserved+LOBReserved+OverflowReserved,
TotalUsed AS RowUsed+LOBUsed+OverflowUsed,
TotalBuffer AS RowBuffer+LOBBuffer+OverflowBuffer,
TotalUnused AS (RowReserved+LOBReserved+OverflowReserved)-(RowUsed+LOBUsed+OverflowUsed),
CONSTRAINT PK_C_IX__Admin__SpaceLog_Index__Date_DatabaseID_TableID_IndexID PRIMARY KEY NONCLUSTERED (Date, DatabaseID, TableID, IndexID) WITH FILLFACTOR=90,
CONSTRAINT U_UX__Admin__SpaceLog_Index__Date_DatabaseName_TableName_IndexName_SchemaName UNIQUE CLUSTERED (Date, DatabaseName, TableName, IndexName, SchemaName) WITH FillFactor=90
)
END

--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

IF OBJECT_ID('Admin.SpaceLog_Partition', 'U') IS NULL
BEGIN
CREATE TABLE Admin.SpaceLog_Partition
(
Date DateTime NOT NULL,
DatabaseID int NOT NULL,
DatabaseName sysname NOT NULL,
SchemaID int NOT NULL,
SchemaName sysname NOT NULL,
TableID int NOT NULL,
TableName sysname NOT NULL,
IndexID int NOT NULL,
IndexName sysname NOT NULL,
PartitionID BigInt NOT NULL,
HobtID BigInt NOT NULL,
PartitionNumber int NOT NULL,
Rows BigInt NOT NULL,
RowReserved BigInt NOT NULL,
RowUsed BigInt NOT NULL,
RowData BigInt NOT NULL,
RowBTree AS RowUsed-RowData,
RowUnused AS RowReserved-RowUsed,
RowBuffer BigInt NOT NULL,
RowDataSpaceID int NOT NULL,
RowDataSpaceName sysname NOT NULL,
LOBReserved BigInt NOT NULL,
LOBUsed BigInt NOT NULL,
LOBUnused AS LOBReserved-LOBUsed,
LOBBuffer BigInt NOT NULL,
LOBDataSpaceID int NOT NULL,
LOBDataSpaceName sysname NOT NULL,
OverflowReserved BigInt NOT NULL,
OverflowUsed BigInt NOT NULL,
OverflowUnused AS OverflowReserved-OverflowUsed,
OverflowBuffer BigInt NOT NULL,
OverflowDataSpaceID int NOT NULL,
OverflowDataSpaceName sysname NOT NULL,
TotalReserved AS RowReserved+LOBReserved+OverflowReserved,
TotalUsed AS RowUsed+LOBUsed+OverflowUsed,
TotalBuffer AS RowBuffer+LOBBuffer+OverflowBuffer,
TotalUnused AS (RowReserved+LOBReserved+OverflowReserved)-(RowUsed+LOBUsed+OverflowUsed),
CONSTRAINT PK_C_IX__SpaceLog_SpaceLog_Partition__Date_DatabaseID_TableID_IndexID_PartitionID PRIMARY KEY NONCLUSTERED (Date, DatabaseID, TableID, IndexID, PartitionID) WITH FILLFACTOR=90,
CONSTRAINT U_IX__Admin__SpaceLog_SpaceLog_Partition__Date_DatabaseID_PartitionID UNIQUE NONCLUSTERED (Date, DatabaseID, PartitionID) WITH FILLFACTOR=90,
CONSTRAINT U_IX__Admin__SpaceLog_SpaceLog_Partition__Date_DatabaseName_TableName_IndexName_PartitionID_SchemaName UNIQUE CLUSTERED (Date, DatabaseName, TableName, IndexName, PartitionID, SchemaName) WITH FILLFACTOR=90
)
END
GO

--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

CREATE PROCEDURE Admin.Util_BuildSpaceLog
@GatherDatabaseName sysname='',
@GatherDatabaseID int=0,
@RecordDatabaseName sysname='',
@RecordDatabaseID int=0,
@RecordSchemaName sysname='',
@RecordSchemaID int=0,
@UpdateUsage tinyint=0
AS

DECLARE @Date DateTime
DECLARE @SQL nVarChar(max)
DECLARE @DBName nVarChar(max)
DECLARE @SName nVarChar(max)

--SET @Date=dbo.fn_DateRound_Hour(GetDate())
SELECT @SQL='', @Date=GetDate()

IF @UpdateUsage=1 DBCC UpdateUsage(0) WITH NO_INFOMSGS, COUNT_ROWS

--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

--GatherDatabase
IF @GatherDatabaseName=''
BEGIN
IF @GatherDatabaseID=0
BEGIN
SELECT @GatherDatabaseID=DB_ID(), @GatherDatabaseName=DB_NAME()
END
ELSE
BEGIN
SELECT @GatherDatabaseName=DB_NAME(@GatherDatabaseID)
END
END
ELSE IF @GatherDatabaseID=0
BEGIN
SELECT @GatherDatabaseID=DB_ID(@GatherDatabaseName)
END

--@RecordDatabase
IF @RecordDatabaseName=''
BEGIN
IF @RecordDatabaseID=0
BEGIN
SELECT @RecordDatabaseID=DB_ID(), @RecordDatabaseName=DB_NAME()
END
ELSE
BEGIN
SELECT @RecordDatabaseName=DB_NAME(@RecordDatabaseID)
END
END
ELSE IF @RecordDatabaseID=0
BEGIN
SELECT @RecordDatabaseID=DB_ID(@RecordDatabaseName)
END

--@RecordSchema
IF @RecordSchemaName=''
BEGIN
IF @RecordSchemaID=0
BEGIN
SELECT @RecordSchemaID=1, @RecordSchemaName='dbo'
END
ELSE
BEGIN
SELECT @RecordSchemaName=SCHEMA_NAME(@RecordSchemaID)
END
END
ELSE IF @RecordSchemaID=0
BEGIN
SELECT @RecordSchemaID=SCHEMA_ID(@RecordSchemaName)
END

SELECT @DBName=CONVERT(nVarChar(max), @GatherDatabaseName), @SName=CONVERT(nVarChar(max), @RecordDatabaseName + '.' + @RecordSchemaName)

--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

SET @SQL='
--Partition
INSERT INTO ' + @SName + '.SpaceLog_Partition
(
Date, DatabaseID, DatabaseName,
SchemaID, SchemaName, TableID, TableName,
IndexID, IndexName, PartitionID, HobtID,
PartitionNumber, Rows,
RowReserved, RowUsed,
RowData, RowBuffer,
RowDataSpaceID, RowDataSpaceName,
LOBReserved, LOBUsed,
LOBBuffer,
LOBDataSpaceID, LOBDataSpaceName,
OverflowReserved, OverflowUsed,
OverflowBuffer,
OverflowDataSpaceID, OverflowDataSpaceName
)
SELECT
@Date AS Date, @GatherDatabaseID AS DatabaseID, databases.name AS DatabaseName,
schemas.schema_id, schemas.name AS SchemaName, dm_db_partition_stats.object_id AS TableID, tables.name AS TableName,
dm_db_partition_stats.index_id AS IndexID, ISNULL(indexes.name,''[HEAP]'') AS IndexID, partitions.partition_id AS PartitionID, partitions.hobt_id AS HOBTID,
dm_db_partition_stats.partition_number AS PartitionNumber, dm_db_partition_stats.row_count AS Rows,
CONVERT(bigint, in_row_reserved_page_count)*8 AS RowReserved, CONVERT(bigint, in_row_used_page_count)*8 AS RowUsed,
CONVERT(bigint, in_row_data_page_count)*8 AS RowData, CONVERT(bigint, ISNULL(in_row_buffer_pages,0))*8 AS RowBuffer,
ISNULL(in_row_data_space_id,0) AS RowDataSpaceID, in_row_data_space_name AS RowDataSpaceName,
CONVERT(bigint, lob_reserved_page_count)*8 AS LOBReserved, CONVERT(bigint, lob_used_page_count)*8 AS LOBUsed,
CONVERT(bigint, ISNULL(lob_buffer_pages,0))*8 AS LOBBuffer,
ISNULL(partitions.lob_data_space_id,0) AS LOBDataSpaceID, lob_data_space_name AS LOBDataSpaceName,
CONVERT(bigint, row_overflow_reserved_page_count)*8 AS OverflowReserved, CONVERT(bigint, row_overflow_used_page_count)*8 AS OverflowUsed,
CONVERT(bigint, ISNULL(row_overflow_buffer_pages,0))*8 AS OverflowBuffer,
ISNULL(row_overflow_data_space_id,0) AS OverflowDataSpaceID, row_overflow_data_space_name AS OverflowDataSpaceName
FROM
(
SELECT
partitions.partition_id, MAX(partitions.partition_number) AS partition_number, MAX(partitions.hobt_id) AS hobt_id,
MAX(partitions.object_id) AS object_id, MAX(partitions.index_id) AS index_id,
MAX(CONVERT(bigint, CASE WHEN allocation_units.type=1 THEN ISNULL(dm_os_buffer_descriptors.BufferCount,0) ELSE 0 END)) AS in_row_buffer_pages,
MAX(CONVERT(bigint, CASE WHEN allocation_units.type=2 THEN ISNULL(dm_os_buffer_descriptors.BufferCount,0) ELSE 0 END)) AS lob_buffer_pages,
MAX(CONVERT(bigint, CASE WHEN allocation_units.type=3 THEN ISNULL(dm_os_buffer_descriptors.BufferCount,0) ELSE 0 END)) AS row_overflow_buffer_pages,
MAX(CASE WHEN allocation_units.type=1 THEN ISNULL(data_spaces.data_space_id,0) ELSE 0 END) AS in_row_data_space_id,
MAX(CASE WHEN allocation_units.type=2 THEN ISNULL(data_spaces.data_space_id,0) ELSE 0 END) AS lob_data_space_id,
MAX(CASE WHEN allocation_units.type=3 THEN ISNULL(data_spaces.data_space_id,0) ELSE 0 END) AS row_overflow_data_space_id,
MAX(CASE WHEN allocation_units.type=1 THEN ISNULL(data_spaces.name,'''') ELSE '''' END) AS in_row_data_space_name,
MAX(CASE WHEN allocation_units.type=2 THEN ISNULL(data_spaces.name,'''') ELSE '''' END) AS lob_data_space_name,
MAX(CASE WHEN allocation_units.type=3 THEN ISNULL(data_spaces.name,'''') ELSE '''' END) AS row_overflow_data_space_name
FROM
' + @DBName + '.sys.partitions
JOIN ' + @DBName + '.sys.allocation_units ON
partitions.partition_id=allocation_units.container_id AND allocation_units.type IN (1,3) OR
partitions.hobt_id=allocation_units.container_id AND allocation_units.type=2
JOIN ' + @DBName + '.sys.data_spaces ON allocation_units.data_space_id=data_spaces.data_space_id
LEFT OUTER JOIN (
SELECT allocation_unit_id, COUNT(*) AS BufferCount FROM ' + @DBName + '.sys.dm_os_buffer_descriptors WHERE database_id=@GatherDatabaseID GROUP BY allocation_unit_id
) AS dm_os_buffer_descriptors ON allocation_units.allocation_unit_id=dm_os_buffer_descriptors.allocation_unit_id
GROUP BY partitions.partition_id
) AS partitions
JOIN ' + @DBName + '.sys.dm_db_partition_stats ON partitions.partition_id=dm_db_partition_stats.partition_id
JOIN ' + @DBName + '.sys.indexes ON partitions.object_id=indexes.object_id AND partitions.index_id=indexes.index_id
JOIN ' + @DBName + '.sys.databases ON databases.database_id=@GatherDatabaseID
JOIN ' + @DBName + '.sys.tables ON dm_db_partition_stats.object_id=tables.object_id
JOIN ' + @DBName + '.sys.schemas ON tables.schema_id=schemas.schema_id
WHERE tables.type=''U''

--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

--Index
INSERT INTO ' + @SName + '.SpaceLog_Index
(
Date, DatabaseID, DatabaseName, SchemaID, SchemaName, TableID, TableName,
IndexID, IndexName, IndexDataSpaceID, IndexDataSpaceName, IndexDataSpaceType,
PrimaryKey, UniqueConstraint, UniqueIndex, IgnoreDuplicateKey, IndexFillFactor,
Rows, RowReserved, RowUsed, RowData, RowBuffer, LOBReserved, LOBUsed, LOBBuffer, OverflowReserved, OverflowUsed, OverflowBuffer
)
SELECT
Date, @GatherDatabaseID AS DatabaseID, databases.name AS DatabaseName, tables.schema_id AS SchemaID, schemas.name AS SchemaName, TableID, tables.name AS TableName,
IndexID, ISNULL(indexes.name,''[HEAP]'') AS IndexName, indexes.data_space_id AS IndexDataSpaceID, data_spaces.name AS IndexDataSpaceName, data_spaces.type AS IndexDataSpaceType,
is_primary_key AS PrimaryKey, is_unique_constraint AS UniqueConstraint, is_unique AS UniqueIndex, ignore_dup_key AS IngoreDuplicateKey, fill_factor AS IndexFillFactor,
Rows, RowReserved, RowUsed, RowData, RowBuffer, LOBReserved, LOBUsed, LOBBuffer, OverflowReserved, OverflowUsed, OverflowBuffer
FROM
(
SELECT
@Date AS Date, TableID, IndexID, SUM(Rows) AS Rows,
SUM(RowData) AS RowData, SUM(RowUsed) AS RowUsed, SUM(RowReserved) AS RowReserved, SUM(RowBuffer) AS RowBuffer,
SUM(LOBUsed) AS LOBUsed, SUM(LOBReserved) AS LOBReserved, SUM(LOBBuffer) AS LOBBuffer,
SUM(OverflowUsed) AS OverflowUsed, SUM(OverflowReserved) AS OverflowReserved, SUM(OverflowBuffer) AS OverflowBuffer
FROM ' + @SName + '.SpaceLog_Partition
WHERE SpaceLog_Partition.Date=@Date AND SpaceLog_Partition.DatabaseID=@GatherDatabaseID
GROUP BY TableID, IndexID
) AS SpaceLog_Partition
JOIN ' + @DBName + '.sys.indexes ON SpaceLog_Partition.TableID=indexes.object_id AND SpaceLog_Partition.IndexID=indexes.index_id
LEFT OUTER JOIN ' + @DBName + '.sys.data_spaces ON indexes.data_space_id=data_spaces.data_space_id
JOIN ' + @DBName + '.sys.tables ON SpaceLog_Partition.TableID=tables.object_id
JOIN ' + @DBName + '.sys.schemas ON tables.schema_id=schemas.schema_id
JOIN ' + @DBName + '.sys.databases ON databases.database_id=@GatherDatabaseID
WHERE tables.type=''U''

--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

--Table
INSERT INTO ' + @SName + '.SpaceLog_Table
(
Date, DatabaseID, DatabaseName, SchemaID, SchemaName, TableID, TableName,
TableDataSpaceID, TableDataSpaceName, TableDataSpaceType,
LOBDataSpaceID, LOBDataSpaceName, LOBDataSpaceType,
Rows, RowReserved, RowUsed, RowData, RowBuffer, IndexReserved, IndexUsed, IndexData, IndexBuffer,
LOBReserved, LOBUsed, LOBBuffer, OverflowReserved, OverflowUsed, OverflowBuffer
)
SELECT
Date, @GatherDatabaseID AS DatabaseID, databases.name AS DatabaseName, tables.schema_id AS SchemaID, schemas.name AS SchemaName, TableID, tables.name AS TableName,
TableDataSpaceID, data_spaces.name AS TableDataSpaceName, data_spaces.type AS TableDataSpaceType,
tables.lob_data_space_id AS LOBDataSpaceID, ISNULL(data_spaces_lob.name,'''') AS LOBDataSpaceName, ISNULL(data_spaces_lob.type,'''') AS LOBDataSpaceType,
Rows, RowReserved, RowUsed, RowData, RowBuffer, IndexReserved, IndexUsed, IndexData, IndexBuffer,
LOBReserved, LOBUsed, LOBBuffer, OverflowReserved, OverflowUsed, OverflowBuffer
FROM
(
SELECT
@Date AS Date, TableID, MAX(CASE WHEN IndexID<2 THEN IndexDataSpaceID ELSE 0 END) AS TableDataSpaceID, MAX(Rows) AS Rows,
SUM(CASE WHEN IndexID<2 THEN RowData ELSE 0 END) AS RowData, SUM(CASE WHEN IndexID<2 THEN RowUsed ELSE 0 END) AS RowUsed, SUM(CASE WHEN IndexID<2 THEN RowReserved ELSE 0 END) AS RowReserved, SUM(CASE WHEN IndexID<2 THEN RowBuffer ELSE 0 END) AS RowBuffer,
SUM(CASE WHEN IndexID>1 THEN RowData ELSE 0 END) AS IndexData, SUM(CASE WHEN IndexID>1 THEN RowUsed ELSE 0 END) AS IndexUsed, SUM(CASE WHEN IndexID>1 THEN RowReserved ELSE 0 END) AS IndexReserved, SUM(CASE WHEN IndexID>1 THEN RowBuffer ELSE 0 END) AS IndexBuffer,
SUM(CASE WHEN IndexID<2 THEN LOBUsed ELSE 0 END) AS LOBUsed, SUM(CASE WHEN IndexID<2 THEN LOBReserved ELSE 0 END) AS LOBReserved, SUM(CASE WHEN IndexID<2 THEN LOBBuffer ELSE 0 END) AS LOBBuffer,
SUM(CASE WHEN IndexID<2 THEN OverflowUsed ELSE 0 END) AS OverflowUsed, SUM(CASE WHEN IndexID<2 THEN OverflowReserved ELSE 0 END) AS OverflowReserved, SUM(CASE WHEN IndexID<2 THEN OverflowBuffer ELSE 0 END) AS OverflowBuffer
FROM ' + @SName + '.SpaceLog_Index
WHERE SpaceLog_Index.Date=@Date AND SpaceLog_Index.DatabaseID=@GatherDatabaseID
GROUP BY TableID
) AS SpaceLog_Index
JOIN ' + @DBName + '.sys.tables ON SpaceLog_Index.TableID=tables.object_id
JOIN ' + @DBName + '.sys.schemas ON tables.schema_id=schemas.schema_id
LEFT OUTER JOIN ' + @DBName + '.sys.data_spaces ON SpaceLog_Index.TableDataSpaceID=data_spaces.data_space_id
LEFT OUTER JOIN ' + @DBName + '.sys.data_spaces AS data_spaces_lob ON tables.lob_data_space_id=data_spaces_lob.data_space_id
JOIN ' + @DBName + '.sys.databases ON databases.database_id=@GatherDatabaseID
WHERE tables.type=''U''

--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

--Schema
INSERT INTO ' + @SName + '.SpaceLog_Schema
(
Date, DatabaseID, DatabaseName, SchemaID, SchemaName,
Rows, RowReserved, RowUsed, RowData, RowBuffer, IndexReserved, IndexUsed, IndexData, IndexBuffer,
LOBReserved, LOBUsed, LOBBuffer, OverflowReserved, OverflowUsed, OverflowBuffer
)
SELECT
Date, @GatherDatabaseID AS DatabaseID, databases.name AS DatabaseName, SchemaID, schemas.name AS SchemaName,
Rows, RowReserved, RowUsed, RowData, RowBuffer, IndexReserved, IndexUsed, IndexData, IndexBuffer, LOBReserved,
LOBUsed, LOBBuffer, OverflowReserved, OverflowUsed, OverflowBuffer
FROM
(
SELECT
@Date AS Date, SchemaID, SUM(Rows) AS Rows,
SUM(RowData) AS RowData, SUM(RowUsed) AS RowUsed, SUM(RowReserved) AS RowReserved, SUM(RowBuffer) AS RowBuffer,
SUM(IndexData) AS IndexData, SUM(IndexUsed) AS IndexUsed, SUM(IndexReserved) AS IndexReserved, SUM(IndexBuffer) AS IndexBuffer,
SUM(LOBUsed) AS LOBUsed, SUM(LOBReserved) AS LOBReserved, SUM(LOBBuffer) AS LOBBuffer,
SUM(OverflowUsed) AS OverflowUsed, SUM(OverflowReserved) AS OverflowReserved, SUM(OverflowBuffer) AS OverflowBuffer
FROM ' + @SName + '.SpaceLog_Table
WHERE SpaceLog_Table.Date=@Date AND SpaceLog_Table.DatabaseID=@GatherDatabaseID
GROUP BY SchemaID
) AS SpaceLog_Table
JOIN ' + @DBName + '.sys.schemas ON SpaceLog_Table.SchemaID=schemas.schema_id
JOIN ' + @DBName + '.sys.databases ON databases.database_id=@GatherDatabaseID

--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

--Database
INSERT INTO ' + @SName + '.SpaceLog_Database
(
Date, DatabaseID, DatabaseName,
Rows, RowReserved, RowUsed, RowData, RowBuffer, IndexReserved, IndexUsed, IndexData, IndexBuffer,
LOBReserved, LOBUsed, LOBBuffer, OverflowReserved, OverflowUsed, OverflowBuffer
)
SELECT
Date, @GatherDatabaseID AS DatabaseID, databases.name AS DatabaseName,
Rows, RowReserved, RowUsed, RowData, RowBuffer, IndexReserved, IndexUsed, IndexData, IndexBuffer,
LOBReserved, LOBUsed, LOBBuffer, OverflowReserved, OverflowUsed, OverflowBuffer
FROM
(
SELECT
@Date AS Date, SUM(Rows) AS Rows,
SUM(RowData) AS RowData, SUM(RowUsed) AS RowUsed, SUM(RowReserved) AS RowReserved, SUM(RowBuffer) AS RowBuffer,
SUM(IndexData) AS IndexData, SUM(IndexUsed) AS IndexUsed, SUM(IndexReserved) AS IndexReserved, SUM(IndexBuffer) AS IndexBuffer,
SUM(LOBUsed) AS LOBUsed, SUM(LOBReserved) AS LOBReserved, SUM(LOBBuffer) AS LOBBuffer,
SUM(OverflowUsed) AS OverflowUsed, SUM(OverflowReserved) AS OverflowReserved, SUM(OverflowBuffer) AS OverflowBuffer
FROM ' + @SName + '.SpaceLog_Table
WHERE SpaceLog_Table.Date=@Date AND SpaceLog_Table.DatabaseID=@GatherDatabaseID
) AS SpaceLog_Table
JOIN ' + @DBName + '.sys.databases ON databases.database_id=@GatherDatabaseID

--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=

--Data Space (Filegroup)
INSERT INTO ' + @SName + '.SpaceLog_DataSpace
(
Date, DatabaseID, DatabaseName,
DataSpaceID, DataSpaceName, DataSpaceType,
Rows, RowReserved, RowUsed, RowData, RowBuffer, IndexReserved, IndexUsed, IndexData, IndexBuffer,
LOBReserved, LOBUsed, LOBBuffer, OverflowReserved, OverflowUsed, OverflowBuffer
)
SELECT
@Date AS Date, @GatherDatabaseID AS DatabaseID, databases.name AS DatabaseName,
DataSpaceID, data_spaces.name AS DataSpaceName, data_spaces.type AS DataSpaceType,
Rows, RowReserved, RowUsed, RowData, RowBuffer, IndexReserved, IndexUsed, IndexData, IndexBuffer,
LOBReserved, LOBUsed, LOBBuffer, OverflowReserved, OverflowUsed, OverflowBuffer
FROM
(
SELECT
CASE WHEN Row.DataSpaceID IS NOT NULL THEN Row.DataSpaceID WHEN LOB.DataSpaceID IS NOT NULL THEN LOB.DataSpaceID ELSE Overflow.DataSpaceID END AS DataSpaceID, ISNULL(Rows,0) AS Rows,
ISNULL(RowReserved,0) AS RowReserved, ISNULL(RowUsed,0) AS RowUsed, ISNULL(RowData,0) AS RowData, ISNULL(RowBuffer,0) AS RowBuffer,
ISNULL(IndexReserved,0) AS IndexReserved, ISNULL(IndexUsed,0) AS IndexUsed, ISNULL(IndexData,0) AS IndexData, ISNULL(IndexBuffer,0) AS IndexBuffer,
ISNULL(LOBReserved,0) AS LOBReserved, ISNULL(LOBUsed,0) AS LOBUsed, ISNULL(LOBBuffer,0) AS LOBBuffer,
ISNULL(OverflowReserved,0) AS OverflowReserved, ISNULL(OverflowUsed,0) AS OverflowUsed, ISNULL(OverflowBuffer,0) AS OverflowBuffer
FROM
(
SELECT
RowDataSpaceID AS DataSpaceID, SUM(CASE WHEN IndexID<2 THEN Rows ELSE 0 END) AS Rows,
SUM(CASE WHEN IndexID<2 THEN RowReserved ELSE 0 END) AS RowReserved, SUM(CASE WHEN IndexID<2 THEN RowUsed ELSE 0 END) AS RowUsed,
SUM(CASE WHEN IndexID<2 THEN RowData ELSE 0 END) AS RowData, SUM(CASE WHEN IndexID<2 THEN RowBuffer ELSE 0 END) AS RowBuffer,
SUM(CASE WHEN IndexID>1 THEN RowReserved ELSE 0 END) AS IndexReserved, SUM(CASE WHEN IndexID>1 THEN RowUsed ELSE 0 END) AS IndexUsed,
SUM(CASE WHEN IndexID>1 THEN RowData ELSE 0 END) AS IndexData, SUM(CASE WHEN IndexID>1 THEN RowBuffer ELSE 0 END) AS IndexBuffer
FROM ' + @SName + '.SpaceLog_Partition
WHERE RowDataSpaceID>0 AND Date=@Date AND DatabaseID=@GatherDatabaseID
GROUP BY RowDataSpaceID
) AS Row
FULL OUTER JOIN (
SELECT
LOBDataSpaceID AS DataSpaceID,
SUM(LOBReserved) AS LOBReserved, SUM(LOBUsed) AS LOBUsed, SUM(LOBBuffer) AS LOBBuffer
FROM ' + @SName + '.SpaceLog_Partition
WHERE LOBDataSpaceID>0 AND Date=@Date AND DatabaseID=@GatherDatabaseID
GROUP BY LOBDataSpaceID
) AS LOB ON Row.DataSpaceID=LOB.DataSpaceID
FULL OUTER JOIN (
SELECT
OverflowDataSpaceID AS DataSpaceID,
SUM(OverflowReserved) AS OverflowReserved, SUM(OverflowUsed) AS OverflowUsed, SUM(OverflowBuffer) AS OverflowBuffer
FROM ' + @SName + '.SpaceLog_Partition
WHERE OverflowDataSpaceID>0 AND Date=@Date AND DatabaseID=@GatherDatabaseID
GROUP BY OverflowDataSpaceID
) AS Overflow ON Row.DataSpaceID=Overflow.DataSpaceID
) AS DataSpaces
JOIN ' + @DBName + '.sys.data_spaces ON DataSpaces.DataSpaceID=data_spaces.data_space_id
JOIN ' + @DBName + '.sys.databases ON databases.database_id=@GatherDatabaseID
'

EXECUTE sp_ExecuteSQL @SQL, N'@GatherDatabaseID int, @Date DateTime', @GatherDatabaseID=@GatherDatabaseID, @Date=@Date
GO

--*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=