Stored procedure to find the biggest tables in the database

UPDATE: The sp_SOS procedure is not working with the latest versions of SQL Server.

Use Stored procedure to identify the top n biggest tables in a database instead

 

To find the biggest tables in the SQL Server database I am using good stored procedure

 

EXEC dbo.sp_SOS @OrderBy=‘T’ 

/*downloaded from http://media.techtarget.com/searchSQLServer/downloads/URL_for_SQL_Script_download_Tip_on_Sp_SOS.doc
The related article “Find size of SQL Server tables and other objects with stored procedure

Valid @OrderBy parameters are:

‘N’ –> Listing by object name

‘R’ –> Listing by number of records

‘T’ –> Listing by total size

‘U’ –> Listing by used portion (excluding free space)

‘I’ –> Listing by index size

‘D’ –> Listing by data size

‘F’ –> Listing by unused (free) space

‘Y’ –> Listing by object type

*/

It’s a start point before you will Archive Old data or consider to Partition table (see Sql Server 2005 – Twelve Tips For Optimizing Query Performance by Tony Wright)

 

 

Advertisements