Get Table Row Size / Count for All Tables With a Database – TSQL Script


USE DWDATA
GO
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'ps_GetAllTableSizes' AND type = 'P')
DROP PROCEDURE ps_GetAllTableSizes
GO

CREATE PROCEDURE ps_GetAllTableSizes
AS
/*
Obtains spaced used data for ALL user tables in the database
*/
--For storing values in the cursor
DECLARE @TableName VARCHAR(100)

--Cursor to get the name of all user tables from the sysobjects listing
DECLARE tableCursor CURSOR
FOR
select [name]
from dbo.sysobjects
where OBJECTPROPERTY(id, N'IsUserTable') = 1
FOR READ ONLY

--Create temp table to store the results
CREATE TABLE #TempTable
(
tableName varchar(100),
numberofRows varchar(100),
reservedSize varchar(50),
dataSize varchar(50),
indexSize varchar(50),
unusedSize varchar(50)
)

--Open the cursor
OPEN tableCursor

--Get the first table name from the cursor
FETCH NEXT FROM tableCursor INTO @TableName

--Loop until the cursor was not able to fetch
WHILE (@@Fetch_Status >= 0)
BEGIN
--Dump the results of the sp_spaceused query to the temp table
INSERT #TempTable
EXEC sp_spaceused @TableName

--Get the next table name
FETCH NEXT FROM tableCursor INTO @TableName
END

--Get rid off the cursor
CLOSE tableCursor
DEALLOCATE tableCursor

--Select all records so we can use the reults
-- Order by the biggest ones
SELECT *
FROM #TempTable
ORDER BY LEN(dataSize) DESC, LEFT(datasize,1) DESC

--Final cleanup!
DROP TABLE #TempTable

GO

-- Execute the Store procedure for testing
-- Once the store procedure has been implemented, you just need to run the code below to get the results
EXEC ps_GetAllTableSizes

GO

Sample Results:

sampleResults

 

, , , , ,

No comments yet.

Leave a Reply

*