Automating Top 3 Table Row Count Reports in SQL Server
As database administrators, monitoring table sizes and row counts is crucial for performance tuning, space management, and optimizing database resources. One of the ways to accomplish this is by generating a report that lists the top 3 tables with the highest row counts in each database, along with space usage details.
DECLARE @command VARCHAR(5000),@cmd Varchar (1000),@profile_name Varchar(1000)
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
DECLARE @Max_Records TABLE (
[DB_name] [nvarchar](128) NULL,
[SchemaName] [sysname] NOT NULL,
[TableName] [sysname] NOT NULL,
[RowCounts] [bigint] NOT NULL,
[Used_MB] [numeric](36, 2) NULL,
[Unused_MB] [numeric](36, 2) NULL,
[Total_MB] [numeric](36, 2) NULL,
[Capture_Date] Date
)
SELECT @command = 'use [?];
SELECT Top 3
DB_Name(),s.Name AS SchemaName,
t.Name AS TableName,
p.rows AS RowCounts,
CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB,
CAST(ROUND((SUM(a.total_pages) - SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS Unused_MB,
CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB,Getdate() As Capture_Date
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
GROUP BY t.Name, s.Name, p.Rows
ORDER BY CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) desc'
INSERT INTO @Max_Records
EXEC sp_MSForEachDB @command
--Select * from @Max_Records
SET @xml = CAST(( SELECT DB.DB_name As 'td',' ',DB.SchemaName AS 'td' ,' ',DB.TableName as 'td' ,' ',DB.RowCounts as 'td',' ',Db.Used_MB as 'td',
' ',DB.Unused_MB as 'td' ,' ',DB.Total_MB as 'td',' ', convert(varchar(20), DB.Capture_Date,120) as 'td' from @Max_Records DB Order by DB.RowCounts Desc
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><body><H3>Top 3 Table Row count</H3>
<table border = 1>
<th> Database Name </th><th> Schema Name </th> <th> TableName </th> <th> RowCounts</th> <th> Used_MB </th> <th> Unused_MB </th>
<td bgcolor=#F0E68C><b>Total_MB(%)<b> </td> <th>Capture_Date</th></tr>'
--<th> Server Name </th><th> Database Name </th> <th> PhysicalFileName </th> <th> FileSizeMB </th> <th> SpaceUsedMB </th> <th> FreeSpaceMB </th><th> FreeSpace(%) </th> <th>Capture_Date</th></tr>'
SET @body = @body + @xml +'</table></body></html>'
Set @cmd= @@SERVERNAME + ': Top 3 Table Row count '
SELECT Top 1 @profile_name=name FROM msdb.dbo.sysmail_profile
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profile_name, -- replace with your SQL Database Mail Profile
@body = @body,
@body_format ='HTML',
@recipients = 'IT.ESA-DBA@sqldbanow.com', -- replace with your email address
@subject = @cmd
No comments:
Post a Comment