Home

	Expand Search

Transact-SQL User's Guide

Chapter 1 SQL Building Blocks

SQL in Adaptive Server

Compiled objects

Compiled objects

Adaptive Server uses compiled objects to contain vital information about each database and to help you access and manipulate data. A compiled object is any object that requires entries in the sysprocedures table, including:

• Check constraints

• Defaults

• Rules

• Stored procedures

• Extended stored procedures

• Triggers

• Views

Compiled objects are created from source text, which are SQL statements that describe and define the compiled object. When a compiled object is created, Adaptive Server:

1. Parses the source text, catching any syntactic errors, to generate a parsed tree.

2. Normalizes the parsed tree to create a normalized tree, which represents the user statements in a binary tree format. This is the compiled object.

3. Stores the compiled object in the sysprocedures table.

4. Stores the source text in the syscomments table.

   If you are upgrading from Adaptive Server version 11.5 or earlier, and you have deleted source text from syscomments, you must restore the deleted portions before you upgrade. To do so, follow the process in the installation documentation for your platform.

In Adaptive Server version 11.5 and in SQL ServerŪ versions prior to that release, the source text was saved in syscomments so it could be returned to a user who executed sp_helptext. Because this was the only purpose of saving the text, users often deleted the source text from the syscomments table to save disk space and to remove confidential information from this public area. However, you should not delete the source text because this may cause a problem for future upgrades of Adaptive Server. If you have removed the source text from syscomments, use the procedures in this section to restore the source text.

If a compiled object does not have matching source text in the syscomments table, you can restore the source text to syscomments using any of the following methods:

• Load the source text from a backup.

• Re-create the source text manually.

• Reinstall the application that created the compiled object.

Adaptive Server versions 11.5 and later allow you to verify the existence of source text, and encrypt the text if you choose. Use these commands when you are working with source text:

• sp_checkresource - verifies that source text is present in syscomments for each compiled object.

• sp_hidetext - encrypts the source text of a compiled object in the syscomments table to prevent casual viewing.

• sp_helptext - displays the source text if it is present in syscomments, or notifies you of missing source text.

• dbcc checkcatalog - notifies you of missing source text.