Get size of all tables

Here is the SP to get the size of all table:


Create PROCEDURE dbo.GetAllTableSizes
AS

Print 'Read complete Article in case of collation conflict error '

IF Object_ID ('tempdb..#TempTable') Is Not Null DROP TABLE #TempTable

----Declare Cursor
DECLARE @TableName VARCHAR(100)
DECLARE tableCursor CURSOR
FOR
Select Schema_Name(Schema_ID) + '.'+T.Name-----Concate schema also
From Sys.Tables T
For Read Only

--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

--Close cursor
CLOSE tableCursor
DEALLOCATE tableCursor

--Select all records so we can use the reults
SELECT Schema_Name(Schema_ID) AS [Schema],T.*
FROM #TempTable T
Inner Join sys.tables ST On T.tableName = ST.Name ----To Disply the schema
Order By T.numberofRows Desc

Go
Exec Dbo.GetAllTableSizes

/*-If you face collation conflict error like "Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
"
Just do simply following steps:
1. Execute sp_Help [sys.tables]
2. Copy Collation for the column NAME
3. Paste that copied collation in SP at temp table script part,
Your temp table in SP will be looks like:
CREATE TABLE #TempTable
(
tableName varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS,
numberofRows varchar(100),
reservedSize varchar(50),
dataSize varchar(50),
indexSize varchar(50),
unusedSize varchar(50)
)

4. Alter the SP
5. Execute again.


Thanks,
Randhir