This tip continues the system stored procedure series with two simple routines.

The SQL code in Listing 1 creates a system stored procedure named sp_FixTables.  The routine performs a specified operation on a specified set of tables.

The SQL code in Listing 2 creates a system stored procedure named sp_FixObjects.  The routine performs a specified operation on a specified set of objects.

The sp_FixTables stored procedure accepts six parameters and all of them are optional.

The first parameter specifies whether T-SQL code is generated or actions are performed immediately.  A value of zero returns T-SQL code which can be saved and/or executed as a separate task.  A value of one causes the specified action(s) to be performed on the selected objects immediately.  The default value is zero.

The next four parameters work together to form a combination of search criteria using object names.  Please refer to my tip dated July 21 for an explanation of how these parameters work.

The last (sixth) parameter specifies the operation to be performed on each object.  The string value should consist of T-SQL statements.  Caret symbols "^" are replaced by single quotes, which makes it cleaner to include embedded single quotes in the T-SQL statements.  The default operation is to UPDATE STATISTICS and EXECUTE sp_recompile.

The sp_FixTables stored procedure is similar to an undocumented stored procedure that comes with SQL Server (sp_MSforeachtable), but this routine has some additional flexibility.

There are repetitive character substitutions made within the string of T-SQL statements to be executed for each object.  An asterisk "*" is replaced by the current object name.  A number sign "#" is replaced by the current object ID.  A dollar sign "$" is replaced by the object owner name.  An ampersand "&" is replaced by the current table name without the prefix (if table name prefixes are used).

This example checks the customer tables in the Northwind database:

USE Northwind EXECUTE sp_FixTables 1,NULL,NULL,'Customer%',NULL,'DBCC CHECKTABLE (^*^)'

The sp_FixObjects stored procedure accepts six parameters and all of them are optional.

The first parameter specifies whether T-SQL code is generated or actions are performed immediately.  A value of zero returns T-SQL code which can be saved and/or executed as a separate task.  A value of one causes the specified action(s) to be performed on the selected objects immediately.  The default value is zero.

The next four parameters work together to form a combination of search criteria using object names.  Please refer to my tip dated July 21 for an explanation of how these parameters work.

The last (sixth) parameter specifies the operation to be performed on each object.  The string value should consist of T-SQL statements.  Caret symbols "^" are replaced by single quotes, which makes it cleaner to include embedded single quotes in the T-SQL statements.  The default operation is to change the object owner to dbo.

The sp_FixObjects stored procedure is much like the sp_FixTables stored procedure, but this routine has slightly different features while the other routine is specifically designed for working on tables.

There are repetitive character substitutions made within the string of T-SQL statements to be executed for each object.  An asterisk "*" is replaced by the current object name.  A number sign "#" is replaced by the current object ID.  A dollar sign "$" is replaced by the object owner name.  An ampersand "&" is replaced by the object type (TABLE, VIEW, PROCEDURE, FUNCTION).

This example returns information about all objects in the Northwind database that contain "Sales by Year" in their name:

USE Northwind EXECUTE sp_FixObjects 1,NULL,NULL,'%Sales by Year%',NULL,'EXECUTE sp_help ^*^'

Sometimes it may be more convenient to provide the sixth parameter by name and omit all the others when calling these stored procedures.

This example generates T-SQL code to drop all the objects in the Northwind database:

USE Northwind EXECUTE sp_FixObjects @PCAdmin = 'DROP & [*]'

I hope you find these two system stored procedures to be useful.