Problem solve Get help with specific problems with your technologies, process and projects.

Stored procedures: Perform operations on SQL Server tables and objects

To perform a particular operation on a specified set of tables or objects, contributor Brian Walker offers two simple and flexible stored procedures: sp_FixTables and sp_FixObjects. These routines allow you to generate T-SQL code, immediately execute commands, more easily specify a subsets and perform character substitutions.

  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 OF CONTENTS
   sp_FixTables parameters
   sp_FixObjects parameters
   Stored procedures

 
sp_FixTables parameters

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 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 (^ * ^)'

 
sp_FixObjects parameters

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 & [*]'

 
sp_FixTables and sp_FixObjects routines

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

 

This was last published in August 2005

Dig Deeper on SQL-Transact SQL (T-SQL)

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close