DECLARE @Objectname SYSNAME = 'ttax.property',
@DataSupplierId SMALLINT = 1
DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = 'SELECT
' + CAST(COUNT(*) AS VARCHAR) + ' as ColumnCount,
' + CAST(SUM(CASE WHEN ts.name NOT LIKE '%var%' THEN co.max_length
ELSE 0
END) AS VARCHAR) + ' as FixedLengthPerRow,
' + CAST(SUM(CASE WHEN co.max_length = -1 THEN 0
WHEN ts.name LIKE 'n%var%' THEN co.max_length * 2
WHEN ts.name LIKE '%var%' THEN co.max_length
ELSE 0
END) AS VARCHAR) + ' as MaxVariableLengthPerRow,
SUM(' + Util.dbo.StringConcat(CASE WHEN ts.name LIKE 'n%var%' THEN 'ISNULL(LEN(' + co.name + '), 0) * 2'
WHEN ts.name LIKE '%var%' THEN 'ISNULL(LEN(' + co.name + '), 0)'
ELSE NULL
END, ' + ') + ') / Count(*) as VariableLengthPerRow,
SUM(' + Util.dbo.StringConcat('CASE WHEN ' + co.NAME + ' IS NULL THEN 1 ELSE 0 END', ' + ')
+ ') / Count(*) AS NullPerRow,
Count(*) AS Rows
FROM ' + sc.name + '.' + tb.name + ' (NOLOCK)
WHERE DataSupplierId = ' + CAST(@DataSupplierId AS VARCHAR)
FROM sys.schemas AS sc (NOLOCK)
INNER JOIN sys.objects AS tb (NOLOCK) ON sc.SCHEMA_ID = tb.SCHEMA_ID
INNER JOIN sys.columns AS co (NOLOCK) ON co.OBJECT_ID = tb.OBJECT_ID
INNER JOIN sys.types AS ts (NOLOCK) ON ts.user_type_id = co.user_type_id
INNER JOIN sys.databases AS db (NOLOCK) ON db.database_id = DB_ID()
CROSS APPLY Util.dbo.GetColumnType(ts.name, co.max_length, co.PRECISION, co.scale, co.collation_name,
db.collation_name) ct
WHERE tb.OBJECT_ID = OBJECT_ID(@Objectname)
AND tb.is_ms_shipped = 0
AND tb.type IN ('U', 'V')
GROUP BY sc.name,
tb.name
-- EXEC Util.dbo.PrintLargeText @SQL
EXEC(@SQL) ;
WITH ColumnReport
AS (SELECT tb.OBJECT_ID,
sc.name AS SchemaName,
tb.name AS TableName,
COUNT(*) AS ColumnCount,
SUM(CASE WHEN ts.NAME LIKE '%char%' THEN 1
ELSE 0
END) AS CharCount,
SUM(CASE WHEN ts.NAME LIKE '%varchar%' THEN 1
ELSE 0
END) AS VarCharCount,
SUM(CASE WHEN co.max_length = -1 THEN 0
ELSE co.max_length
END) AS TotalBytes,
SUM(CASE WHEN ts.NAME LIKE '%char%'
AND co.max_length > 0 THEN co.max_length
ELSE 0
END) AS CharBytes,
SUM(CASE WHEN ts.NAME LIKE '%varchar%'
AND co.max_length > 0 THEN co.max_length
ELSE 0
END) AS VarCharBytes,
SUM(CASE WHEN co.max_length = -1 THEN 1
ELSE 0
END) AS VarMaxCount
FROM sys.schemas AS sc (NOLOCK)
INNER JOIN sys.tables AS tb (NOLOCK) ON sc.SCHEMA_ID = tb.SCHEMA_ID
INNER JOIN sys.columns AS co (NOLOCK) ON co.OBJECT_ID = tb.OBJECT_ID
INNER JOIN sys.types AS ts (NOLOCK) ON ts.user_type_id = co.user_type_id
INNER JOIN sys.databases AS db (NOLOCK) ON db.database_id = DB_ID()
WHERE tb.is_ms_shipped = 0
AND tb.OBJECT_ID = OBJECT_ID(@Objectname)
GROUP BY tb.OBJECT_ID,
sc.name,
tb.name),
RawReport
AS (SELECT TOP 9999
s.name AS SchemaName,
o.name AS TableName,
CAST(COUNT(DISTINCT CASE WHEN i.index_id > 0 THEN i.index_id
END) AS VARCHAR) + ': ' + ISNULL(Util.dbo.StringConcat(DISTINCT '('
+ I.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS
+ ') ' + i.NAME, ', '),
'N/A') AS Indexes,
SUM(CASE WHEN i.index_id < 2 THEN ddps.row_count
ELSE 0
END) AS row_count,
SUM(CASE WHEN i.index_id < 2 THEN ddps.used_page_count
ELSE 0
END) * 8 AS usedDataKB,
SUM(CASE WHEN i.index_id < 2 THEN ddps.Reserved_page_count
ELSE 0
END) * 8 AS ReservedDataKB,
SUM(CASE WHEN i.index_id >= 2 THEN ddps.used_page_count
ELSE 0
END) * 8 AS usedIndexKB,
SUM(CASE WHEN i.index_id >= 2 THEN ddps.Reserved_page_count
ELSE 0
END) * 8 AS ReservedIndexKB,
SUM(ddps.used_page_count) * 8 AS usedKB,
SUM(ddps.Reserved_page_count) * 8 AS ReservedKB,
Util.dbo.StringConcat(DISTINCT ps.data_compression_desc COLLATE SQL_Latin1_General_CP1_CI_AS, ', ') AS DataCompression,
COUNT(DISTINCT ps.partition_number) AS DistinctPartitions,
o.create_date,
MAX(cr.ColumnCount) AS ColumnCount,
MAX(cr.CharCount) AS CharCount,
MAX(cr.VarCharCount) AS VarCharCount,
MAX(cr.TotalBytes) AS TotalBytes,
MAX(cr.CharBytes) AS CharBytes,
MAX(cr.VarCharBytes) AS VarCharBytes,
MAX(cr.VarMaxCount) AS VarMaxCount
FROM sys.indexes AS i (NOLOCK)
INNER JOIN sys.objects o (NOLOCK) ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.schemas s (NOLOCK) ON o.SCHEMA_ID = s.SCHEMA_ID
INNER JOIN sys.partitions ps (NOLOCK) ON ps.index_id = i.index_id
AND ps.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats ddps (NOLOCK) ON i.OBJECT_ID = ddps.OBJECT_ID
AND i.index_id = ddps.index_id
AND ps.partition_number = ddps.partition_number
LEFT OUTER JOIN sys.data_spaces ds (NOLOCK) ON ds.data_space_id = I.data_space_id
LEFT OUTER JOIN ColumnReport cr ON cr.OBJECT_ID = o.OBJECT_ID
WHERE o.is_ms_shipped = 0
AND o.OBJECT_ID = OBJECT_ID(@Objectname)
AND ps.partition_number = $PARTITION.[pfDatasupplierId](@DataSupplierId)
GROUP BY s.name,
o.name,
o.create_date
ORDER BY s.name,
o.NAME)
SELECT r.SchemaName,
r.TableName,
r.row_count,
CAST(r.usedKB * 1.0 / 1024 AS NUMERIC(12, 2)) AS usedMB,
CAST(r.ReservedKB * 1.0 / 1024 AS NUMERIC(12, 2)) AS ReservedMB,
Util.dbo.GetPercentage(r.usedKB, r.ReservedKB) AS [Density%],
CAST(r.usedDataKB * 1.0 / 1024 AS NUMERIC(12, 2)) AS usedDataMB,
CAST(r.ReservedDataKB * 1.0 / 1024 AS NUMERIC(12, 2)) AS ReservedDataMB,
Util.dbo.GetPercentage(r.usedDataKB, r.ReservedDataKB) AS [DataDensity%],
CAST(r.usedIndexKB * 1.0 / 1024 AS NUMERIC(12, 2)) AS usedIndexMB,
CAST(r.ReservedIndexKB * 1.0 / 1024 AS NUMERIC(12, 2)) AS ReservedIndexMB,
Util.dbo.GetPercentage(r.usedIndexKB, r.ReservedIndexKB) AS [IndexDensity%],
CASE WHEN r.row_count > 0 THEN r.usedDataKB * 1024 / r.row_count
END AS AvgRowByte,
r.DataCompression,
r.DistinctPartitions,
r.create_date,
r.Indexes,
r.ColumnCount,
r.CharCount,
r.VarCharCount,
r.TotalBytes,
r.CharBytes,
r.VarCharBytes,
r.VarMaxCount
FROM rawreport r