Index created in the last few days

Question

Index created in the last few days

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
July 26, 2022 at 10:34 pm

#4068649

Hello,
I'm looking for a proper query to find the created indexes in the last few days. I use the below query but it does not appear to be perfect. Can anyone please share the proper query to use?

select top 30 *
from <dbname>.sys.indexes i
inner join <dbname>.sys.objects o
on i.object_id = o.object_id
where o.type not in ('S', 'IT')
and modify_date >= dateadd(day, -1, getdate())
order by modify_date desc

Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101117 More actions · Answer 1

There is no query that will tell you what index(es) were created in the last few days. SQL Server itself does not store that info anywhere.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

rothj Hall of Fame Points: 3412 More actions · Answer 2

You might check the schema change history report (data from default trace).

On the database, right click, choose Reports > Standard Reports > Schema Changes History from context menu.

Phil Parkin SSC Guru Points: 246977 More actions · Answer 3

rothj wrote:

You might check the schema change history report (data from default trace).
On the database, right click, choose Reports > Standard Reports > Schema Changes History from context menu.

Good tip. And this page shows how to query the default trace results using T-SQL.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 4

If your system is like mine, the data in the default trace lasts about 6 seconds. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ken McKelvey SSCoach Points: 18853 More actions · Answer 5

This series of articles shows a number of ways to track schema changes. Looking in the log or the log backups might help you if nothing is setup. Extended Events or DDL triggers will probably be better if you need to track changes in the future:

https://blog.sqlterritory.com/2018/11/20/5-ways-to-track-database-schema-changes-part-1-default-trace/

avnisaha Newbie Points: 5 More actions · Answer 6

This reply has been reported for inappropriate content.

There are numerous pieces of Silver Jewellery for the Woman of Today present with unique designs. But in today’s time, people consider buying Customized Jewellery. In this, customers have the freedom to choose the patterns, materials and designs. These silver jewellery defines a person’s taste and gives them an aesthetic look.

Attachments:

You must be logged in to view attached files.