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 ;)

About these ads

4 Responses to Checking MS SQL Server Objects

  1. sahridhayan says:

    it is great this tool, any possibility i can get the source of this, just for exploring and customizing further will share with you once done… so.
    (this exploration is part of my own study not any commercial involved in my experiment)

    thank you

  2. [...] 3 years ago I described how to store application constants in SQL Server views so that T-SQL code can be checked against these constants be referring view [...]

  3. [...] feature of the FMTONLY mode is how IF statements are processed: BOTH the IF and the ELSE branch are analyzed independent of the [...]

  4. […] other day I was playing around with two of my applications, checktsql and SMOscript, as I was considering to include the functionality of checktsql into […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 65 other followers

%d bloggers like this: