Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

SQL Scripts & Samples

Posted Nov 17, 2008

Quick Table and Index Size Script

By DatabaseJournal.com Staff


This script from Matthew L. Kearns quickly returns table and index disk space and row counts for all tables in the current database. Essentially, the script puts the table names into a cursor and then spits out sp_spaceused results into a temp table. The contents of the temp table can be displayed in the sort order desired by the user.


SET NOCOUNT ON

CREATE TABLE #TBLSize
 (Tblname varchar(80), 
 TblRows int,
 TblReserved varchar(80),
 TblData varchar(80),
 TblIndex_Size varchar(80),
 TblUnused varchar(80))

DECLARE @DBname varchar(80) 
DECLARE @tablename varchar(80) 

SELECT @DBname = DB_NAME(DB_ID())
PRINT 'User Table size Report for (Server / Database):   ' + @@ServerName + ' / ' + @DBName
PRINT ''
PRINT 'By Size Descending'
DECLARE TblName_cursor CURSOR FOR 
SELECT NAME 
FROM sysobjects
WHERE xType = 'U'

OPEN TblName_cursor

FETCH NEXT FROM TblName_cursor 
INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
   INSERT INTO #tblSize(Tblname, TblRows, TblReserved, TblData, TblIndex_Size, TblUnused)
   EXEC Sp_SpaceUsed @tablename
      
   -- Get the next author.
   FETCH NEXT FROM TblName_cursor 
   INTO @tablename
END

CLOSE TblName_cursor
DEALLOCATE TblName_cursor

SELECT  CAST(Tblname as Varchar(30)) 'Table',
	CAST(TblRows as Varchar(14)) 'Row Count',
	CAST(LEFT(TblReserved, CHARINDEX(' KB', TblReserved)) as int) 'Total Space (KB)',
        CAST(TblData as Varchar(14)) 'Data Space',
	CAST(TblIndex_Size  as Varchar(14)) 'Index Space',
        CAST(TblUnused as Varchar(14)) 'Unused Space'
FROM #tblSize
Order by 'Total Space (KB)' Desc

PRINT ''
PRINT 'By Table Name Alphabetical'


SELECT  CAST(Tblname as Varchar(30)) 'Table',
	CAST(TblRows as Varchar(14)) 'Row Count',
	CAST(LEFT(TblReserved, CHARINDEX(' KB', TblReserved)) as int) 'Total Space (KB)',
        CAST(TblData as Varchar(14)) 'Data Space',
	CAST(TblIndex_Size  as Varchar(14)) 'Index Space',
        CAST(TblUnused as Varchar(14)) 'Unused Space'
FROM #tblSize
Order by 'Table'

DROP TABLE #TblSize




SQL Scripts & Samples Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
SQL Scripts & Samples Forum
Topic By Replies Updated
sql help!! add 1 August 22nd, 11:58 AM
How To Combine These 2 Queries into 1 Query ? tarek_land 1 June 7th, 08:37 AM
solving query svibuk 1 February 3rd, 06:08 AM
converting from a character string to uniqueidentifier saturnius 4 January 4th, 05:56 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date