May 27, 2016 at 9:36 am
How do I find which tables and indexes in a particular database is giving this message?
Indexes have been identified with an index key larger than the recommended size (900 bytes)
Thank you.
May 27, 2016 at 9:47 am
Try this:
SELECT
OBJECT_NAME(I.object_id),
I.name,
I.type_desc,
Cols.IDX_Width
FROM
sys.indexes AS I
CROSS APPLY (SELECT
SUM(C.max_length) AS IDX_Width
FROM
sys.index_columns AS IC
INNER JOIN sys.columns AS C
ON C.object_id = IC.object_id
AND C.column_id = IC.column_id
WHERE
IC.object_id = I.object_id
AND IC.index_id = I.index_id) AS Cols
WHERE
I.type_desc != 'HEAP'
AND I.object_id IN (SELECT
AO.object_id
FROM
sys.all_objects AS AO
WHERE
AO.type IN ('V', 'U'))
ORDER BY
Cols.IDX_Width DESC;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 27, 2016 at 10:20 am
Thank you.
But is also picking for included columns, I want to exclude that one
May 27, 2016 at 11:16 am
Customize the sub-query Where clause to suit your needs, of course.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply