September 29, 2007 at 10:45 pm
Comments posted to this topic are about the item Show Table, Index and Image/Text size.
November 30, 2007 at 7:28 am
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
December 5, 2007 at 9:46 am
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
December 5, 2007 at 12:20 pm
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"
January 8, 2008 at 12:44 pm
Why do you use KiloBits for your labels (Kb), when you are calculating KiloBytes (KB)? Capitalization matters, since it changes the meaning.
May 3, 2016 at 7:02 am
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