CLR Stored Procedure Finds Broken Objects

To build and deploy the BrokenObjects stored procedure, follow these steps:

1. Download the CLR stored procedure's source code (BrokenObjects.cs) by clicking the 101939.zip hotlink at the top of the page.
2. 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.
3. 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.
4. 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.
5. 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.
6. 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.
7. On the Project menu, select Build Solution.
8. 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.