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