Show Table, Index and Image/Text size.

  • Comments posted to this topic are about the item Show Table, Index and Image/Text size.

  • I like it, but I've got a situation where I want to get it for each db in my instance. I've tried using the sp_MSforeachdb but can't seem to get it working? I've also included the db_name() function so that I can get the full list... Any help would be appreciated

  • Getting Sizes for Each Index...

    I borrowed the concept to create a script to get size of each index instead of size of all indexes for the table.

    /* Get tablename, tableid, index name, index id and size in KB(descending) of all indexes for the User tables */

    /* Toni M. */

    select object_name(x.id) 'table name', x.id 'table id',x.name 'index name', x.indid 'index id',

    ((x.used * 8192.00)/1024) IndexSizeKb

    from sysindexes x

    where Substring(x.name,1,7) <> '_WA_SYS' -- do not include statistics

    and x.indid not in (0,255) and -- not a table or TEXT/IMAGE

    exists(Select type from sysobjects o where o.name = object_name(x.id) -- table name for index

    and o.type='U' ) -- user table

    order by IndexSizeKb desc

  • Hi.. this is how I got a proc to run on all dbs

    you need to create the proc from the statement(s) on each database (using your standard distribution methods I assume - if you know how to do this please let me know - just learning)

    Create procedure indexlist

    as

    select db_name() 'DataBase',object_name(x.id) tblnm,x.name 'index name',

    --rest of query

    order by IndexSizeKb desc

    go

    Then you can use sp_MSforeachdb such as.....

    EXEC sp_MSforeachdb @command1 = "Use ? Exec indexlist"

  • Why do you use KiloBits for your labels (Kb), when you are calculating KiloBytes (KB)? Capitalization matters, since it changes the meaning.

  • Thanks for the script.

Viewing 6 posts - 1 through 5 (of 5 total)

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