SQL To Find The Latest Modified Database Objects

SQL To Find The Latest Modified Database Objects

By Strictly-Software

Lots of times I want to quickly see which database objects I have modified lately without having to open up specialist programs such as AdeptSQL or Redgate etc.

By using the System Views you can easily find the objects you have recently created or modified.

The sys.objects view is what we use here and we can filter the types of object very easily with the [type] column.

The (main) values for this are:

P = Stored Procedure 
U = User Table (includes non clustered indexes added to it) 
D = Default Value Constraint 
FN = Scalar User Defined Function 
TF = Table User Defined Function 
PK = Primary Key 
UQ = Unique Constraint 
SN = Synonym
V   = View

If you really wanted to, you could search the system tables, default constraints, and other objects such as...

D   = Default Constraint
F   = Foreign Key Constraint
FS = CLR Scalar Function
PC = CLR Stored Procedure
IF  = SQL Inline Table Valued Function
IT  = Internal Table
S    = System Table
SQ = Service Queue
X   = Extended Stored Procedure


This example however looks for the latest modified User Defined Functions (Scalar and Table), and Stored Procedures.

SELECT name, create_date, modify_date, [type]
FROM sys.objects
WHERE [type] IN('P' , 'FN', 'TF')
ORDER BY modify_date DESC

This example looks for the most recent created stored procedures that start with the name usp_net_save

SELECT name, create_date
FROM sys.objects
WHERE [type] = 'P' 
 AND name like 'usp_asp_save%'
ORDER BY create_date DESC

This is a very quick and easy way to find the code in an SQL database that you have either modified or updated.

By Strictly-Software

© 2016 Strictly-Software