Script to find table sizes in a database

Question

Post reply

Script to find table sizes in a database

harsha.majety

SSCommitted

Points: 1537
More actions
December 13, 2010 at 9:57 pm

#388432

Comments posted to this topic are about the item Script to find table sizes in a database

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Andrew Watson-478275 SSCarpal Tunnel Points: 4652 More actions · Answer 1

If you don't mind using undocumented procedures, you can simplify it slightly by replacing all the loop stuff with:

exec sp_msforeachtable @command1='insert #TempTable exec sp_spaceused ''?'''

I also convert the "KB" values from sp_spaceused into numbers to allow for easier manipulation.

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 2

Andrew Watson-478275 (12/14/2010)
If you don't mind using undocumented procedures, you can simplify it slightly by replacing all the loop stuff with:
exec sp_msforeachtable @command1='insert #TempTable exec sp_spaceused ''?'''
I also convert the "KB" values from sp_spaceused into numbers to allow for easier manipulation.

Though sp_msforeachtable procedure is undocumented, but it is really useful if we want to apply the changes to all the tables in the database - by using this we can avoid Loop or cursors. We are using this to enable/disable all the constraints at one go.

Thanks

Andrew Watson-478275 SSCarpal Tunnel Points: 4652 More actions · Answer 3

You don't really avoid using cursors or loops - if you have a look inside sp_msforeachtable and sp_msforeach_worker, you'll see it uses them itself.

For me, the main reason for using it is that it's easier.

jordonpilling SSCommitted Points: 1690 More actions · Answer 4

Great Script, Just a small tweak if i may;

The 'Order By' clause you use on line 45 does not really work as the data column is a nvarchar and contains " KB".

replace

ORDER BY data DESC;

with

ORDER BY CONVERT(INT, replace(data,' KB','')) desc;

handy for databases that contain 1000+ tables

Knock Knock, Who's There?, sp underscore, sp underscore who?
spid1, spid2, spid3....

william.long Grasshopper Points: 19 More actions · Answer 5

Here are some tweaks I did on my SQL Server 2005 to get it to work for me...

--DECLARE @i INT = 1;

DECLARE @i INT ;

SET @i = 1;

--DECLARE @tableCount INT = (SELECT COUNT(1) FROM #AllTables );

DECLARE @tableCount INT

SET @tableCount = (SELECT COUNT(1) FROM #AllTables );

peter-757102 SSCertifiable Points: 6877 More actions · Answer 6

Don't forget to first use: DBCC UPDATEUSAGE(0);

BOL says about this:

Reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure.

gitmo Old Hand Points: 379 More actions · Answer 7

In SQL 2008 I right click the database name in Object Explorer, select Reports / Standard Report / Disk Usage by Table.

m.alves.amorim Valued Member Points: 57 More actions · Answer 8

To see sizes of all tables in SQL 2005 use this:

DECLARE @tableName VARCHAR(1000);

CREATE TABLE #AllTables

(

row_num INT IDENTITY(1, 1),

table_name VARCHAR(1000)

);

--Using temp table, i dont like to use cursors

INSERT INTO #AllTables

(table_name)

SELECT s.[NAME] + '.' + t.[name]

FROM sys.Tables t, sys.schemas s

WHERE t.[SCHEMA_ID] = s.[SCHEMA_ID]

CREATE TABLE #TempTable

(

tableName VARCHAR(100),

[rows] VARCHAR(100),

reserved VARCHAR(50),

data VARCHAR(50),

index_size VARCHAR(50),

unused VARCHAR(50)

)

DECLARE @i INT;

DECLARE @tableCount INT;

SELECT @i = 1;

SELECT @tableCount = (SELECT COUNT(1) FROM #AllTables );

--Loop to get all tables

WHILE ( @i <= @tableCount )

BEGIN

SELECT @tableName = table_name

FROM #AllTables

WHERE row_num = @i;

--Dump the results of the sp_spaceused query to the temp table

INSERT #TempTable

EXEC sp_spaceused @tableName;

SET @i = @i + 1;

END;

--Select all records so we can use the reults

SELECT *

FROM #TempTable

ORDER BY data DESC;

--Final cleanup!

DROP TABLE #TempTable

DROP TABLE #Alltables;

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 9

gitmo (12/14/2010)
In SQL 2008 I right click the database name in Object Explorer, select Reports / Standard Report / Disk Usage by Table.

Oh great... Thanks. I don't know about this - it is really useful. You can also check no of records for all the tables in a single go.

Thanks

kiran.konankal SSC Veteran Points: 236 More actions · Answer 10

can somebody tell me, how can we list all the columns from a temp(#) table without using SP_Help or system objects.