| Downloads |
|---|
| 101939.zip |
When stored procedures, user-defined functions (UDFs), views, Data Manipulation Language (DML) triggers, database-level Data Definition Language (DDL) triggers, and server-level DDL triggers are schema-bound, you can't modify their referenced objects. However, that's not the case for non-schema-bound objects. You can modify their referenced objects, which can cause the non-schema-bound objects to fail when executed. Failures can occur for many reasons, such as references to invalid column names, invalid object names, and invalid linked server names.
You might think you have schema-bound objects, but in reality you might not. For example, one way to create a schema-bound dependency is to use T-SQL's SCHEMABINDING clause when you create a view or UDF. However, this clause works only when you use a two-part name (schema.object) for the referenced object. If you use a three-part name (database.schema.object) or four-part name (linked_server.database.schema.object) for the referenced object, the view or UDF will be non-schema-bound.
All schema-bound objects are bindable. Here's what that means: When you execute T-SQL code, the Query Optimizer performs the following tasks: compiling (parsing and binding), optimizing (generating an efficient execution plan), and executing. The parsing process checks for T-SQL language syntax errors in the T-SQL code being submitted to the query optimizer, without checking to see whether the objects exist. The binding process binds the objects' names extracted from the parsing process to the actual objects in the database to make sure the objects actually exist in the database. Binding only occurs for DML statements or DDL statements that contain DML statements (e.g., statements that create a view). For some objects, binding can be deferred until runtime, as in the case of a stored procedure that references an object that doesn't exist. If a stored procedure references an existing table, then binding validation isn't deferred.
Non-schema-bound objects might be bindable, depending on the presence of an invalid object reference. So, to find non-schema-bound objects that must be verified, you can query the sys.sql_modules system view, where the is_schema_bound field equals 0.
I developed a SQL CLR stored procedure named BrokenObjects to identify unbindable,or broken, objects. (I couldn't develop a SQL CLR function because including SET OPTION in a function generates the error Invalid use of side-effecting or time-dependent operator in 'SET OPTION ON' within a function.) The stored procedure uses SET NOEXEC ON. When you use this statement, DDL and DML statements are compiled but not executed. Thus, you can reissue a CREATE statement for an object even though that object already exists in the database and you won't receive an object already exists error. You will, however, find out whether there's a compile or parse error.
I wanted the BrokenObjects stored procedure to be able to look for broken objects in a particular database rather than in all the databases on a SQL Server instance, so I decided to pass in a database name as an input parameter. To change the database context, I had to use the command
USE database_name
where database_name is the name of the database to check. When you run a USE command after a SET NOEXEC ON statement, the T-SQL scripting engine won't change the database context. It will only parse the USE command for validity (i.e., it will check to see whether the database name passed to the USE command exists). So, I had to put the USE command before the SET NOEXEC ON statement. After both the USE and SET NOEXEC ON statements run, the stored procedure uses a T-SQL query to find the broken objects, then compiles those objects' DDL (data definition language) statements.
To build and deploy BrokenObjects, you need Microsoft Visual Studio 2005 or later. You can't create, build, or deploy SQL CLR projects with earlier versions of Visual Studio because SQL CLR requires Microsoft .NET Framework 2.0 or later, which is in Visual Studio 2005 and later. If you're using Visual Studio 2008, you'll see the .NET Framework version you're building the SQL CLR for in the top right corner of the New Project dialog box, as shown in Figure 1. Because my CLR stored procedure doesn't reference any .NET 3.0 or .NET 3.5 Framework specific classes, I used .NET Framework 2.0 so that you can you deploy BrokenObjects on SQL Server 2008 and SQL Server 2005.
Figure 1: Creating the BrokenObjects project |
 |
To build and deploy the BrokenObjects stored procedure, follow these steps:
- Download the CLR stored procedure's source code (BrokenObjects.cs) by clicking the 101939.zip hotlink at the top of the page.
- Using SQL Server Management Studio (SSMS), connect to a SQL database server and create a database named Common_Assembly by running the command
CREATE DATABASE Common_Assembly
This is where you'll be deploying the CLR stored procedure to. - Open Visual Studio. On the File menu, select New, then Project Solution. In the New Project dialog box that Figure 1 shows, navigate to Visual C#, Database, SQL Server Project in the Project types tree. In the Name text box, enter BrokenObjects. The Solution Name field will automatically populate as you enter the project name. Change the Location field to C:\BrokenObjects. Click OK.
- When the Add Database Reference dialog box appears, click Add a New Reference. In the New Database Reference dialog box, as shown in Figure 2, specify the Server Name and Common_Assembly as the database.
- Create a strong name key file for signing the assembly. On the Project menu, select BrokenObjects Properties. When the Project Designer appears, click the Signing tab. After making sure the Sign the assembly check box is selected, choose New from the drop-down menu. In the Create Strong Name Key dialog box, enter BrokenObjects for the filename, clear the Protect my key file with password check box, and click OK.
- Add the stored procedure. On the Project menu, select Add Stored Procedure. Set the name to BrokenObjects.cs. Enter the source code for BrokenObjects.cs.
- On the Project menu, select Build Solution.
- On the Project menu, select Deploy Solution to install the assembly and create the stored procedure.
Figure 2: Adding Database Reference |
 |
With the compiled DLL file, you can now deploy the SQL CLR procedure to other servers. You can even deploy it to servers that don't have Visual Studio by using the compiled .NET assembly (which is located in C:\BrokenObjects\BrokenObjects\BrokenObjects\bin\Debug\ BrokenObjects.dll) and the Install_Assembly.sql and Uninstall_Assembly.sql scripts (which you'll find in the 101939.zip file).
After BrokenObjects is deployed, you can use Test.sql in the 101939.zip file to test the installed assembly. This script creates a schema-bound view and a referenced table in the Common_Assembly database, then tries to change the name of one of the table's columns. Next, the script creates a non-schema-bound view and a referenced table, then tries to change the name of one of the table's columns. With the stage set, the script then runs the BrokenObjects stored procedure. Figure 3 shows the results.
Figure 3: Sample results from BrokenObjects |
 |
To run the BrokenObjects stored procedure against one of your databases, follow the syntax
Common_Assembly.dbo.sp_broken_objects 'DB', 1
where DB is the name of the database you want to check for unparsable objects. The last parameter, if set to 1, will call the SqlPipe.Send(string) .NET method. If it's set to 0, the method isn't called. This method is used to display messages similar to that of a PRINT statement. The message will specify which object it's attempting to parse, as Figure 4 shows.
Figure 4: Sample output when you use SqlPipe.Send |
 |
Note that the BrokenObjects stored procedure's results are dependent on the behavior of SET NOEXEC ON. There might be situations in which SET NOEXEC ON doesn't find every binding error, as Scenario 3 in Test.sql demonstrates. The stored procedure will be unable to report any binding errors of an object if that object's name resolution has been deferred, which is the case in Scenario 3. When deferred name resolution occurs, the binding validation is also deferred until the object's execution. (For more information, see the Deferred Name Resolution and Compilation web page.)
Also note that I haven't tested the stored procedure with all the various types of UDFs. Once again, the results will depend on the behavior of the SET NOEXEC ON being performed on the object's DDL statement.
