Moer and Éric Moreau's web site

(Print this page)

Validating database objects

Published date: Monday, February 1, 2010

On: Moer and Éric Moreau's web site

Original URL: http://www.emoreau.com/Entries/Articles/2010/02/Validating-database-objects.aspx

This month article solves another problem I faced last week. I was working on an application and all of a sudden, it just stopped working. Being the first in the office after a long weekend, I couldn’t ask teammates who did what to have them where the famous hat. I finally found that somebody changed the name of a database column (fixed a typo in the name) and didn’t check the dependencies (objects like Functions, Stored Procedures, Views) to see which one would be affected and save the new table structure. The net results was that some Stored Procedures where not compiling/running anymore.

At this point I had serious doubts that many other Stored Procedures, Views, and/or Functions would suffer the very same problem.

Then I started looking for a magic statement in SSMS (SQL Server Management Studio), or a magic DBCC statement, or just about anything that would let me check the integrity of my many hundreds of database objects to quickly discover which one would have been affected by table modifications. I haven’t found any.

I can hear you scream to use SP_Recompile. This statement does not recompile the Stored Procedures, it only flags it to be recompiled before being used the next time (instead of using the cache). And that statement wouldn’t work for Functions and Views.

My search also led me to “Set NoExec On” but I couldn’t get good results trying to validate all my objects in a loop.

So I forgot the T-SQL pure solution and turn back to Visual Studio. I am a programmer after all!

Requirements

The demo has been created with Visual Studio 2008 SP1 but could work with any other version as well.

You will also need a running instance of Microsoft SQL Server. The demo has been created for SQL Server 2008 but work as is on SQL 2005. Minor changes would be required to run on older versions.

Downloadable code

It’s finally back! I have both VB and C# versions of this month demo application.

The sample application is made of a single form. It shows a textbox to enter the connection string, a Refresh button to launch the validation, a checkbox to control the level of details displayed (valid objects won’t show when unchecked), and a listbox to display the results. The running application is shown in figure 1.

Figure 1: The demo application in action

Querying objects

The first thing you need to do after having created your connection to the database, is to retrieve the list of database objects you want to validate. The easiest way is to query the sysobjects table from the database itself because it has a pointer to all the objects we will want to validate.

For example, here we query the database to provide a list of all Stored Procedures (P), Views (V), User-Defined Functions (FN and IF) with this query (you will find this query into the DatabaseIsValid method):

Dim strCommand As String = "SELECT [type], object_schema_name(o.id) AS [schema], name " + _
                           ", OBJECTPROPERTY(id, 'ExecIsQuotedIdentOn') as quoted_ident_on " + _
                           ", OBJECTPROPERTY(id, 'ExecIsAnsiNullsOn') as ansi_nulls_on " + _
                           "from sysobjects o " + _
                           "where type in ('P', 'V', 'FN', 'IF') " + _
                           "order by 1, 2, 3 "

This query runs into a SQLCommand and loads a DataSet.

Retrieving Database objects text

Once we have that list of objects into a DataTable from the previous step, we can start looping through each row.

The next step we need to execute is to extract the text used to create the object. The safest method is to call the T-SQL sp_HelpText statement.

In my application, the GetProcedureText method is doing exactly this task. The method loops through each rows of the filled DataReader and appends that to a StringBuilder instance.

Validating the object

We now have everything we need to validate if our object is still valid.

The hard stuff is done in the TryCompile method. The important thing to know here is that before calling the CREATE xxx T-SQL statement, the “Set NoExec On” T-SQL statement is executed so that the CREATE XXX won’t really do anything on the server except the important thing for us, the compilation validation. If an error occurs in this fake-execution, an exception is triggered by SQL Server and handled by .Net. We can then catch it and report it to the user.

A sample database

For testing purposes, I suggest that you create a small database with one table and a couple of objects. For example I have created a small table like this shown in figure 2.

Figure 2: Creating a test table

If you prefer, here is the script to create this table:

CREATE TABLE [dbo].[Table_1](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[Col1] [varchar](50) NULL,
	[Col2] [datetime] NULL,
	[Col3] [money] NULL
)

Then I have created few objects with those scripts:

CREATE VIEW [dbo].[View_1]
AS
SELECT     id, Col1, Col2, Col3
FROM       dbo.Table_1
ORDER BY id

GO

CREATE PROCEDURE [dbo].[Proc1]
AS
BEGIN
	SET NOCOUNT ON;
	SELECT * FROM dbo.Table_1
END

GO

CREATE PROCEDURE [dbo].[Proc2] 
AS
BEGIN
	SET NOCOUNT ON;
	SELECT id , Col1 , Col2 , Col3 FROM Table_1
END

