Execute SQL on Multiple Tables/Columns - New Feature in SQL Server 2000 - Examples
(Page 4 of 6 )
1. This example uses NORTHWIND database.
This example updates data on all tables where the column name contains “Name” in their names. The data being changed here is “Ltd.” to “LIMITED” by searching all the tables plus only those columns that contains word “NAME” in their names.
2. This example uses NORTHWIND database.
This example checks the existence of “QUICK-stop” in all the tables whose column names contain “NAME”. This is an example of executing a set of SQL statements. A temporary table is created before Executing SP_execSQLonDB. For SQL input parameters, a set of SQL statements are supplied, which simply checks row count for the “QUICK-Stop” string. If the count is more than one, it then inserts the found Table Name, Column Name and Row Count into the temporary table ##TMP1.
3. Example to supply a Stored Procedure as an SQL Statement for SP_execSQLonDB.
This example executes the USP_DeptStats stored procedure on all the tables that start with “Employee” plus columns that start with “DEPT”. The @TABLENAME, @COLUMNNAME in the SQL parameters correspond to running Table Name and Column Name respectively as per the result set of the Cursor declaration.
4. Example to supply multiple Table and Column names to @TABLENAME, @COLUMNNAME input parameters.
This will execute the “USP_EMPLOYEE_PF” procedure on all the tables whose name start with either “Employee” or “PF”, on all the columns whose names are either “SALARY” or contain the word “AMOUNT”.
Next: Expansion >>
More SQL Server Articles
More By Raghavendra Narayana
/ 43 
