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

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.

Advertisements
  1. November 25, 2010 at 2:51 am

    Thanks dude, very helpful.

  2. May 14, 2011 at 9:39 pm

    Very nice , thanks a sharing..

  3. July 26, 2013 at 11:44 am

    Thanks… It helped me 🙂

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: