September 5, 2018 at 1:20 am
Hi,
Iimagine if you will you have a database with over 500 stored procedures, views and functions and your testing an upgrade from the third party who developed it.
The problem is you know that the changes they made will have made some of the stored procedures, views and functions unusable, how?
Llike this (this is in the sample I attached as well).
In version 1.0 - they create a table called dbo.SampleTable, you then use that table in a procedure called dbo.SampleProc, this all works fine.
But then in version 1.1 they go and drop the table dbo.SampleTable, your procedure will still be there but if you try and run it then it will error as the table it needs is not there.
The question is does anyone know a way to have to have SQL tell me all the objects are unusable? I could just go and execute every procedure and see what fails in a test environment but there are 500 of them, all with different parameters and outputs etc so I think this would take an age to do.
I've attached a sample file which demonstrates the above scenario.
Any ideas would be appreciated.
Thanks,
Nic
September 5, 2018 at 2:00 am
You can query the sys.sql_expression_dependencies to find that information. Here's documentation on that. I would also suggest you look into setting up a continuous integration process for your database development. You can create basic unit tests to ensure that stuff like that doesn't happen.
You might also want to look to SQL Dependency Tracker from Redgate Software. It does a more thorough job.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 7, 2018 at 4:44 am
Thanks for the response Grant.
couldn't agree more on the unit tests, sadly this is a 3rd party owned database and they do the upgrades. I guess I'll have to see if I can put a bit of pressure on them to do these tests before.
Thanks,
Nic
September 14, 2018 at 10:08 am
Well, you could also load their database into a DB source control system like Redgate's SQL Source Control or MS Visual Studio SSDT. Maintain this as a project, and then you can import the changes when new versions come out. Either system will alert you to potential validation errors. This is the first step toward creating a CI process. You even could create your own mini-CI process just for this 3rd party software by importing their changes into your source control system and then deploying the changes yourself - after making updates to ensure that your own code will not break.
Just a thought! 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply