Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Identify Large Tables

By Jesse McLain,

The script can be run as-is, and will produce a report on the size of all user tables in the currently selected database. When I find that I need to trim the fat in a database, this report will quickly show where I can gain the most immediately. For example, a large table at the top of the results might have been created for a temporary purpose and can now be dropped, or perhaps is ripe for archiving older rows to a partition devoted to historical data.

The output consists of table name, number of rows, and its physical size, in kilobytes (including unused space), ordered by largest size to smallest.

I reluctantly used cursors for this, working with the assumption that the number of user tables in the database should be relatively small (numbering in the hundreds, or thousands at most), so that cursor performance would not be a problem. As a future improvement, I'd like to possibly convert this to set-based logic.

Update 2/13/09 - Added 'TRUE' param to call to 'sp_spaceused' - this updates the tableusage to provide more accurate results - thanks to Doug (http://www.sqlservercentral.com/Forums/UserInfo437086.aspx). Also changed use of Sysobjects to INFORMATION_SCHEMA.TABLES

Jesse McLain

jesse@jessemclain.com

www.jessemclain.com

www.jessesql.blogspot.com

Total article views: 3153 | Views in the last 30 days: 5
 
Related Articles
FORUM

how to count the number of characters in a postgres database

number of characters in a postgres database

FORUM

Database table design for huge number of columns

Database table design for Large number of columns

FORUM

Number of Non NULL Fields in a database

I have been tasked with finding the total number of fields in a given database for invoicing purpose...

FORUM

Tempdb database will depend on the number of CPUs (logical or physical)

Tempdb database will depend on the number of CPUs (logical or physical)

FORUM

user tables in database where the number of rows is less than 100

user tables in database where the number of rows is less than 100

Tags
maintenance    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones