SQL To Find The Latest Modified Database ObjectsBy 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.
© 2016 Strictly-Software