In SQL 2000, you used to be able to view table details for a SQL database and see the size of each table based on data size used and index sized used. This information was available right from the SQL enterprise manager. The only problem with that was the tables were sorted by table name, not size so it was a little time consuming to find what the biggest tables were.
SQL 2005 dropped this view from the SQL Management Studio, so there is no graphical way of seeing the table size usage.
There is however a way of getting this information and it is fairly straight forward.
First off there is a SQL stored procedure called sp_SpaceUsed. This procedure allows you to see the space usage for a single table. See this posting. This procedure only allows you to query one table at a time. We can use another stored procedure however in conjunction with the first. There is an undocumented stored procedure called sp_MSForEachTable. This procedure acts like a cursor allowing you to loop through all the tables in a database and perform some kind of action on them. IN this case, it allows us to run the sp_SpaceUsed procedure on every table. This undocumented procedure simply takes the command to be run against every table and then you substitute the specific table name with a “?”, like so:
EXECUTE sp_MSforeachtable 'EXECUTE sp_spaceused [?]'
Now using these procedures in conjunction with each other produces several results sets, one result set for each table. This could be improved by taking the various results and populating them into a temporary table. Then you can examine the contents of this table to get the results of all table information returned in one result set.
Here is a query that does just that. The query can be modified by editing the @Sort variable so that the results are returned in various sorting directions.
SET NOCOUNT ON
/*
Sorting options
Set the @Sort value to one of the following
0 = Alphabetically by the name of the table
1 = By row count
2 = By table size
3 = By unused space
4 = By index size
5 = By data size
*/DECLARE @Sort int
–*****************************************************************************
SELECT @Sort = 2 –Change this value to control sort option. See notes above
–*****************************************************************************–Creates temp Table
CREATE TABLE #SizeTable
( Table_Name varchar(50),
Row_Count int,
Table_Size varchar(50),
Data_Space_Used varchar(50),
Index_Space_Used varchar(50),
Unused_Space varchar(50)
)
–Create Stored Procedure String
–Populate Temporary Table
INSERT INTO #SizeTable EXEC(‘sp_msforeachtable ”sp_spaceused “?””’)CREATE TABLE #ResultTable
( Table_Name varchar(50),
Row_Count int,
Table_Size int,
Data_Space_Used int,
Index_Space_Used int,
Unused_Space int
)
insert into #ResultTable select Table_Name, replace(row_count,’kb’,”),
replace(Table_Size,’kb’,”),replace(Data_Space_Used,’kb’,”),replace(Index_Space_Used,’kb’,”),
replace(Unused_Space,’kb’,”) from #SizeTable–Determine sorting method
If @Sort =0 SELECT * FROM #ResultTable ORDER BY Table_Name
If @Sort =1 SELECT * FROM #ResultTable ORDER BY Row_Count Desc
If @Sort =2 SELECT * FROM #ResultTable ORDER BY Table_Size Desc
If @Sort =3 SELECT * FROM #ResultTable ORDER BY Unused_Space Desc
If @Sort =4 SELECT * FROM #ResultTable ORDER BY Index_Space_Used Desc
If @Sort =5 SELECT * FROM #ResultTable ORDER BY Data_Space_Used Desc
–Delete Temporay Tables
DROP TABLE #ResultTable
DROP TABLE #SizeTable
This query should work on SQL 2000, 2005 and 2008 instances.
