question

jxia88 avatar image
jxia88 asked

Audit DDL without any impact to existing server instance.

I given a task for auditing any DDL changes in one particular server instance. There are a few databases on the server and I cannot create any objects or any writes on the server. How can I hit a dictionary table and generate list like Owner, Object Name, Object Type, Last DDL time, Created Time. I plan to run this query from time to time to compare the output. I am a Oracle DBA and have No knowledge of sql server. Please help! Thanks.
ddl-changes
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Not exactly what you are looking for, but might help a bit: SELECT S.name AS [schema_name] ,O.name AS [object_name] ,O.type_desc AS object_type ,O.modify_date ,O.create_date FROM sys.objects O LEFT JOIN sys.schemas S ON S.[schema_id] = O.[schema_id] WHERE O.is_ms_shipped = 0 ORDER BY O.type_desc ,S.name ,O.name Note that the modify_date columns is defined as: "Date the object was last modified by using an ALTER statement. If the object is a table or a view, modify_date also changes when a clustered index on the table or view is created or altered."
0 Likes 0 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.