We are in the process of refining some of our data types. As part of this, we needed to know the space usage and row count of each table in the database before we begin.
We were able to use the system stored procedure: sp_spaceUsed.
The sp_spaceUsed stored procedure can be passed an argument:
Which will then return the stats for that table: including the number of rows, table size and index size.
Using this stored procedure, along with the undocumented sp_MSforEachTable we can loop through all the tables in the database and dump the results into a temp table.
See the code below:
-- Create Table to hold all tables and their values
CREATE TABLE #TableUsage
, rows int
, reserved varchar(50)
, data varchar(50)
-- Use the undocumented stored procedure
-- sp_MSforEachTable with the sp_spaceUsed procedure
INSERT INTO #TableUsage
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"
-- Now view the result set
Select * FROM #tableUsage