Monday, January 11, 2010

How do I quickly get MSSQL table sizes?

Frequently when testing, you will need a quick way to get the size (in kilobytes) for all tables in a given database. For example, you can take before and after snapshots of all tables' sizes to quickly ensure the proper work was done on the right tables.

The following two-line SQL statement will automatically generate a much larger, custom-built SQL script that can be copied and executed to fetch sizes for all tables in the target database.

Follow these basic steps to use it:

STEP 1: Open SQL Server Management Studio

STEP 2: Open a "New Query" pointing to target database of your choice

STEP 3: Copy and run the generic "SQL Script Generator" statement below:
    SELECT 'EXEC sp_spaceused '
    + TABLE_NAME + ';'
    FROM INFORMATION_SCHEMA.TABLES






















STEP 4: Copy "Auto-Generated-SQL-Script" (AGSS) from text above (yellow execution results)

STEP 5: Paste the AGSS from Figure 2 (yellow highlight) into a new Query window

STEP 6: Execute the AGSS to see table counts for the entire database as shown in Figure 3 (green highlight)

No comments:

Post a Comment