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.
Caution
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 ...
Get Inside Microsoft® SQL Server™ 2005 now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
