I am trying to perform automation of my build for CI and allow automatic deployment( I have access to SQL CMD exe on build machine). The developers are writing out views, stored procedures and related functions without properly modifying all the usages.
I want to know how to forcefully re-validate all entities and I am after a query that can be automated.(right click generate script and re-run is not an option but the equivalent command line options are welcome).
e.g. Say I have function called dbo.findSum and lets assume 2 stored procedures Proc1 and Proc2 are using them.
The developer can choose to create alter scripts for the function dbo.findSum and procedure Proc1 .
Clearly Dev missed out Proc2 change script to be committed to source control. I dont want this silly error to create a runtime surprise when code moves to evaluate Proc2.
I was thinking sp_recompile would help but in reality it will only marked for compilation when it runs next time. I could not find a way to re validate whole schema entities for accuracy.
Any pointers to re-validate whole entities is well appreciated.I need steps that i can automate.
Is there a specific query that can create entire db entity scripts and re-run them in specific order that will force the entities to rebuild and throw error if found inconsistent ?
Is there Script Wizard equivalent command line tool that i can use to generate scripts if my approach is right.
