Getting Last Modified/Created Table, Stored Procedure, Function.. in Sql Server

Sometimes, we want to know how many tables, stored procedures or functions we have created on a particular day, or on which tables, sps or functions, modifications have occured. All this can be known through a very simple sql query, which is as below:

select name,type,type_desc, create_date, modify_date from sys.objects

where  convert(varchar,modify_date,101)  =  convert(varchar,getdate(),101)

order by modify_date desc

Here

name : – “Its the table or sp or function name”

type: – “To identify a table or sp or function etc.. P, S, U”

type_desc: -”Description whether it is table, sp etc..”

- S = System Table, PK = Primary Key,U = User Table,

- P= Stored Procedure, TF = Table Value Function,

- FN = Scalar Function, D= Default Constraint

create_date: – “Date when it is created”

modify_date: – “Last modified datetime of table, sp,.. etc”

In above query, in place of getdate(), you can pass the date you want, also you can get complete list after removing where condition, just giving order by modified date from sys objects..

Very important when we want to generate scripts for particular tables, sps or function for a particular date.