Here I will describe two simple routines. In the first, the SQL code creates a system stored procedure named sp_FixTables, which performs a specified operation on a specified set of tables. The second is a system stored procedure named sp_FixObjects, which performs a specified operation on a specified set of objects.
[TABLE]
The sp_FixTables stored procedure performs a specified operation on a specified set of tables. It accepts six parameters and all of them are optional.
The first parameter specifies timing issues, whether T-SQL code is generated or actions are performed immediately. A value of zero returns T-SQL code that 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 (two through five) work together to form a combination of search criteria using object names. Please refer to my previous tip on finding columns and tables for an explanation of how these parameters work.
The last (sixth) parameter specifies which operation will be performed on each object. The string value should consist of T-SQL statements. Caret symbols "^" are replaced by single quotes, making it cleaner when you 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. You can generate T-SQL code or immediately execute commands. You can more easily specify a subset of tables. You can perform character substitutions other than table name.
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 pound sign "#" is replaced by the
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
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).
The sp_FixTables stored procedure can be used in place of hand-coded script for each table or writing SQL code to implement a cursor that loops through the tables. Tasks such as index maintenance can be performed on all tables with examples like these:
EXECUTE sp_FixTables @DBUltra = 1, @PCAdmin = 'DBCC INDEXDEFRAG (0, #)'
EXECUTE sp_FixTables @DBUltra = 1, @PCAdmin = 'DBCC DBREINDEX (^* ^)'
This example checks the customer tables in the Northwind database:
USE Northwind EXECUTE sp_FixTables 1,NULL,NULL,'Customer%',NULL,'DBCC CHECKTABLE (^ * ^)'
[TABLE]
The sp_FixObjects stored procedure performs a specified operation on a specified set of objects. It 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 that 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 (two through five) work together to form a combination of search criteria using object names.
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 pound 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 names:
USE Northwind EXECUTE sp_FixObjects 1,NULL,NULL,'%Sales by Year%',NULL,'EXECUTE sp_help ^ * ^'
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 & [*]'
[TABLE]
Click for the stored procedures: sp_FixTables and sp_FixObjects
About the author: Brian Walker is a senior database architect in an IS department that uses SQL Server 2000 and the .NET Framework. He has more than 25 years of experience in the IT industry with the last several years focused on databases and SQL Server. Walker is a software developer, database developer, database administrator and database consultant. He develops utility software as a hobby, including a large collection of SQL Server utilities.
More information from SearchSQLServer.com
Tip: Stored procedure: Find columns and tables
Learning Center: Stored procedures
Topic: Get best practices for working with stored procedures
