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!
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
DECLARE @StartDate datetime = DATEADD(DAY, -3, GETDATE()); DECLARE @cmd nvarchar(max); DECLARE @params nvarchar(max); SET @params = N'@StartDate datetime'; SET @cmd = N''; SELECT @cmd = @cmd + CASE WHEN @cmd = N'' THEN N'' ELSE N' UNION ALL ' END + N'SELECT [Database] = ''' + d.name + N''' , ObjectName = QUOTENAME(sc.name) COLLATE SQL_Latin1_General_CP1_CI_AS + N''.'' + QUOTENAME(so.name) COLLATE SQL_Latin1_General_CP1_CI_AS , CreateDate = CONVERT(varchar(30), so.create_date, 120) , ObjectType = so.type_desc FROM ' + QUOTENAME(d.name) + N'.sys.objects so INNER JOIN ' + QUOTENAME(d.name) + N'.sys.schemas sc ON so.schema_id = sc.schema_id WHERE so.create_date >= @StartDate AND so.name NOT LIKE N''#%'' AND so.is_ms_shipped = 0' FROM sys.databases d WHERE d.state_desc = N'ONLINE'; PRINT @cmd; SET @cmd = @cmd + N' ORDER BY 1, QUOTENAME(sc.name) COLLATE SQL_Latin1_General_CP1_CI_AS + N''.'' + QUOTENAME(so.name) COLLATE SQL_Latin1_General_CP1_CI_AS, 3;' EXEC sys.sp_executesql @cmd, @params = @params, @StartDate = @StartDate; |
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:
- Post implementation – use the list to confirm the objects created match the list of items you expected.
- Finding tables created in the wrong database. For instance, have tables been created in
masterrecently? - 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.
Hi Hannah;
I ran the script on my instance but it has an error related to collation:
Msg 451, Level 16, State 1, Line 1
Cannot resolve collation conflict between “Latin1_General_100_CI_AS_KS_WS_SC” and “Latin1_General_BIN” in UNION ALL operator occurring in ORDER BY statement column 2.
Do you know how I can solve the question to run your script successfully in my database?
Thank you.
Carlos.
HI Carlos,
Thanks for your input! I’ve added
COLLATEstatements to the script that should fix the issue you’re seeing. Let me know how it works out!Cheers,
Hannah