Handy Script: Confirming Objects Modified

Leave a comment

April 15, 2015 by Mike Hillwig

One of the things my team does frequently for clients is moving objects between environments. We have to migrate a procedure from DEV to Test, or we may move it from Test to Production. The migration is easy enough. But when responding back to the client, I hate the idea of saying “all set” or “done.” Instead, I’d rather show something that proves we did what we said we did. More importantly, I needed something that would work against multiple databases on multiple servers. That’s where this script came from.


set nocount on
go

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

SELECT @dbname = 'MyDB' -- 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)

The top three SELECT statement are the parameters. It generates a nice, little output that shows the server, database, and object.  It also shows the object ID and the date/time of modification. That’s enough to paste into a ticket to let the client know we’ve fulfilled their request.

objects_modified_confirmation

This is actually one of the first little scripts I wrote when I joined this company. It’s such a little thing that’s very simple, but it’s served us very well for nearly four years.