I've been given a task to improve the performance of a database (SQL server) that a 3rd party manages. I've decided to do this by suggesting rewrites to the current views.
To help accomplish this I've asked that I be kept in the loop in regards to any structural DB changes (Addition or alteration to tables, views, function, ...) that will be applied to our UAT db (we have a QA, UAT and Prod version of the DB). This way I can update my views to reflect the changes being applied.
From this request, I feel like the 3rd party might be trying to drown me in information. For an update, I'm getting multiple files (~6) all between 1k to 6k in length (line count) where the same object can be redefined multiple times.
... ALTER VIEW [Reporting].[VW_VIEW_NAME] AS SELECT ... GO ... GO ALTER VIEW [Reporting].[VW_VIEW_NAME] AS SELECT ...
After getting two update packages I can confirm that these long update scripts are not an aggregation of changes that have been applied to the database nor are they a recreation of all currently used tables. Instead, the packages seem to include:
- a mixture of minor DB changes (rename a field in a view from
A.Name AS first_nameto
A.Name as first_name_a);
- some migration scripts (Take data from
tblA.colAand move it to table
- and a lot of redundant/bloat queries (queries that redefine views to what they currently are, queries that redefine a view for it to be again redefined to something else later in a different script that is run in the same update, creation of views or functions that are not used, ...). I also strongly suspect that these scripts don't include all DB changes being applied (I will be able to confirm this a little later when I get the next update package to review).
Note: Changes are done at intervals of ~1 month.
To help reduce the amount of time I'll be spending on digging through their update scripts I've decided to try to find a way to track changes being applied to the structure of the DB. That way I only need to focus on analyzing the migration scripts found within their update package.
From my research, I can tell this is done normally using permissions that I don't have (ie.
You do not have permission to run 'SYS.TRACES').
I have very limited permission (essentially I can read and view definitions to tables and some views/UDP/UDF). Is there a way I can take a snapshot of the DB so that I can compare the structure at a later point in time? I was thinking of creating a query I can run to output a query that would recreate the current DB (only structure and settings, no data).
With this, I can run a text comparison between the two of them to see a summarization of the actual changes that have been applied since the last time I ran the script.
How would you go about being able to keep track of changes being applied to the structure of a database (columns in tables or views (their types, names, ...), indexes being maintained, differences in UDF and UDP, constraints...)?
Nice to have
This is not part of the question, but if possible please take into consideration that the DB has >150 tables (~2000 total unused columns) that are just empty (this does not include the plethora of unused columns in actually used tables).
Ratio: ~1 used column for every 25 unused/empty columns.