List Recently Created Objects

The code below provides a list of all SQL Server objects created in the past “x” number of days. Dynamic T-SQL is used to construct a query for each database, including system databases. Each query provides the schema, name, and date created for each object listed, along with the object type description.

Still Life with Jug, Burkemeyer and Smoking Utensils, by Pieter Claesz - a lot of objects but no objects created recently!

Still Life with Jug, Burkemeyer and Smoking Utensils, by Pieter Claesz – a lot of objects but no objects created recently!

Modify the DECLARE @StartDate datetime = DATEADD(DAY, -3, GETDATE()); line below to set the period of time you’re interested in. As shown, the start date is exactly 72 hours prior to now. If you don’t modify the script, only objects that have been created in the past 72 hours will be listed.

Output looks like:

╔═══════════╦════════════════════════════════════════╦═════════════════════╦════════════════════════╗
║ Database  ║               ObjectName               ║     CreateDate      ║       ObjectType       ║
╠═══════════╬════════════════════════════════════════╬═════════════════════╬════════════════════════╣
║ dba_admin ║ [dbo].[BlockedProcess_xml_events]      ║ 2019-06-04 12:57:36 ║ USER_TABLE             ║
║ dba_admin ║ [dbo].[GatherBlockedProcessEvents]     ║ 2019-06-04 13:00:41 ║ SQL_STORED_PROCEDURE   ║
║ tempdb    ║ [dbo].[BlockingTest]                   ║ 2019-06-06 10:03:45 ║ USER_TABLE             ║
║ tempdb    ║ [dbo].[PK__#B750992__6894C54B6FD2CB4F] ║ 2019-06-05 00:00:00 ║ PRIMARY_KEY_CONSTRAINT ║
║ tempdb    ║ [dbo].[PK__#temp_jo__6E32B6A56BCEBE4C] ║ 2019-06-04 13:04:30 ║ PRIMARY_KEY_CONSTRAINT ║
╚═══════════╩════════════════════════════════════════╩═════════════════════╩════════════════════════╝

I find this code useful in the following scenarios:

  1. Post implementation – use the list to confirm the objects created match the list of items you expected.
  2. Finding tables created in the wrong database. For instance, have tables been created in master recently?
  3. Did SQL Server automatically create any new statistics objects recently?

There are probably a bunch of other circumstances where the list might useful… add a comment if you can think of one not listed above!

For reference purposes, the code queries the system catalog views sys.schemas, sys.objects, and sys.databases in each database.

Ads by Google, Paying the Rent: