Determining what was created

Question

Determining what was created

Rod at work

SSC-Dedicated

Points: 34007
More actions
June 25, 2010 at 2:58 pm

#141134

I've noticed a new table in our production database, which isn't familiar to me. It's got good information in it (a list of dates and which fiscal year they fall into). That's nice, and all that, but I'd like to know where it came from and what created it. I've asked my colleagues, but it's late on a Friday and I doubt I'll get any answer until next week.
But this got me to wondering, what else may have been created then. (I've right clicked the table in SSMS and saw that it was created on 6/6/2010.) I'm sure there's some way of determining what SQL objects were created in SQL Server, I just don't know how that's done. So, how do I determine what tables, views, SP's, etc were created on and after a given date?
Kindest Regards, Rod Connect with me on LinkedIn.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

the default trace captures all the DDL commands, including who dunnit info, but it rolls over if you have a lot of cahnges.

if not too much time has past, you should find it useful.

the GUI has a report based on it filtered by database:

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

There's no log kept. You can check the default trace, if it hasn't rolled over (it keeps 5 files of 20 MB only). If you want to track this kind of thing, you need either a server-side trace or a DDL trigger.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 3

You can check on create_Date and modif_Date in sys.objects.

But it will be better to go for DDL triggers to get the exact information.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

Rod at work SSC-Dedicated Points: 34007 More actions · Answer 4

Sweet! This is very useful information, thanks!

Kindest Regards, Rod Connect with me on LinkedIn.