As a SQL Server database administrator (DBA), it is crucial to have control over the database environment and ensure that no unnecessary objects are created without prior approval. Recently, I had an interesting conversation with a DBA friend who was tasked with identifying tables that were created in the past week in the production server. In this blog post, I will share a simple script that can be used to accomplish this task.

One approach to solving this problem is to build an audit system. However, I suggested a simpler solution that involves running a script to identify the tables created or modified within the past week. Here is the script:

SELECT o.name AS [Object_Name],
       s.name AS [Schema_Name],
       o.type_desc AS [Description],
       o.create_date AS [Creation_Date],
       o.modify_date AS [Modified_Date]
FROM sys.all_objects o
LEFT OUTER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE create_date > (GETDATE() - 7)
   OR modify_date > (GETDATE() - 7)

This script retrieves the name of the object, schema name, object type, creation date, and modification date from the system catalog views. By specifying a date range of the past week using the GETDATE() function, we can filter the results to only include tables that were created or modified within that timeframe.

The output of the script can be added to a SQL Server job and automatically sent as an email to the DBA or security team. Alternatively, you can run an SSRS report and schedule it to be sent via email to the security team.

If you prefer a more user-friendly approach, SQL Server Management Studio (SSMS) provides a built-in “Schema Change History” report that can be used to obtain the same information. This report displays the object name, schema name, object type, creation date, and modification date in a formatted manner.

Most of these tips and tricks in SSMS are simple yet powerful, and often go unnoticed. If you have encountered similar requirements in your role as a DBA, I would love to hear about your experiences and the solutions you implemented. Feel free to share your thoughts and insights in the comments section below.