Get all the table sizes in a DataBase
Get all the tables along with their sizes in a DataBase
One of the DBA's work is to monitor the table sizes in a DataBase.
If we get all the tables sizes we can easily analyze which one has the
greater amount of data, the reserved space and also the rows in it.
Suppose if a user is taking a back up of a table and forget to delete,
if it's size is big, it will create more problem by occupying more space.
SQL DBA has to identify each tables and can delete the unwanted one.
Below is the Procedure which will list all the tables in a database and
also it will give the details on how many rows does each table own.
CREATE PROC GetTableSizes
/*----Comments-----
Created Date: 15th Dec 2009
Created By: Alwyn Duraisingh.M
Purpose: To get all the Tables and their Sizes in a DataBase
------*------*----*/
AS
SET NOCOUNT ON
DECLARE @TableName VARCHAR(255)
--Loading the user defined tables to a Temporary table
SELECT Table_Name into #Tables
FROM information_schema.tables
WHERE table_type = 'base table'
--Creating temporary table to hold the tables and details
CREATE TABLE #TablesAndDetails(
TableName NVARCHAR(100)
,Rows NVARCHAR(20)
,Reserved NVARCHAR(20)
,Data NVARCHAR(20)
,Index_Size NVARCHAR(20)
,UnUsed NVARCHAR(20) )
DECLARE Paging_Cursor CURSOR DYNAMIC READ_ONLY FOR
SELECT Table_Name FROM #Tables
OPEN Paging_Cursor
FETCH Next FROM Paging_Cursor INTO @TableName
--Looping through the cursor and get each table details
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
INSERT #TablesAndDetails
EXEC Sp_SpaceUsed @TableName
FETCH Next FROM Paging_Cursor INTO @TableName
END
CLOSE Paging_Cursor
DEALLOCATE Paging_Cursor
--Getting all the table details in a DB
SELECT *
FROM #TablesAndDetails
--Drop the Temp Tables Used.,
DROP TABLE #TablesAndDetails
DROP TABLE #Tables
SET NOCOUNT OFF
GO
Code to call the above Stored Procedure
EXEC GetTableSizes
Execute the above procedure and view the results, then start ur analysis...
