SQL with Manoj

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