November 10, 2009 at 2:03 pm
Hello frineds, i use SQL S2205. Now can someone help me with a query that will show me the indexes on a database that has large sizes eg. more than atleast 500 MB.
November 10, 2009 at 2:31 pm
any ideas friends?
November 10, 2009 at 2:53 pm
courtesy of Mr. Barry Young
/*
vwTableInfo - Table Information View
This view display space and storage information for every table in a
SQL Server 2005 database.
Columns are:
Schema
Name
Ownermay be different from Schema)
Columnscount of the max number of columns ever used)
HasClusIdx1 if table has a clustered index, 0 otherwise
RowCount
IndexKBspace used by the table's indexes
DataKBspace used by the table's data
16-March-2008, RBarryYoung
31-January-2009, Edited for better formatting
*/
--CREATE VIEW vwTableInfo
-- AS
SELECT SCHEMA_NAME(tbl.schema_id) as [Schema]
, tbl.Name
, Coalesce((Select pr.name
From sys.database_principals pr
Where pr.principal_id = tbl.principal_id)
, SCHEMA_NAME(tbl.schema_id)) as [Owner]
, tbl.max_column_id_used as [Columns]
, CAST(CASE idx.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [HasClusIdx]
, Coalesce( (Select sum (spart.rows) from sys.partitions spart
Where spart.object_id = tbl.object_id and spart.index_id < 2), 0) AS [RowCount]
, Coalesce( (Select Cast(v.low/1024.0 as float)
* SUM(a.used_pages - CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id = p.partition_id
Where i.object_id = tbl.object_id )
, 0.0) AS [IndexKB]
, Coalesce( (Select Cast(v.low/1024.0 as float)
* SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id = p.partition_id
Where i.object_id = tbl.object_id)
, 0.0) AS [DataKB]
, tbl.create_date, tbl.modify_date
FROM sys.tables AS tbl
INNER JOIN sys.indexes AS idx ON (idx.object_id = tbl.object_id and idx.index_id < 2)
INNER JOIN master.dbo.spt_values v ON (v.number=1 and v.type='E')
order by datakb desc
---------------------------------------------------------------------
November 10, 2009 at 2:54 pm
Rather impatient aren't you... that said try this (Contributed by one unknown to me, prior to your asking the question. To this individual we both owe a hearty THANK YOU.
Select obj.name,
(Select Sum(((used * 8192.00)/1024))
from sysindexes
where id in (select objChild.id from sysobjects objChild where objChild.name = obj.name)
and indid in(0,1)
) TableSizeKb,
(Select Sum(((used * 8192.00)/1024)) from sysindexes where
id in (select objChild.id from sysobjects objChild where objChild.name = obj.name)
and indid not in (255,0,1)
) IndexSizeKb
From sysobjects obj
Where type = 'U'
November 10, 2009 at 3:03 pm
Thanks bitbucket-25253 the output is what i want but just need to add one column that says table name.
Name Tablesizekb Indexsizekb
pe_lymph_detail_ 44856.0000000 3856.0000000
ORT_Surg_Schedule_25728.00000006920.0000000
but if one more column that says Table name then would be great. Please can you get me a script that will show table name also.
I appreciate you work.
Thanks
November 10, 2009 at 3:36 pm
I owe bitbucket a favour so:
the 'name' column is the name of the table.
for clarity you could change the first line of the query to
Select obj.name as 'table',
barrys code also lists the table name.
---------------------------------------------------------------------
November 10, 2009 at 3:38 pm
November 10, 2009 at 3:44 pm
bitbucket-25253 (11/10/2009)
Use the code posted by george sibbald-364359 to this forum.
you can call me just george sibbald. Despite that darned number suffix which I hate I am the only george sibbald on the forum. 🙂
---------------------------------------------------------------------
November 10, 2009 at 4:19 pm
Anthony, this might be more what you originally asked for:
Select o.name as 'table', i.indid,i.name as 'index',i.used * (8192.00/1024) as 'index size in KB' from sysindexes i
inner join sysobjects o on i.id = o.id
where i.indid not in (255,0)
and o.type = 'U'
--and i.used > 64000
order by o.name,i.used desc
the commented out line will limit it to indexes > 500MB (= 64000 pages)
remember indid = 1 is the clustered index and therefore actually the size of the table.
---------------------------------------------------------------------
November 10, 2009 at 5:25 pm
george sibbald-364359 (11/10/2009)
you can call me just george sibbald. Despite that darned number suffix which I hate I am the only george sibbald on the forum. 🙂
George,
You can change that via the Control Panel
It's registration details you want. This link might take you straight there: https://www.sqlservercentral.com/MyAccount
November 10, 2009 at 6:05 pm
A 2005-specific version:
;WITH NonclusteredIndexes
AS (
SELECT [schema_name] = SCHEMA_NAME(SO.[schema_id]),
[object_name] = OBJECT_NAME(PS.[object_id]),
index_name = SI.name,
used_MB = FLOOR(SUM(used_page_count) / 128.0)
FROM sys.dm_db_partition_stats PS
JOIN sys.objects SO
ON SO.[object_id] = PS.[object_id]
JOIN sys.indexes SI
ON SI.[object_id] = PS.[object_id]
AND SI.index_id = PS.index_id
WHERE SI.type_desc = N'NONCLUSTERED'
GROUP BY
SCHEMA_NAME(SO.[schema_id]),
OBJECT_NAME(PS.[object_id]),
SI.name
)
SELECT *
FROM NonclusteredIndexes
WHERE used_MB >= 500
ORDER BY
used_MB DESC;
November 11, 2009 at 6:50 am
thanks for the tip Paul but unfortunately when I say I am the only george sibbald on the forum that is true, but the nickname george sibbald is taken, a I registered again when I changed jobs a few years back. Unfortunately the original unused account got to keep the name.
I tried to get it changed via the webmaster, but no reply.
Its a shame as I have 19 points on that old account 🙂
---------------------------------------------------------------------
November 13, 2009 at 11:49 pm
Check your messages George! 😉
November 14, 2009 at 4:07 am
Hey Paul, I heard from steve, the wheels are in motion. Thanks a lot mate 😀
---------------------------------------------------------------------
November 14, 2009 at 7:41 am
yea, I got my name back!
😀
---------------------------------------------------------------------
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply