Use SQL Server system functions for code archiving

As a database administrator, I always try to give a complete look at the code submitted to the development environment before I can migrate the code. However, I have to admit that I cannot guarantee that there will be no damage to the development system. When this happens, the possible remedy is to revert to the previous version of the target code, which may be stored procedures, functions, and so on. If possible, what you don't want to do but have to do is recover the code from the backed up database, but if the backed up database is stored on tape, this method may be too time-consuming to use. If the database is huge, it will take a long time to recover, not to mention that you are looking for a server that is large enough to store the backed-up files. However, there are better ways.

One of the solutions I found a long time ago was to back up the database code into a separate datasheet so that if the code we developed had an error, we could recover the process or function of the error from the datasheet. This approach does save a lot of time.

In SQL Server 2000, this can be done by making a complete syscomments backup of the special database and then putting the backed-up datasheet into the file table. I usually save important process code for the last two weeks. The only trouble with using this technology is that if the code object is very large, you might want to refactor the code. Sometimes this can be a headache if the code is stored in a different row in the syscomments table.

One of the new additions to SQL Server 2005 is the ability to use a system function to return the complete code of an object, which makes it easy to archive your process code.

SQL Server 2005 's new system function Object_definition returns the object's TSQL code based on the object ID provided to the function. To better understand the working process of this function, let me give an example. First we create a user-defined function that has the following script:

CREATE FUNCTION udf_Multiply
(@Val1 INT,
@Val2 INT
)
RETURNS INT
AS
BEGIN
DECLARE @RetVal INT
SET @RetVal = (@Val1 * @Val2)
RETURN(@RetVal)
END

This is a very simple little function. Because it only handles two parameters, it's enough to show us how the Object_definition function works. The script for testing the system function is as follows:

DECLARE @ObjectID INT
SET @ObjectID = OBJECT_ID('udf_Multiply')
SELECT OBJECT_DEFINITION(@ObjectID)

In this example, we actually use two system functions. First, we want to get the object_id of the udf_multiply function we created earlier, in the SQL Server database engine, OBJECT_ID is the system identifier for an object. We then pass this ID to the system function object_definition, which returns the code of the ID object provided to it, that is, the return value is the TSQL code we previously wrote for the Udf_multiply function.