Stored procedures: Perform operations on SQL Server tables and objects

Stored procedures: Perform operations on SQL Server tables and objects

 
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

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchSQLServer.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchSQLServer.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

Premium Access

Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
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

This was first published in August 2005


   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

 

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

    Back to top