Script to find table sizes in a database

  • Comments posted to this topic are about the item Script to find table sizes in a database

  • If you don't mind using undocumented procedures, you can simplify it slightly by replacing all the loop stuff with:

    exec sp_msforeachtable @command1='insert #TempTable exec sp_spaceused ''?'''

    I also convert the "KB" values from sp_spaceused into numbers to allow for easier manipulation.

  • Andrew Watson-478275 (12/14/2010)


    If you don't mind using undocumented procedures, you can simplify it slightly by replacing all the loop stuff with:

    exec sp_msforeachtable @command1='insert #TempTable exec sp_spaceused ''?'''

    I also convert the "KB" values from sp_spaceused into numbers to allow for easier manipulation.

    Though sp_msforeachtable procedure is undocumented, but it is really useful if we want to apply the changes to all the tables in the database - by using this we can avoid Loop or cursors. We are using this to enable/disable all the constraints at one go.

    Thanks

  • You don't really avoid using cursors or loops - if you have a look inside sp_msforeachtable and sp_msforeach_worker, you'll see it uses them itself.

    For me, the main reason for using it is that it's easier.

  • Great Script, Just a small tweak if i may;

    The 'Order By' clause you use on line 45 does not really work as the data column is a nvarchar and contains " KB".

    replace

    ORDER BY data DESC;

    with

    ORDER BY CONVERT(INT, replace(data,' KB','')) desc;

    handy for databases that contain 1000+ tables

    Knock Knock, Who's There?, sp underscore, sp underscore who?
    spid1, spid2, spid3....

  • Here are some tweaks I did on my SQL Server 2005 to get it to work for me...

    --DECLARE @i INT = 1;

    DECLARE @i INT ;

    SET @i = 1;

    --DECLARE @tableCount INT = (SELECT COUNT(1) FROM #AllTables );

    DECLARE @tableCount INT

    SET @tableCount = (SELECT COUNT(1) FROM #AllTables );

  • Don't forget to first use: DBCC UPDATEUSAGE(0);

    BOL says about this:

    Reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure.

  • In SQL 2008 I right click the database name in Object Explorer, select Reports / Standard Report / Disk Usage by Table.

  • To see sizes of all tables in SQL 2005 use this:

    DECLARE @tableName VARCHAR(1000);

    CREATE TABLE #AllTables

    (

    row_num INT IDENTITY(1, 1),

    table_name VARCHAR(1000)

    );

    --Using temp table, i dont like to use cursors

    INSERT INTO #AllTables

    (table_name)

    SELECT s.[NAME] + '.' + t.[name]

    FROM sys.Tables t, sys.schemas s

    WHERE t.[SCHEMA_ID] = s.[SCHEMA_ID]

    CREATE TABLE #TempTable

    (

    tableName VARCHAR(100),

    [rows] VARCHAR(100),

    reserved VARCHAR(50),

    data VARCHAR(50),

    index_size VARCHAR(50),

    unused VARCHAR(50)

    )

    DECLARE @i INT;

    DECLARE @tableCount INT;

    SELECT @i = 1;

    SELECT @tableCount = (SELECT COUNT(1) FROM #AllTables );

    --Loop to get all tables

    WHILE ( @i <= @tableCount )

    BEGIN

    SELECT @tableName = table_name

    FROM #AllTables

    WHERE row_num = @i;

    --Dump the results of the sp_spaceused query to the temp table

    INSERT #TempTable

    EXEC sp_spaceused @tableName;

    SET @i = @i + 1;

    END;

    --Select all records so we can use the reults

    SELECT *

    FROM #TempTable

    ORDER BY data DESC;

    --Final cleanup!

    DROP TABLE #TempTable

    DROP TABLE #Alltables;

  • gitmo (12/14/2010)


    In SQL 2008 I right click the database name in Object Explorer, select Reports / Standard Report / Disk Usage by Table.

    Oh great... Thanks. I don't know about this - it is really useful. You can also check no of records for all the tables in a single go.

    Thanks

  • can somebody tell me, how can we list all the columns from a temp(#) table without using SP_Help or system objects.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply