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
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 @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’
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.
Category Handy Scripts | Tags: