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: 100952 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) A socialist is someone who will give you the shirt off *someone else's* back.

rothj Hall of Fame Points: 3352 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: 246723 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.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Jeff Moden SSC Guru Points: 1003894 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: 18776 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.