Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Show Table, Index and Image/Text size. Expand / Collapse
Author
Message
Posted Saturday, September 29, 2007 10:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 6, 2010 11:10 AM
Points: 1, Visits: 26
Comments posted to this topic are about the item Show Table, Index and Image/Text size.
Post #404596
Posted Friday, November 30, 2007 7:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, December 7, 2012 6:43 AM
Points: 49, Visits: 94
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


Post #428096
Posted Wednesday, December 5, 2007 9:46 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 1, 2014 5:51 AM
Points: 242, Visits: 940
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
Post #429842
Posted Wednesday, December 5, 2007 12:20 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 1, 2014 5:51 AM
Points: 242, Visits: 940
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"
Post #429891
Posted Tuesday, January 8, 2008 12:44 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 9:38 AM
Points: 357, Visits: 2,318
Why do you use KiloBits for your labels (Kb), when you are calculating KiloBytes (KB)? Capitalization matters, since it changes the meaning.


Post #440246
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse