Execute the following T-SQL example scripts in Microsoft SQL Server Management Studio Query Editor to create and execute a stored procedure for listing all tables with basic row count and size information and summary by schema.
-- Simplest all tables list method
SELECT * FROM AdventureWorks2008.sys.tables
------------
-- SQL Server list all tables in a database - SQL Server database row count
-- SQL Server database table list - SQL Server table size
USE AdventureWorks2008;
GO
-- SQL Server stored procedure create - dynamic SQL stored procedure
CREATE PROC DatabaseFingerprint @DBName SYSNAME
AS
BEGIN
DECLARE @DynamicSQL VARCHAR(1024), @bytes INT
SET NOCOUNT ON
SELECT @bytes = [low] / 1024
FROM MASTER..spt_values
WHERE NUMBER = 1
AND TYPE = 'E'
CREATE TABLE #Fingerprint (
id INT,
SchemaName SYSNAME,
TableName SYSNAME,
NoOfRows MONEY,
DataInMB MONEY,
IndexInMB MONEY)
SET @DynamicSQL = 'INSERT INTO #Fingerprint (id, SchemaName,TableName)
SELECT object_id, schema_name(schema_id), name FROM ' + @DBName + '.sys.objects
WHERE type = ''U'''
EXEC( @DynamicSQL)
SET @DynamicSQL = 'UPDATE #Fingerprint SET NoOfRows = A.rows
FROM ' + @DBName + '..sysindexes A WHERE #Fingerprint.id = A.id
AND A.indid IN (0, 1)'
EXEC( @DynamicSQL)
SET @DynamicSQL = 'UPDATE #Fingerprint SET DataInMB = C.DataInMB
FROM (SELECT A.id, A.SumDpages + ISNULL(B.SumUsed, 0) AS DataInMB
FROM (SELECT id, SUM(dpages) AS SumDpages FROM ' + @DBName + '..sysindexes
WHERE indid IN (0, 1) GROUP BY id) AS A
LEFT JOIN (SELECT id, ISNULL(SUM(used), 0) AS SumUsed
FROM ' + @DBName + '..sysindexes WHERE indid = 255
GROUP BY id) AS B ON A.id = B.id) AS C JOIN #Fingerprint
ON C.id = #Fingerprint.id'
EXEC( @DynamicSQL)
SET @DynamicSQL = 'UPDATE #Fingerprint SET IndexInMB =
A.SumUsed - #Fingerprint.DataInMB FROM (SELECT id, SUM(used)
AS SumUsed FROM ' + @DBName + '..sysindexes WHERE indid IN (0, 1, 255)
GROUP BY id) AS A JOIN #Fingerprint ON A.id = #Fingerprint.id'
EXEC( @DynamicSQL)
UPDATE #Fingerprint
SET DataInMB = DataInMB * @bytes,
IndexInMB = IndexInMB * @bytes
-- Summary of table sizes by schema
SELECT SchemaName,
Rows = SUM(NoOfRows),
DataInMB = SUM(DataInMB / 1024),
IndexInMB = SUM(IndexInMB / 1024)
FROM #Fingerprint
GROUP BY SchemaName
ORDER BY SchemaName
SELECT SchemaName, TableName,
Rows = left(convert(VARCHAR,NoOfRows,1),len(convert(VARCHAR,NoOfRows,1)) - 3),
DataInMB = convert(VARCHAR,DataInMB / 1024,1),
IndexInMB = convert(VARCHAR,IndexInMB / 1024,1)
FROM #Fingerprint
ORDER BY NoOfRows DESC
END
GO
EXEC DatabaseFingerprint AdventureWorks2008
GO
/*
SchemaName Rows DataInMB IndexInMB
dbo 1105637.00 47.398 41.3903
HumanResources 934.00 0.1873 0.2888
Person 141250.00 39.3355 8.3746
Production 349814.00 21.6398 9.2802
Purchasing 13426.00 0.8905 0.4216
Sales 253735.00 20.1714 15.726 */
/*
SchemaName TableName Rows DataInMB IndexInMB
Sales SalesOrderDetail 121,317 11.25 6.05
Production TransactionHistory 113,443 7.22 3.61
Production TransactionHistoryArchive 89,253 5.69 2.90
Production WorkOrder 72,591 4.78 2.09
Production WorkOrderRouting 67,131 6.34 1.16
dbo numbers 65,536 0.83 0.02
..... */
------------
Related articles:
No comments:
Post a Comment