Advertisements
Home > DBA Stuff, SQL Tips > SQL DBA – When was the Table, View, Stored Procedure Created or Modified

SQL DBA – When was the Table, View, Stored Procedure Created or Modified


On my previous blog posts [link] I discussed about System Catalog views and some tips to get the informaion regarding Database objects, like: Tables, Views, etc.

Adding to that post, this post provides the basic information that various DBAs & Developers are interested in, i.e. when was the Table created? or When was that particular Stored Procedure modified or updated?

Following set of queries provides this information:

select object_id, name, create_date, type_desc, modify_date
from sys.tables
UNION
select object_id, name, create_date, type_desc, modify_date
from sys.views
UNION
select object_id, name, create_date, type_desc, modify_date
from sys.procedures
UNION
select object_id, name, create_date, type_desc, modify_date
from sys.triggers

-OR- a single query to check all objects information:

select object_id, name, create_date, type_desc, modify_date
from sys.all_objects
where type in ('U', 'V', 'P', 'TR' ,'FN', 'IF', 'TF')
order by type, name

By querying the sys.all_objects view you can also get the information about other DB objects other than listed above, like: Indexes, Constraints, Synonyms, Stats, etc.

MS BOL link on sysobjects and object-type codes: http://msdn.microsoft.com/en-us/library/ms177596.aspx

Advertisements
Categories: DBA Stuff, SQL Tips Tags:
  1. No comments yet.
  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: