If someone can help me in this.
I want a stored procedure which compares list of tables present in a database today with list of tables present in same db yesterday and if any changes like table deletion then mail that table name.
Just a script where we compare will work for me.
Thanks in advance
Stored procedures are tracked in sys.procedures. You'll need a daily task to export the names from sys.procedures plus a date stamp into a history table that you create. You can then compare any two days in that history table -
You can do the same with sys.tables, sys.views, etc., or you can track them all at once using sys.all_objects
Sticking with the stored procedure example, if <your_date> is today, and <your_other_date> is yesterday then this query gives you procedures from today that weren't there yesterday
select name from history_table where date_stamp = <your_date> except select name from history_table where date_stamp = <your_other_date>
and this one gives you procedures that were there yesterday but not today
select name from history_table where date_stamp = <your_other_date> except select name from history_table where date_stamp = <your_date>
A common alternate approach to this is to use a DDL trigger to either log changes or notify you immediately when an object is created/dropped/altered - https://docs.microsoft.com/en-us/sql/relational-databases/triggers/ddl-triggers?view=sql-server-2017
20 People are following this question.
