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.
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.