Finding deployment data to a SQL Database

Question

In an effort to maintain versions of the databases we have in our CMDB, I have to obtain the versions of some databases deployed to our servers by a third party company.

Is there a system table, view or procedure that allows me to view information regarding recent deployments (code changes from an update script) to a SQL database?

Answer 1

You have three options.

First, you can build your own logging based on a table and a ddl trigger which will log each change in any procedure etc.

Second, you can track changes in one of this sys tables:

select * from sys.all_sql_modules -- Get the sourcecode of each proc (and track it)
select * from sys.objects -- Get information which object is modified at which date

Third, you can reverse engineer changes in the recent past by reading the trace log of the sql server itself and filter for drop/create events. (needed SA permission)

-- Get the current server trace file 
select * 
from fn_trace_getinfo(NULL)
where property=2
and traceid = 1

-- Copy value from the query above and paste it here
select * 
from fn_trace_gettable('[PASTE PATH HERE!]', -1) 
where EventClass IN(46,47) -- Create/Drop Object

Hopefully there is one solution for you which is helpfully for you.

But by the way. Another idea is, if your workflow allows this. Just use SSDT to create deployment packages and keep track of your changes.

Best regards, Ionic