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


Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: