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.