For anyone wanting a complete solution, this one was customised to avoid looking at tables containing identities, as well as Unique Indexes.

This saves a huge chunk of time in skipping...

Hi Tom,Nice to hear my environment is far better than I thought. The picture you paint is bleak.

My circumstance is legacy columns, too many indexes on high trans tables (one...

In addition to Tom's solution, there is another modification here to return rows where the Identity for the table has been excluded from the comparison.

create procedure dbo.finddups

...

Thanks tom

Only really had to add

SET @sql = ''

select @sql = @sql+','+'['+C.name+']' from sys.columns C

to take care of columns with spaces in the name and now I'm good to...

Thanks Tom

This is the direction I wanted to go in.

At worst, I would then replace table name and run your dynamic query together with information schema.

At best, I can use...

Thanks

Somehow this query is returning records which dont have duplicates, but what will help me a lot is the

sp_msforeachtable

Thanks for the hint!

Thanks for the response.

Yep, as I specified in my query, that is how it is done, with a particular query for a particular table.

What I am looking for is a...

Thanks Champ and GM.

All these indexes look like they were supposed to have included columns, or had them removed from the index later due to space but preserving the...

Create the common factor index without the included columns initially. Both queries should use this index, unless the index already exists on the table.

The reason you want to include...

BW_Toro (7/6/2011)

---

Why is there not more concern being expressed when the query cited by peter-757102 (see page 1) is probably causing incorrect results from this tool? I posted a related...

Brian O'Leary (7/5/2011)

---

doranmackay (7/5/2011)

---

For me it was quick to find the root cause.

So when you have stuff like "where [datetimecolumn] >=getdate()" (if datetimecolumn is indexed)

the optimizer says "You know...

Did you know it is possible to create duplicate indexes on one table, both non clustered?

One will never be used, but will cost in inserts and updates.

What reason would there...

For me it was quick to find the root cause.

I found many non clustered indexes included dateTime as part of a covering index. 6 indexes on the main mothershiptable of...