Friday, April 20, 2012

A stored proc everyone needs - show space all tables

Everyone should have this stored proc (or something similar) in their database. Microsoft really should have built this in to their product. It shows all the tables and their sizes (including LOB data stored off page), and sorts the tables by size.

Aside from the obvious maintenance applications, this is the first thing I do when I work on an unfamiliar database. The largest tables are not always the most important (our 2nd largest table is a log file used only for troubleshooting), but that is the best place to start.



SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





CREATE PROC [dbo].[pDBShowSpaceAllTables]
AS
DBCC UPDATEUSAGE(0)

DECLARE @t TABLE
(
id INT,
SchemaName VARCHAR(64),
TableName VARCHAR(64),
NRows INT,
Total FLOAT,
Reserved FLOAT,
TableSize FLOAT,
IndexSize FLOAT,
FreeSpace FLOAT,
LobUsed FLOAT,
LobTotal FLOAT
)

INSERT @t EXEC sp_msForEachTable 'SELECT
OBJECT_ID(''?''),
PARSENAME(''?'', 2),
PARSENAME(''?'', 1),
COUNT(*),0,0,0,0,0,0,0 FROM ?'

DECLARE @low INT

SELECT @low = [low] FROM master.dbo.spt_values
WHERE number = 1
AND type = 'E'

UPDATE @t
SET Reserved = x.r,
IndexSize = x.i
FROM @t T INNER JOIN (
SELECT id, r = SUM(si.reserved), i = SUM(si.used)
FROM sysindexes si
WHERE si.indid IN (0, 1, 255)
GROUP BY id
) x ON x.id = T.id

UPDATE @t SET TableSize = (
SELECT SUM(si.dpages)
FROM sysindexes si
WHERE si.indid < 2
AND si.id = T.id
)
FROM @T T

UPDATE @t
SET TableSize = TableSize + (
SELECT COALESCE(SUM(used), 0)
FROM sysindexes si
WHERE si.indid = 255
AND si.id = T.id
)
FROM @t T

UPDATE @t SET FreeSpace = Reserved - IndexSize

UPDATE @t SET IndexSize = IndexSize - TableSize

UPDATE @t
SET LobUsed = u.used_pages,
LobTotal = u.total_pages
FROM @t T
INNER JOIN sys.partitions p ON T.id = p.object_id
INNER JOIN sys.allocation_units u ON u.container_id = p.partition_id
WHERE u.type = 2

UPDATE @t SET Total = Reserved + LobTotal

SELECT
schemaname,
tablename,
nrows,
Total = LTRIM(STR(
total * @low / 1024.,15,0) +
' ' + 'KB'),
Reserved = LTRIM(STR(
reserved * @low / 1024.,15,0) +
' ' + 'KB'),
DataSize = LTRIM(STR(
tablesize * @low / 1024.,15,0) +
' ' + 'KB'),
IndexSize = LTRIM(STR(
indexSize * @low / 1024.,15,0) +
' ' + 'KB'),
LobUsedSpace = LTRIM(STR(
LobUsed * @low / 1024.,15,0) +
' ' + 'KB'),
LobTotalSpace = LTRIM(STR(
LobTotal * @low / 1024.,15,0) +
' ' + 'KB'),
FreeSpace = LTRIM(STR(
freeSpace * @low / 1024.,15,0) +
' ' + 'KB'),
Total AS Sort
FROM @t
ORDER BY Sort DESC

SELECT nrows = SUM(nrows),
Total = LTRIM(STR(
SUM(total) * @low / 1024.,15,0) +
' ' + 'KB'),
Reserved = LTRIM(STR(
SUM(reserved) * @low / 1024.,15,0) +
' ' + 'KB'),
DataSize = LTRIM(STR(
SUM(tablesize) * @low / 1024.,15,0) +
' ' + 'KB'),
IndexSize = LTRIM(STR(
SUM(indexSize) * @low / 1024.,15,0) +
' ' + 'KB'),
FreeSpace = LTRIM(STR(
SUM(freeSpace) * @low / 1024.,15,0) +
' ' + 'KB')
FROM @t

GO

4 Comments:

Anonymous Anonymous said...

http://www.bulkping.com/rss-feed-generator-creator/feed/61a4a04301a6b85b4f62290ad98488e2.xml trendysrliyiw
Ozark trendystbfedn

[url=http://www.jacketopsale.com/]Arcteryx jackets china[/url] trendysjrdaot
http://www.jacketopsale.com/ trendysbkwdyv

3:02 PM  
Anonymous Anonymous said...

Nike Arian Foster Jersey

Manufacture B group vitamins, biotin and folic acid Try it, and you could find hidden gems that will increase your business profitIf you are waiting you are not living in the momentThen he stopped

JJ Watt Women's Jersey

7 Practicing into a tape recorder is the next best thing because it allows you to hear the voice others will hearRemember, it is only temporary!Even the U All free of charge

Torrey Smith Authenitc Jersey

3:11 PM  
Anonymous Anonymous said...

Did you [url=http://www.onlinecasinos.gd]video poker[/url] identify that you can on Joyride Majestic squander leisure at without delay from your mobile? We delight in a series transportable casino elbow in the past master care of iPhone, iPad, Android, Blackberry, Windows 7 and Smartphone users. Elevate your gaming with you and be a disposition [url=http://www.avi.vg]online adult toys[/url] wherever you go.

3:53 PM  
Anonymous Anonymous said...

top [url=http://www.c-online-casino.co.uk/]uk casinos online[/url] coincide the latest [url=http://www.casinolasvegass.com/]casino las vegas[/url] unshackled no store bonus at the best [url=http://www.baywatchcasino.com/]online casinos
[/url].

4:48 PM  

Post a Comment

<< Home