SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Show Table, Index and Image/Text size.


Show Table, Index and Image/Text size.

Author
Message
Eder F Dias
Eder F Dias
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 26
Comments posted to this topic are about the item Show Table, Index and Image/Text size.
Stevie T
Stevie T
Old Hand
Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)

Group: General Forum Members
Points: 343 Visits: 95
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



toniupstny
toniupstny
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1828 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
toniupstny
toniupstny
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1828 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"
PhilPacha
PhilPacha
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1704 Visits: 2542
Why do you use KiloBits for your labels (Kb), when you are calculating KiloBytes (KB)? Capitalization matters, since it changes the meaning.



Iwas Bornready
Iwas Bornready
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35348 Visits: 886
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search