Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Script to find table sizes in a database Expand / Collapse
Author
Message
Posted Monday, December 13, 2010 9:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 16, 2014 2:48 PM
Points: 9, Visits: 435
Comments posted to this topic are about the item Script to find table sizes in a database
Post #1034178
Posted Tuesday, December 14, 2010 1:54 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:11 AM
Points: 1,209, Visits: 2,200
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.
Post #1034242
Posted Tuesday, December 14, 2010 2:21 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:29 AM
Points: 1,038, Visits: 1,306
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
Post #1034249
Posted Tuesday, December 14, 2010 2:27 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:11 AM
Points: 1,209, Visits: 2,200
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.
Post #1034251
Posted Tuesday, December 14, 2010 5:21 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, March 7, 2014 2:41 AM
Points: 50, Visits: 396
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....
Post #1034305
Posted Tuesday, December 14, 2010 7:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 13, 2012 3:24 PM
Points: 1, Visits: 14
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 );
Post #1034408
Posted Tuesday, December 14, 2010 8:28 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:18 AM
Points: 323, Visits: 2,200
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.
Post #1034469
Posted Tuesday, December 14, 2010 8:28 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, June 28, 2014 4:53 PM
Points: 60, Visits: 177
In SQL 2008 I right click the database name in Object Explorer, select Reports / Standard Report / Disk Usage by Table.
Post #1034470
Posted Tuesday, December 14, 2010 6:20 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 14, 2010 8:06 PM
Points: 1, Visits: 2
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;


Post #1034849
Posted Tuesday, December 14, 2010 9:31 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:29 AM
Points: 1,038, Visits: 1,306
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
Post #1034879
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse