导出数据库的视图,函数,存储过程以前触发器

CREATE PROC dbo.usp_OutObjects

    @dbname sysname,

    @Path NVARCHAR(1024)

AS

BEGIN

    DECLARE

        @viewPath NVARCHAR(1024),

        @functionPath NVARCHAR(1024),

        @procedurePath NVARCHAR(1024),

        @triggerPath NVARCHAR(1024);

 

    SELECT

        @viewPath=@Path+'Views/',

        @functionPath=@Path+'Functions/',

        @procedurePath=@Path+'Procedures/',

        @triggerPath=@Path+'Triggers/';

 

    DECLARE @cmd NVARCHAR(4000);

    SET @cmd='md "'+@viewPath+'"';

 

    EXEC master.dbo.xp_cmdshell @cmd,no_output;

 

    SET @cmd='md "'+@functionPath+'"';

    EXEC master.dbo.xp_cmdshell @cmd,no_output;

 

    SET @cmd='md "'+@procedurePath+'"';

    EXEC master.dbo.xp_cmdshell @cmd,no_output;

 

    SET @cmd='md "'+@triggerPath+'"';

    EXEC master.dbo.xp_cmdshell @cmd,no_output;

 

    IF DB_ID(@dbname) IS NULL OR ISNULL(NULLIF(@dbname,''),'')=''

        SET @dbname=db_name();

 

    CREATE TABLE MyTest..OutputObjects

    (

        [object_id] INT,

        [name] sysname,

        [text] NVARCHAR(MAX),

        [type] TINYINT -- 0:Views ,1:function ,2:procedure ,3:trigger

    );

 

    SET @cmd=N'USE ['+@dbname+']';

    SET @cmd=@cmd+CHAR(13)+CHAR(10);

    SET @cmd=@cmd+'INSERT INTO MyTest..OutputObjects

            SELECT

                o.[object_id],

                o.name,

                m.definition,

                CASE WHEN OBJECTPROPERTY(o.[object_id],''IsView'')=1

                        THEN 0

                     WHEN OBJECTPROPERTY(o.[object_id],''IsScalarFunction'')=1

                           OR OBJECTPROPERTY(o.[object_id],''IsTableFunction'')=1

                        THEN 1

                     WHEN OBJECTPROPERTY(o.[object_id],''IsProcedure'')=1

                         THEN 2

                     WHEN OBJECTPROPERTY(o.[object_id],''IsTrigger'')=1

                         THEN 3

                 END

            FROM sys.objects AS o

                JOIN sys.sql_modules AS m

                    ON o.[object_id]=m.[object_id]

            WHERE OBJECTPROPERTY(o.[object_id],''IsEncrypted'')=0

                AND OBJECTPROPERTY(o.[object_id],''IsExecuted'')=1

                AND o.is_ms_shipped=0

            ORDER BY o.[object_id];';

    EXEC(@cmd);

 

    DECLARE @object_id INT;

    DECLARE @filename NVARCHAR(2056);

 

    SET @object_id=(SELECT MIN([object_id]) FROM MyTest..OutputObjects);

 

    WHILE @object_id IS NOT NULL

        BEGIN

            SELECT

                @filename=

                    CASE [type]

                        WHEN 0 THEN @viewPath

                        WHEN 1 THEN @functionPath

                        WHEN 2 THEN @procedurePath

                        WHEN 3 THEN @triggerpath

                    END +name + '.sql'

            FROM MyTest.dbo.OutputObjects

            WHERE [object_id]=@object_id;

 

            SET @cmd=N'bcp "SELECT [text] FROM MyTest.dbo.OutputObjects';

            SET @cmd=@cmd+N' WHERE [object_id]='+RTRIM(@object_id)

            SET @cmd=@cmd+N'" queryout "'+@filename+'"'

            SET @cmd=@cmd+N' -q -w -T -Smyfend/LIANGCK';

 

            EXEC master.dbo.xp_cmdshell @cmd,no_output;

 

            SET @object_id=(SELECT MIN([object_id]) FROM MyTest.dbo.OutputObjects

 

                               WHERE [object_id]>@object_id);

        END

    DROP TABLE MyTest..OutputObjects;

END

 

GO

EXEC dbo.usp_OutObjects 'MyTest','G:/Test/'

GO

DROP PROC dbo.usp_OutObjects