automatic test if stored procedures still working + SQL SERVER
I was wondering how can I test existing stored procedures if they are still up to date. I mean let's create table in SQL Server and then prepare a stored procedure witch inserting some data to this table and select something from another one.
For some time (few months) our table might be changed. And our stored procedure might be not up to date (it just may cause any problems). In case when it is the only one procedure it is not a problem. We can check procedure manually (just try to run it once again and see if there are any errors) and fix it if it is necessary. But what in case if we have a lot of procedures and we even don't remeber which ones was connected with table which was changed.
Is there any way of automatic run all procedures (I don't want to pass any parameters I would only like to test if the procedure can compile without errors)?
@Md. Elias Hossain -> This query will return procedure name and all parameters for this procedure for the given proceddure. But I said the situation looks like: I have a lot of stored procedures and I have some structure of tables. Now I need to change some tables (I don't know maybe - add some columns or rename column's name) And I DON'T need to change any procedure! But when I run my application (after some changes in the structure of tables) which uses stored procedures, it will failed (as some procedures are not correct now - i.e. some fields might not exists any more).
When I create new procedure, if it is ready I click Execute and it will run and save within all procedures. If something was wrong with it, I will get an error message and procedure won't be saved. Now I would like to do something very similar. I would like to Execute all procedures (after any changes in tables structure) and check if they are still correct (correct in structural meanings). So I don't need to know any parameters for structures. Next I can do it one by one, but I think it is not a good way to do it. So I asked if anyone know any good way to Execute all procedures (I don't want to insert any data to db or delete or select anything. The only one thing I would like to do is checking if all these procedures are still correct in structural meaning)
If you just want to make sure they're still valid, there's a feature in SQL Prompt called 'Find Invalid Objects' which should work for you.
There is a screenshot of how this works here: http://www.red-gate.com/products/sql-development/sql-prompt/screenshots
If you want to do it 'properly' and make sure they not only compile but behave as expected, you will need to take Klas's advice and implement unit testing. The company I work for just released a preview UI tester runner, SQL Test, that builds on the open source framework, tSQLt.
For more information, visit:http://www.sql-test.com