Show Table, Index and Image/Text size.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Eder Dias » Show Table, Index and Image/Text size.

Show Table, Index and Image/Text size.

Rate Topic

Display Mode

Topic Options

Author

Message

Eder F Dias

Posted Saturday, September 29, 2007 10:45 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, January 06, 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

Stevie T

Posted Friday, November 30, 2007 7:28 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, December 07, 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

toniupstny

Posted Wednesday, December 05, 2007 9:46 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, February 22, 2013 9:04 AM
Points: 241, Visits: 928

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

toniupstny

Posted Wednesday, December 05, 2007 12:20 PM

SSC Veteran

Group: General Forum Members
Last Login: Friday, February 22, 2013 9:04 AM
Points: 241, Visits: 928

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

PhilPacha

Posted Tuesday, January 08, 2008 12:44 PM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 2:47 PM
Points: 322, Visits: 2,001

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 »

Permissions