November 15, 2011 by Mike Hillwig
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)