利用SQL Server系统函数进行代码存档

核心提示： 这是一个很简单的小函数，因为它仅仅处理两个参数，利用SQL Server系统函数进行代码存档(2)，但是已足够为我们演示OBJECT_DEFINITION函数是如何工作，测试该系统函数的脚本如下：DECLARE @ObjectID INTSET @ObjectID = OBJECT_ID(

这是一个很简单的小函数。因为它仅仅处理两个参数，但是已足够为我们演示OBJECT_DEFINITION函数是如何工作。测试该系统函数的脚本如下：

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

在这个例子中，我们实际上用了两个系统函数。首先，我们要得到前面创建的udf_Multiply函数的OBJECT_ID，在SQL Server 数据库引擎中，OBJECT_ID是一个对象的系统标识符。然后我们将这个ID传给系统函数OBJECT_DEFINITION，这一系统函数将返回提供给它的ID对象的代码，即返回值是我们以前为udf_Multiply 函数写的TSQL代码。

既然我们对OBJECT_DEFINITION函数的工作原理有了很好的了解，接下来让我们看看如何利用这个函数来存档我们数据库中的过程代码。首先，运行下面的脚本程序在测试数据库中创建20个存储过程。

DECLARE @i INT
SET @i = 1
WHILE @i <= 20
BEGIN　　　EXECUTE　　　
(　　　'IF OBJECT_ID(''usp_TestProcedure'+@i + ''')>0　　　　　　
DROP PROCEDURE usp_TestProcedure'+@i+'　　　'　　　)　　　
EXECUTE　　　(　　　'　　　
CREATE PROCEDURE usp_TestProcedure' + @i + '　　　
AS　　　
BEGIN　　　　　　　　　　　
PRINT ''The name of this procedure is ''
+ CAST(OBJECT_NAME(@@PROCID)
AS VARCHAR(20))　　　 END'　　　)　　　
SET @i = @i + 1
END

你将看到在上面的脚本中，我们使用了动态SQL语句。当创建动态SQl语句时，我习惯用系统存储过程sp_executesql，因为该过程能够很好地在系统中缓存SQL语句。但是，在我们这一例子中，EXECUTE命令就能很好地完成任务。

现在我们的数据库中已经有了一些对象，我们可以创建用来存档数据库中存储过程需要的对象和代码。下面的脚本可以为我们完成这项工作。

IF OBJECT_ID('CodeArchive','U')>0　　　
DROP TABLE CodeArchive
CREATE TABLE CodeArchive
(　　　ArchiveID INT IDENTITY(1,1) PRIMARY KEY,　　　
ObjectName SYSNAME,　　　
ObjectDescription VARCHAR(60),　　　
ObjectType CHAR(2),　　　
ObjectDefinition VARCHAR(MAX),　　　
ObjectID INT,　　　
CreationDate DATETIME,　　　
ModifiedDate DATETIME,　　　
EntryDate DATETIME DEFAULT(GETDATE())
)
INSERT INTO CodeArchive
(　　　ObjectName, ObjectDescription,
ObjectType, ObjectDefinition,　　
ObjectID, CreationDate, ModifiedDate
)
SELECT　　　 so.name, so.type_desc, so.type,
OBJECT_DEFINITION(object_id),　　　
so.object_id, so.create_date, so.modify_date　　
FROM　　　sys.objects so
WHERE　　　
so.[type] IN('C', 'D', 'P', 'FN', 'R', 'RF', 'TR', 'IF', 'TF', 'V')