SQL Prompt - find invalid objects

Показать описание

In this tip Data Platform MVP Ike Ellis explains how to use SQL Prompt to find invalid objects, so you can catch problems earlier in development and resolve issues quickly.

Transcript:
Hi I'm Ike Ellis and I’m going to show you a cool feature of SQL Prompt. One of the issues with T-SQL is that doesn't really tell you when you have dependencies with your objects by default. So if I create a table right here like the customers table and then I create a stored procedure that's dependent on the customers table and then later on at some point I delete the customers table like I drop table customers, there's no warning here telling me that I've got a procedure using that table.

Software development teams have struggled with this for a very long time. Often what they'll do is at one step in the release pipeline they'll drop and recreate all the stored procedures, or all their views or all their functions and then it will warn them to tell them that they have a dependency in the drop and recreation.

But the problem with that is you have to remember to do it - you have to do it manually or automatically - and you actually have to do it at the last step of your release pipeline, well after you've made any schema changes or else you really didn't do anything useful. But one thing you can do with Redgate SQL Prompt is you can right-click on your database and you can say ‘find invalid objects’ and it will warn you, it will say “hey this store procedure is dependent on a table that's no longer here”.

And if you get used to doing this you can do it way earlier in the development process. You can do it every day or at the end of every day or before you check in a source control, and then you can catch your problem before any other developer on your team finds the problem. We know that when a developer finds his own problem or her own problem that it's far cheaper, way easier to resolve, all good things.

So thanks, hope you enjoyed this tip and you find out for yourself by downloading the latest SQL Prompt. Thanks have a great day.