DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to "fingerprint" a database quickly?

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:

How do I get a list of SQL Server tables and their row counts?

http://www.sqlusa.com/bestpractices2005/alltablesrowcount/

http://www.sqlusa.com/bestpractices/dynamicsql/

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE