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.
[SQL]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 + ”’

order by modify_date desc'

EXEC (@sql)

[/SQL]