如何获得创建存储过程,用户定义函数和触发器的正确​​顺序

我读过在SQL Server 2008中改进了对象依赖性。 我有一个相当复杂的数据库模式,包含存储过程,用户定义的函数,触发器。 任何人都可以给我一个查询,根据它们的依赖关系返回正确的创建这些项目的顺序吗? 我在这里读到有些工具可以完成这项工作,但我正在寻找可编写脚本的东西。此外,他们经常给出一个对象的依赖关系,我想要一个数据库范围的解决方案。 谢谢。     
已邀请:
Redgate的sql compare pro可以通过命令行执行。我有GUI版本,它总是正确的。通过查看此工具的输出,我甚至学到了一些东西! 您也可以使用SMO脚本来完成它,但我相信您需要为自己找出依赖关系和正确的操作顺序。     
好吧,我根据那篇文章做了些什么 我修改了sp_FindDependencies以包含依赖关系树中的级别:
 CREATE PROCEDURE sp_FindDependencies
 (
         @ObjectName SYSNAME,
         @ObjectType VARCHAR(5) = NULL
 )
 AS
 BEGIN
     DECLARE @ObjectID AS BIGINT    

         SELECT TOP(1) @ObjectID = object_id
         FROM sys.objects
         WHERE name = @ObjectName
         AND type = ISNULL(@ObjectType, type)    

     SET NOCOUNT ON ;    

       WITH DependentObjectCTE (DependentObjectID, DependentObjectName, ReferencedObjectName, ReferencedObjectID, Level)
         AS
         (
         SELECT DISTINCT
                sd.object_id,
                OBJECT_NAME(sd.object_id),
                ReferencedObject = OBJECT_NAME(sd.referenced_major_id),
                ReferencedObjectID = sd.referenced_major_id,
                1 AS Level
         FROM    
                sys.sql_dependencies sd
                JOIN sys.objects so ON sd.referenced_major_id = so.object_id
         WHERE   
                sd.referenced_major_id = @ObjectID
         UNION ALL
         SELECT
                sd.object_id,
                OBJECT_NAME(sd.object_id),
                OBJECT_NAME(referenced_major_id),
                object_id,
                Level + 1
         FROM    
                sys.sql_dependencies sd
             JOIN DependentObjectCTE do ON sd.referenced_major_id = do.DependentObjectID       
         WHERE
                sd.referenced_major_id <> sd.object_id     
         )
         SELECT DISTINCT
                DependentObjectName, Level
         FROM   
                DependentObjectCTE c
 END
GO
然后我走过我想要追踪的所有物体。在我的系统中,我在表SpVersion中维护这些对象的版本。
DECLARE @err int;
DECLARE @level int;
DECLARE @name varchar(50);
DECLARE @name2 varchar(50);

DECLARE cur CURSOR LOCAL FAST_FORWARD FOR 
SELECT SP.name AS obj_name
FROM sys.procedures SP, SpVersion SPV WHERE
SP.name = SPV.SpName
UNION
SELECT SO.name AS obj_name
FROM sys.objects SO, SpVersion SPV 
WHERE type_desc LIKE '%FUNCTION%' AND SO.name=SPV.SpName
UNION
SELECT ST.name AS obj_name
FROM sys.triggers ST, SpVersion SPV 
WHERE ST.name=SPV.SpName;

CREATE TABLE #T1 (procname varchar(50) COLLATE DATABASE_DEFAULT, Level int);
CREATE TABLE #T2 (procname varchar(50) COLLATE DATABASE_DEFAULT, Level int);

OPEN cur
SELECT @err = @@error IF @err <> 0 RETURN  

WHILE 1=1 BEGIN
    FETCH NEXT FROM cur INTO @name
    SELECT @err = @@error IF @err <> 0 RETURN 
    IF @@FETCH_STATUS <> 0 BREAK

    DELETE #T1;
    INSERT #T1 (procname, level) VALUES (@name, 0);
    INSERT #T1 (procname, level) EXEC sp_FindDependencies @name;

    DECLARE cur2 CURSOR LOCAL FAST_FORWARD FOR 
    SELECT procname, level FROM #T1;

    OPEN cur2
    SELECT @err = @@error IF @err <> 0 RETURN  

    WHILE 1=1 BEGIN
        FETCH NEXT FROM cur2 INTO @name2, @level
        SELECT @err = @@error IF @err <> 0 RETURN 
        IF @@FETCH_STATUS <> 0 BREAK

        PRINT @name;
        PRINT CONVERT(nvarchar, @level)

        IF NOT EXISTS(SELECT 1 FROM #T2 WHERE procname = @name) BEGIN
            INSERT INTO #T2 (procname, level) VALUES (@name, @level);
        END ELSE BEGIN
            UPDATE #T2 SET level=@level WHERE procname=@name;
        END
    END

    CLOSE cur2;
    DEALLOCATE cur2;
END

CLOSE cur;
DEALLOCATE cur;

SELECT * FROM #T2 ORDER BY Level DESC;

DROP TABLE #T1
DROP TABLE #T2
GO
    

要回复问题请先登录注册