3 added 491 characters in body
source | link

I've modified a central table in my database, and sp_depends literally returns hundreds of results, and I'm concerned some of those stored-procedures might not compile anymore after my change.

Checking one single stored procedure is easy (I just rerun the alter script and see whether the operation is successful), but doing that on 100+ procedures is a bit cumbersome.

I know I can use a script like this one to recompile all the objects of my database, but the actual operation will take place next time the stored procedure is executed, not immediately, so that doesn't seem appropriate in my case.

I was also thinking that I could drop all the stored procedures altogether, and resycnhronize my database with my source control system, but that option, although viable, isn't very elegant. Is there a better way of doing this?

I'm using SQLServer 2008 R2 and my database scripts are stored in a VS 2008 database project.


To clarify, I'm not advocating one should solely rely on this approach to test code. Just exactly like in c# you instantaneously detect syntax error in other dependent files as you code (and then use other strategies to test such as unit tests, which is usually several orders of magnitude slower), I think it would make sense to detect SQL dependencies errors in seconds rather than having to run a full functional test which can typically take a few hours to complete.

I've modified a central table in my database, and sp_depends literally returns hundreds of results, and I'm concerned some of those stored-procedures might not compile anymore after my change.

Checking one single stored procedure is easy (I just rerun the alter script and see whether the operation is successful), but doing that on 100+ procedures is a bit cumbersome.

I know I can use a script like this one to recompile all the objects of my database, but the actual operation will take place next time the stored procedure is executed, not immediately, so that doesn't seem appropriate in my case.

I was also thinking that I could drop all the stored procedures altogether, and resycnhronize my database with my source control system, but that option, although viable, isn't very elegant. Is there a better way of doing this?

I'm using SQLServer 2008 R2 and my database scripts are stored in a VS 2008 database project.

I've modified a central table in my database, and sp_depends literally returns hundreds of results, and I'm concerned some of those stored-procedures might not compile anymore after my change.

Checking one single stored procedure is easy (I just rerun the alter script and see whether the operation is successful), but doing that on 100+ procedures is a bit cumbersome.

I know I can use a script like this one to recompile all the objects of my database, but the actual operation will take place next time the stored procedure is executed, not immediately, so that doesn't seem appropriate in my case.

I was also thinking that I could drop all the stored procedures altogether, and resycnhronize my database with my source control system, but that option, although viable, isn't very elegant. Is there a better way of doing this?

I'm using SQLServer 2008 R2 and my database scripts are stored in a VS 2008 database project.


To clarify, I'm not advocating one should solely rely on this approach to test code. Just exactly like in c# you instantaneously detect syntax error in other dependent files as you code (and then use other strategies to test such as unit tests, which is usually several orders of magnitude slower), I think it would make sense to detect SQL dependencies errors in seconds rather than having to run a full functional test which can typically take a few hours to complete.

    Tweeted twitter.com/#!/StackDBAs/status/189364879705128960
2 deleted 8 characters in body
source | link

I've modified a central table in my database, and sp_depends returns literally returns hundreds of results, and I'm concerned some of those stored-procedures might not compile anymore after my change.

Checking one single stored procedure is easy (I just rerun the alter script and see whether the operation is successful), but doing that on 100+ procedures is a bit cumbersome.

I know I can use a script like this one to recompile all the objects of my database, but the actual operation will take place next time the stored procedure is executed, not immediately, so that doesn't seem appropriate in my case.

I was also thinking that I could drop all the stored procedures altogether, and resycnhronize my database with my source control system, but that option, although viable, isn't very elegant. Is there a better way of doing this?

I'm using SQLServer 2008 R2 and my database scripts are stored in a VS 2008 database project.

I've modified a central table in my database, and sp_depends returns literally returns hundreds of results, and I'm concerned some of those stored-procedures might not compile anymore after my change.

Checking one single stored procedure is easy (I just rerun the alter script and see whether the operation is successful), but doing that on 100+ procedures is a bit cumbersome.

I know I can use a script like this one to recompile all the objects of my database, but the actual operation will take place next time the stored procedure is executed, not immediately, so that doesn't seem appropriate in my case.

I was also thinking that I could drop all the stored procedures altogether, and resycnhronize my database with my source control system, but that option, although viable, isn't very elegant. Is there a better way of doing this?

I'm using SQLServer 2008 R2 and my database scripts are stored in a VS 2008 database project.

I've modified a central table in my database, and sp_depends literally returns hundreds of results, and I'm concerned some of those stored-procedures might not compile anymore after my change.

Checking one single stored procedure is easy (I just rerun the alter script and see whether the operation is successful), but doing that on 100+ procedures is a bit cumbersome.

I know I can use a script like this one to recompile all the objects of my database, but the actual operation will take place next time the stored procedure is executed, not immediately, so that doesn't seem appropriate in my case.

I was also thinking that I could drop all the stored procedures altogether, and resycnhronize my database with my source control system, but that option, although viable, isn't very elegant. Is there a better way of doing this?

I'm using SQLServer 2008 R2 and my database scripts are stored in a VS 2008 database project.

1
source | link

How can I detect broken stored procedures after a schema change?

I've modified a central table in my database, and sp_depends returns literally returns hundreds of results, and I'm concerned some of those stored-procedures might not compile anymore after my change.

Checking one single stored procedure is easy (I just rerun the alter script and see whether the operation is successful), but doing that on 100+ procedures is a bit cumbersome.

I know I can use a script like this one to recompile all the objects of my database, but the actual operation will take place next time the stored procedure is executed, not immediately, so that doesn't seem appropriate in my case.

I was also thinking that I could drop all the stored procedures altogether, and resycnhronize my database with my source control system, but that option, although viable, isn't very elegant. Is there a better way of doing this?

I'm using SQLServer 2008 R2 and my database scripts are stored in a VS 2008 database project.