Confirming Objects Modified

I work in a hosting environment, and frequently our clients will ask us to promote a stored procedure (or some other schema object) through the DEV, TEST, Production environments. We have one client that is really big on seeing some type of evidence that we did what we say we did.

I wrote this little nugget that generates enough confirmation for the client’s relationship manager to demonstrate that we did indeed move their code. And it seems to make the client happy.

set nocount on
go

DECLARE @dbname VARCHAR(30)
DECLARE @num_objects INT
DECLARE @object_type VARCHAR(3)

SELECT @dbname = 'userdatabasename' -- Use the database where the objects were moved
SELECT @num_objects = 1   -- Use the number of objects moved.
SELECT @object_type = 'P' -- Use P for procedures, F for functions, U for tables, V for views, etc. 

DECLARE @SQL VARCHAR (1000)

select @@servername

SELECT @sql = 'select top ' + cast(@num_objects as varchar) + 
' left(name,30) as object, object_id, modify_date from ' + @dbname +
'.sys.objects where type = ''' + @object_type + '''</pre>
<pre>order by modify_date desc'

EXEC (@sql)

2 Responses to “ “Confirming Objects Modified”

  1. Khamar says:

    Hi,I agree this is not something fancy, but, I’ll also quote mylesf I’m going to explain a non often used SQL that came in hand recently . The thing is, that this can help you with let’s say complex menu table (that for example contains n-level menu). Then you avoid the need to pull the menu with more than 1 query.And one more thing This article was intended for beginner to intermediate level of knowledge a simple example And thanks for opinions!

  2. By Paul Riddle May 31, 2012 – 6:06 amHi, I am enquiring about sheet music for one of your pieces. A student of mine is wishing to perform ‘Big City’, is there any possibility of purchasing band charts for ‘Big City’?Cheers,Paul Riddle

Leave a Reply

Your email address will not be published. Required fields are marked *