Table Sizes 2

[ptheriault]

I'm looking to create a list of all the tables and indexes in my database orderd by size desc. I have written the following but I'm not sure it's correct.

select a.object_id, a.name, a.type_desc, b.object_id, b.name, b.type_desc, c.rows
FROM sys.objects a join sys.indexes b on a.object_id = b.object_id
join sys.partitions c on c.object_id = a.object_id
WHERE a.name not like 'sys%'
order by c.rows desc

 

[Catadmin]

What do you get when you run it in QA or SSMS?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[ptheriault]

It runs but I think I might be getting dupes. I'm not sure I have the join correct.

 

[mutley1]

Try this.

CREATE TABLE [#temp1] (
	[name] [varchar] (50),
	[rows] [int],
	[reserved] [varchar] (50),
	[data] [varchar] (50),
	[index_size] [varchar] (50),
	[unused] [varchar] (50)
) ON [PRIMARY]
GO

insert into #temp1
exec sp_msforeachtable "exec sp_spaceused '?'"

select *
from #temp1
order by [rows] desc

drop table #temp1

Rgds,

M.

 

[mutley1]

Sorry - just realised you are trying to get the names etc. in there as well aren't you.....

 

[ptheriault]

Yes I need the names. I have just started at this company and I need to learn as much as possible about this new database a consultant is creating for us.

 

[ptheriault]

I like that script. It's a keeper. Now I just need to learn what I have for indexes on each table.
Thanks

 

[manmaria]

I do not remember where I got this script but try this

SELECT LEFT(so.[name], 30) TableName, LEFT(si.[name], 50) IndexName, LEFT(sc.[name], 30) ColumnName, si.indid,
CASE si.indid WHEN 1 THEN 'Clustered' ELSE 'Non-Clustered' END IndexType, LEFT(sfg.groupname, 10) GroupName
FROM sysindexes si
INNER JOIN sysindexkeys sik ON (si.[id] = sik.[id] AND si.indid = sik.indid)
INNER JOIN sysobjects so ON si.[id] = so.[id]
INNER JOIN syscolumns sc ON (so.[id] = sc.[id] AND sik.colid = sc.colid)
INNER JOIN sysfilegroups sfg ON si.groupid = sfg.groupid
--where si.indid between 2 and 254 --only non-clustered indexes
--WHERE si.indid = 1 --only non-clustered indexes
WHERE si.[name] NOT LIKE 'sys%' --filter out tables that start with sys
AND si.[name] NOT LIKE '[u,n]c%sys%' --filter out tables that start with ncsys, nc1, nc2, ucsys
AND si.[name] NOT LIKE '_WA_%' --filter out tables that start with _WA_
AND si.[name] NOT LIKE 'hind_%' --filter out tables that start with hind_
AND so.[name] <> 'dtproperties' --filter out the dtproperties table
--AND sfg.groupname = 'PRIMARY'
ORDER BY so.[name], si.indid

 

[ptheriault]

Thanks for the help manmaria. This is what I was looking for.

 

[mutley1]

One from the SQLservercentral archives......I have so much info generating scipting on this machine but don't use many of them. As I say, thanks to someone else who scripted this - it is not mine! Plenty of info in this.

set nocount on 

select 	o.name as 'TableName',
	i.name as 'IndexName',
	CASE WHEN (i.status & 0x800)     = 0 THEN 0 ELSE 1 END AS 'Primary', 
	CASE WHEN (i.status & 0x10)      = 0 THEN 0 ELSE 1 END AS 'Clustered', 
	CASE WHEN (i.status & 0x2)       = 0 THEN 0 ELSE 1 END AS 'Unique', 
	CASE WHEN (i.status & 0x1)       = 0 THEN 0 ELSE 1 END AS 'IgnoreDupKey', 
	CASE WHEN (i.status & 0x4)       = 0 THEN 0 ELSE 1 END AS 'IgnoreDupRow', 
	CASE WHEN (i.status & 0x1000000) = 0 THEN 0 ELSE 1 END AS 'NoRecompute', 
	i.OrigFillFactor AS 'FillFactor', 
	i.rowcnt as 'Est.RowCount',
	i.reserved * cast(8 as bigint) as ReservedKB,  
	i.used * cast(8 as bigint) as UsedKB,  
	k.keyno as 'KeyNumber',
	c.name as 'ColumnName',
	t.name as 'DataType', 
	c.xprec as 'Precision',
	c.xscale as 'Scale', 
	c.iscomputed as 'IsComputed', 
	c.isnullable as 'IsNullable', 
	c.collation as 'Collation'
from 	           sysobjects   o with(nolock)
	inner join sysindexes   i with(nolock) on o.id    =  i.id
	inner join sysindexkeys k with(nolock) on i.id    =  k.id    and    i.indid =  k.indid
	inner join syscolumns   c with(nolock) on k.id    =  c.id    and    k.colid =  c.colid 
	inner join systypes     t with(nolock) on c.xtype =  t.xtype 

where 	o.xtype <> 'S' -- Ignore system objects
and 	i.name not like '_wa_sys_%' -- Ignore statistics

order by
	o.name, 
	k.indid,
	k.keyno

 

[ptheriault]

Thanks everyone. These scripts are great.