1

Background

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_name to A.Name as first_name_a);
  • some migration scripts (Take data from tblA.colA and move it to table tableB.colB);
  • 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').

Question

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.

7
  • seems like the perfect fit to ask for a DBCC CLONEDATABASE or get a signed stored procedure to make one on demand for you Commented Nov 20, 2023 at 16:44
  • @SeanGallardy after reading a little into DBCC CLONEDATABASE I believe that it would require "membership in the sysadmin fixed server role.". Is there a way to circumvent this if I were to try to get the cloned database created on a SQL server instance that I create locally on my machine (source no permission, destination I have permission) or just for it to output the script it would use to a "*.sql" file instead?
    – Tolure
    Commented Nov 20, 2023 at 16:53
  • 2
    That's why the comment said signed stored procedure so that you don't need sysadmin Commented Nov 20, 2023 at 18:59
  • If you can convince the powers at be to add you to the db_backupoperator role, then you can take full backups at the interval most conducive to your liking. This role literally only permits you to take backups of only that database. Then you can restore them on a local instance where you'd have full control and use a comparison tool to compare the full schema changes between the live database and your backup to get a single set of changes. You can even have the tool auto-generate said changes as scripts, if needed. It's a little bit of a process but you won't be flooded with scripts then.
    – J.D.
    Commented Nov 20, 2023 at 22:47
  • @J.D. I do not think I'd be able to. I've been fighting for a few weeks just to get the permissions required to execute all functions contained within a standard view (a view we use in prod). I can execute the view but not all functions contained within.
    – Tolure
    Commented Nov 21, 2023 at 16:19

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.