Checking MS SQL Server Objects

I have written about a method to check stored procedures in MS SQL Server using T-SQL before. I extended this method to also include views and stored functions, and wrote a small utility called checktsql.

The idea is to retrieve all views, stored procedures and functions from an MS SQL Server database, and execute:

• SELECT TOP(1) * FROM [View]
• EXECUTE [Procedure]
• SELECT [Function]
• SELECT * FROM [TableValuedFunction]

Each statement is executed within a transaction that is rolled back after execution. All expected parameters are set to NULL. All Exceptions are caught and displayed by the application making it easier to find out which objects are out-of-date and need to be fixed.

checktsql supports to modes of operation: plain execution and setting FMTONLY ON. In the latter case, SQL Server checks all conditional branches (i.e. ignores IF/ELSE).

The application opens one tab per connection/check, and displays the result for each object in a list, along with the type of exception and the exception message. The result list can be filtered to the entries containing errors, and be copied to the clipboard using the context menu. (see screenshots)

Some warnings

• Even though a stored procedure may execute correctly in normal usage, passing NULL parameters may break its functionality and generate error messages like

SQL Exception    Cannot insert the value NULL into column ‘ID’, table ‘mydb.dbo.tablefoo’; column does not allow nulls. INSERT fails. INSERT fails. The statement has been terminated. The statement has been terminated.

This is caused by the NULL values being passed to the procedure.

• Long-running stored procedures may time out

• If you have stored procedure that “clean up” tables or reset or destroy data, you should add a dummy parameter if there is none, and distinguish the check mode from real execution by checking the parameter value for NOT NULL.

• Setting FMTONLY will cause exceptions if the stored procedure uses temporary tables

SQL Exception    Invalid object name ‘#TBL’.

Finally, if you get a message stating

SQL Exception    Invalid object name ‘MyOtherTable’.

or

SQL Exception    Could not find stored procedure ‘sp_Foo’.

you know where to start to fix your T-SQL code…

checktsql is available for download here.

This is a real version 1.00, so send your comments if something is missing 😉