Uses of Dynamic SQL
Now that the fundamentals of dynamic SQL, EXEC, and sp_executesql have been covered, this section will demonstrate several ways to apply dynamic SQL.
Dynamic Maintenance Activities
One of the main uses of dynamic SQL is to construct code dynamically for automated maintenance activities such as performing index defragmentation, backups, and the like. You need to query metadata and environmental information and use it to construct the code.
Be aware that metadata should be carefully checked for potential SQL Injection attempts (for example, through maliciously named objects).
A classic scenario for automated maintenance code ...