question

tiwaryrohit143 avatar image
tiwaryrohit143 asked

Compare names of tables which was present yesterday and present today in same database

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

t-sqlsql-server-2012tablesprocedure
10 |1200 characters needed characters left characters exceeded

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

1 Answer

· Write an Answer
KenJ avatar image
KenJ answered

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

10 |1200 characters needed characters left characters exceeded

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

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.