Check for Objects Created Without SET Options Enabled

Downloads
95501.zip

Congratulations to John Costantino, who won first prize of $100 for the best solution to the April Reader Challenge, "Change the Status of Table Items." Here's his solution to the April Reader Challenge. (To read the full Challenge, go to InstantDoc ID 95324.)

Solution:

Siva should first create an index on the OrderStatus column of the Orders table. Creating this index will help him quickly locate orders of a particular status. Additionally, he should include a column such as OrderDate as part of the index to answer more useful questions about date and status of the orders. Siva can create this index by using the following CREATE INDEX statement:

Next, Siva should use the UPDATE statement in Web Listing 1 (http://www.sqlmag.com, InstantDoc ID 95501) to change the status of orders from Processing to Shipped after all of the line items have been processed. The UPDATE statement selects all orders in the Orders table that are in the "Processing" status and ensures that all of the line items for every order are in the "Processed" status. The index on the OrderStatus and OrderDate columns of the Orders table improves the UPDATE statement's performance. To test the UPDATE statement action, Siva can use the code in Web Listing 2.

MAY CHALLENGE:

Test your SQL Server savvy in this month's Reader Challenge. Submit your solution in an email message to challenge@sqlmag.com by May 10. Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We'll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.

Problem:

Richard is a database architect for a company that uses SQL Server as its database. The company's DBAs typically execute database updates by using T-SQL scripts. The front-end application that interacts with SQL Server requires several ANSI settings to be enabled when the DBAs create database objects such as tables, functions, stored procedures, and triggers. The application also uses features such as indexed views and indexes on computed columns. For the application to work correctly, it requires the following ANSI SQL SET options:

1. ANSI_PADDING should be enabled on all character and binary columns in tables.
2. ANSI_NULLS and QUOTED_IDENTIFIER options should be enabled when stored procedures, functions, and triggers are created.

Richard has recently encountered production problems that happen when DBAs execute scripts from a session that don't have the required ANSI SET options enabled. The scripts create objects that have nondefault settings, resulting in application errors and data integrity errors. To prevent future problems, Richard wants to include checks in the application and in the scripts that can identify objects that don't have the necessary SET options enabled. Help Richard do the following:

1. Write code to list the columns that are created with ANSI_PADDING OFF.
2. Write code to list the stored procedures, functions, and triggers that were created or modified with ANSI_NULLS or QUOTED_IDENTIFIER OFF. For each object, the setting that's disabled should be shown.
3. 3Create the checks in a T-SQL batch that can be run at the end of the script or in the application. The checks should also raise an error if any objects were found that meet the criteria.

You can use the script in Web Listing 3 to create in the tempdb database objects that have some of the SET options disabled.