GO

CREATE FUNCTION [dbo].[fnTest1] ()
RETURNS TABLE 
AS
RETURN 
(
	SELECT * FROM Table_1
)

GO

CREATE FUNCTION [dbo].[fnTest2]() 
RETURNS TABLE 
AS
RETURN 
(
	SELECT id , Col1 , Col2 , Col3 FROM dbo.Table_1
)

GO

CREATE FUNCTION [dbo].[fnTest3] 
(
	@P1 VARCHAR(50)
)
RETURNS int
AS
BEGIN
	DECLARE @ResultVar int
	SELECT @ResultVar = id from Table_1 WHERE Col1 = @P1
	RETURN @ResultVar
END

GO

There is nothing complex in those scripts. They simply select values from the table.

Now if you run the application, you should get no errors because the table reflects what’s used into the objects.

But be a bad boy (or girl) for 2 minutes and modify your table and change the name of Col1, you can for example rename it for Col12345.

Now try running the application once again. The application will detect every objects referring to Col1 and report them as problematic. You can now easily discover problems with your objects.

A problem remains

But a problem still remains with this method! What if a Stored Procedure builds a string and executes it with SP_Execute? If the dynamic query uses the renamed column, it won’t be found by this method. The reason is quite simple. The string builds correctly, it is only at execution time that the column will used for real.

Helpful tool

Check the Red Gate website as they just released a new free tool. I have been beta testing a tool called SQL Search that they will offer freely (at least this is what we heard). This tool is an add-in to SSMS and helps you find strings into your database objects. It is handy to help discover referenced columns into dynamic queries.

Lately

While putting the final touches to this article, I found a plain T-SQL script that does almost the same thing I just packaged here. This script can be found at http://www.codeproject.com/KB/database/validatingsql.aspx?msg=3334360#xx3334360xx:

DECLARE @Schema NVARCHAR(100),
   @Name NVARCHAR(100),
   @Type NVARCHAR(100),
   @Definition NVARCHAR(MAX),
   @CheckSQL NVARCHAR(MAX)    
        
DECLARE crRoutines CURSOR FOR   
   SELECT  OBJECT_SCHEMA_NAME(sm.OBJECT_ID) AS schema_name,
      OBJECT_NAME(sm.object_id) AS object_name,
      o.type_desc,
      CASE 
         WHEN o.type_desc = 'SQL_STORED_PROCEDURE'
            THEN STUFF(sm.definition, CHARINDEX('CREATE PROC', sm.definition), 11, 'ALTER PROC')
         WHEN o.type_desc lIKE '%FUNCTION%' 
            THEN STUFF(sm.definition, CHARINDEX('CREATE FUNC', sm.definition), 11, 'ALTER FUNC')
         WHEN o.TYPE = 'VIEW' 
            THEN STUFF(sm.definition, CHARINDEX('CREATE VIEW', sm.definition), 11, 'ALTER VIEW')
         WHEN o.TYPE = 'SQL_TRIGGER' 
            THEN STUFF(sm.definition, CHARINDEX('CREATE TRIG', sm.definition), 11, 'ALTER TRIG')
      END        
   FROM    sys.sql_modules AS sm                
   JOIN sys.objects AS o 
   ON sm.object_id = o.OBJECT_ID        
   ORDER BY 1, 2 ;    
      
OPEN crRoutines
FETCH NEXT FROM crRoutines INTO @Schema, @Name, @Type, @Definition

WHILE @@FETCH_STATUS = 0    
BEGIN               
      IF LEN(@Definition) > 0             
      BEGIN                
            BEGIN TRY                                
                  SET PARSEONLY ON ;                    
                  EXEC ( @Definition ) ;                    
                  SET PARSEONLY OFF ;                                    
            END TRY                
            BEGIN CATCH                               
                  PRINT @Type + ': '+ @Schema + '.' + @Name                   
                  PRINT ERROR_MESSAGE()                               
            END CATCH                     
      END                 
      
      FETCH NEXT FROM crRoutines INTO @Schema, @Name, @Type, @Definition    
END

CLOSE crRoutines
DEALLOCATE crRoutines

One problem this script has is that if you rename an object by right-clicking an object and selecting the Rename menu item of the contextual menu, the object seems to be in error until you currently open (Modify) the object and “Alter” it.

This script also suffers the same problem as with the preceding solution: dynamic queries are not executed.

Conclusion

If everybody who modifies was conscious enough to check the dependencies every time that make a modification to a database structure, we wouldn’t require validation code like this. Problems would only be possible if you are using dynamic queries (and not everybody does that). But nobody is perfect! If you have some tasks that are checking the database integrity, this article definitely helps you detect problems earlier.

(Print this page)