Dynamic SQL usage

One of the usefulness about dynamic SQL is for database maintenance. For example, you want to run the maintenance process in the selected databases.

Below is the flow of using dynamic SQL to ease the maintenance:

• Create a table that stores the database names for your system.
• Declare a cursor and loop through all your database names and match the process number (explain later).
• Generate the dynamic SQL statement to be executed against the selected databases.
• Finally, execute the dynamic SQL.

The reason to have a table to stores your database names:

• You are not interested in all databases stored in sys.databases. You just want to run the script against the selected databases.
• The database name did not follow any convention before you take over the maintenance. In you are handling the database server, then, you should follow a standard naming convention for the database name. This allows you to query the database name from sys.databases.
• You don't want to execute the SQL statement against all your application databases. Only certain databases are going to run script 1 and other databases are going to run script 2, etc.

The table design to stores the database name:

• db_name nvarchar(255) - this is the database name
• process_1 int - if '1' means run the maintenance stored procedure #1. '0' to ignore.
• process_2 int - if '1' means run the maintenance stored procedure #2.'0' to ignore.
• (other process flag).