%nbsp

CREATE PROCEDURE [dbo].[up_CreateScript]
        @TargetDBAddr   VARCHAR(15),
        @TargetDBName   VARCHAR(128),
        @TargetDBUser   VARCHAR(255) = NULL,  -- IF NULL Windows 로그인
        @TargetDBPass   VARCHAR(255) = NULL,
        @OutFilePath    VARCHAR(256) = 'c:\', -- In Scripting SQL-Server Side OutFilePath   -- IF NULL 'c:\'
        @Objtype        TINYINT = 1,          -- 1 : Table & View 2 : Procedure             -- IF NULL all objects
        @OutFileName    VARCHAR(256) = NULL,  -- In Scripting SQL-Server Side OutFileName   -- IF NULL each object file create
        @ObjName        VARCHAR(255) = NULL   -- 테이블명 or 프로시저명                     -- IF NULL all objects
        /*
        Copy Left All Right Reserverd
       
        @TargetDBAddr = '192.168.1.1'
        @TargetDBName = '데이터베이스명'
        @TargetDBUser = 'DB계정명'
        @TargetDBPass = 'DB비밀번호'
        @OutFilePath = 'c:\스크립트 파일들이 생성될 경로\'
        @Objtype = 1 -- 생성종류
        @OutFileName ='ALL.sql'  --출력파일명
        */
AS
IF right(@OutFilePath,1) <> '\' BEGIN
    SELECT @OutFilePath = @OutFilePath + '\'
END
-- get objects to script and object type
CREATE TABLE #ObjectList ( Owner VARCHAR(128), Objname VARCHAR(128), SubObjname VARCHAR(128), Objtype VARCHAR(20), refcnt tinyint )
DECLARE @TT TABLE(objtype varchar(3) )
DECLARE @sql            VARCHAR(8000),
        @tmpFileName    VARCHAR(256),
        @FileName       VARCHAR(256),
        @SubObjname     VARCHAR(256),
        @obj            VARCHAR(256),
        @ScriptType     INT
DECLARE @ObjectServer   INT,
        @rc             INT,
        @seq            INT
/* 임시테이블에 TargetDB의 Object 객체 추가. 생성 순서 에 맞게  필요한 대로 정렬해서 사용하심 될듯..;; */
/* table, procedure, view */
SET @sql = 'SELECT '
SET @sql = @sql + 'u.Name as Owner, o.name, o.xtype, '
SET @sql = @sql + 'case o.xtype '
SET @sql = @sql + ' when ''P'' then 100 '
SET @sql = @sql + ' when ''V'' then 10 '
SET @sql = @sql + ' when ''U'' then 0 '
SET @sql = @sql + 'end+'
SET @sql = @sql + 'ISNULL (b.cnt, 0) as refcnt '
SET @sql = @sql + 'FROM '
SET @sql = @sql + @TargetDBName + '..sysobjects o '
SET @sql = @sql + 'INNER JOIN '
SET @sql = @sql + 'master..sysusers u on (o.uid = u.uid) '
SET @sql = @sql + 'LEFT OUTER JOIN '
SET @sql = @sql + '     (   select o.parent_obj as id, count(o.parent_obj) as cnt from '
SET @sql = @sql + @TargetDBName + '     ..sysobjects o '
SET @sql = @sql + '         where o.xtype in (''F'') Group by o.parent_obj  ) as b '
SET @sql = @sql + ' ON ( o.id = b.id) '
SET @sql = @sql + 'WHERE '
SET @sql = @sql + 'o.xtype in (''U'',''V'',''P'' ) '
SET @sql = @sql + ' AND o.name not like ''sys%'''
SET @sql = @sql + ' AND o.name NOT LIKE ''dt%'''
SET @sql = @sql + ' ORDER BY refcnt , base_schema_ver DESC '
SELECT @sql = replace(@sql,'''','''''')
/* 자주 사용할경우 Linked DB 등록후 해서 사용하세요..;; 보안문제로..;; */
exec('INSERT INTO #ObjectList (Owner , Objname , Objtype, refcnt )SELECT a.*  FROM OPENROWSET(''sqloledb'',''' + @TargetDBAddr + ''';''' + @TargetDBUser +''';'''+@TargetDBPass+''','''+ @sql+''')as a')
/* //////////////////////////////////////////////////////// */
/* /이하 부분은 DMO를 통한 스크립트 파일을 생성하는 부분 // */
/* //////////////////////////////////////////////////////// */
/* DMO 객체 생성 */
PRINT 'CREATE DMO OBJECT...'
EXEC @rc = sp_OACreate 'SQLDMO.SQLServer', @ObjectServer OUT
IF @rc <> 0 or @@error <> 0 goto Error
/* 로그인 */
IF (@TargetDBUser IS NULL OR @TargetDBPass IS NULL) BEGIN
    EXEC @rc = sp_OASetProperty @ObjectServer, 'LoginSecure', 'True'
    IF @rc <> 0 or @@error <> 0 goto Error
END
EXEC @rc = sp_OAMethod @ObjectServer, 'Connect', NULL, @TargetDBAddr, @TargetDBUser, @TargetDBPass
if @rc <> 0 or @@error <> 0 goto Error
/* 스크립트 생성 타입 설정 및 임시파일명 지정
http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=122&rl=1
*/
SELECT  @ScriptType     = 32 | 4  | 1 | 32 | 73736 | 2 | 64 | 262144 ,
        @tmpFileName    = @OutFilePath + 'ScriptTmp.txt'
/* 지정파일 새로 생성할 경우 */
-- IF @OutFileName IS not null BEGIN
--  PRINT @OutFilePath + @OutFileName + ' Create... '
--  SELECT  @sql = 'echo. > ' + @OutFilePath + @OutFileName
--  EXEC master..xp_cmdshell @sql
-- END
/* 스크립트를 생성할 해당 Object 객체 대상 선정 */
IF (@Objtype = 1) BEGIN -- table & view
    INSERT INTO @TT VALUES('U')
    INSERT INTO @TT VALUES('V')
    INSERT INTO @TT VALUES('IX')
    INSERT INTO @TT VALUES('TR')
END ELSE IF  (@Objtype = 2) BEGIN
    INSERT INTO @TT VALUES('P')
END ELSE BEGIN
    INSERT INTO @TT VALUES('U')
    INSERT INTO @TT VALUES('V')
    INSERT INTO @TT VALUES('P')
    INSERT INTO @TT VALUES('IX')
    INSERT INTO @TT VALUES('TR')
END
    /* 각각의 object 를 파일로 생성함 */
    DECLARE CreateScript_cursor CURSOR
    FOR
        SELECT  Objname, SubObjname,
            Owner+'.'+Objname+ CASE Objtype
                        WHEN 'P' THEN '.PRC'
                        WHEN 'U' THEN '.TAB'
                        WHEN 'V' THEN '.View'
                        WHEN 'IX' THEN '.TAB'
                        WHEN 'TR' THEN '.TAB'
                        ELSE '.SQL'
                    END as FileName,
            CASE Objtype
                WHEN 'P' THEN 'storedprocedures'
                WHEN 'U' THEN 'tables'
                WHEN 'V' THEN 'views'
                WHEN 'IX' THEN 'indexes'
                WHEN 'TR' THEN 'triggers'
                ELSE 'invalid @ObjectType'
            END as Obj
        from #ObjectList
        WHERE Objtype in ( select * from @TT ) AND 
            (CASE WHEN @ObjName IS NOT NULL THEN ObjName ELSE 'ALL' END)
            Like (CASE WHEN @ObjName IS NOT NULL THEN '%' + @ObjName + '%' ELSE 'ALL' END)
    OPEN CreateScript_cursor
    FETCH NEXT FROM CreateScript_cursor INTO @Objname, @SubObjname, @FileName, @Obj
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
        IF (@@FETCH_STATUS <> -2)
        BEGIN
            PRINT @FileName
            IF ((@SubObjname IS NOT NULL) AND  (@Obj  = 'indexes')) BEGIN
                Set @sql = 'databases("' + @TargetDBName + '").' + 'tables' + '("' + @Objname + '").indexes("' + @SubObjname + '").script'
            END ELSE IF ((@SubObjname IS NOT NULL) AND  (@Obj  = 'triggers')) BEGIN
                Set @sql = 'databases("' + @TargetDBName + '").' + 'tables' + '("' + @Objname + '").triggers("' + @SubObjname + '").script'
            END ELSE BEGIN
                Set @sql = 'databases("' + @TargetDBName + '").' + @Obj + '("' + @Objname + '").script'
            END
   
            EXEC @rc = sp_OAMethod @ObjectServer, @sql , NULL , @ScriptType , @tmpFileName
   
            IF @OutFileName IS not null BEGIN
                SELECT @sql = 'type ' + @tmpFileName + ' >> ' + @OutFilePath + @OutFileName
                EXEC master..xp_cmdshell @sql
            END ELSE BEGIN
                SELECT @sql = 'type ' + @tmpFileName + '>> ' + @OutFilePath + @FileName
                EXEC master..xp_cmdshell @sql
            END
   
            FETCH NEXT FROM CreateScript_cursor INTO @Objname, @SubObjname, @FileName, @Obj
        END
    END
    CLOSE CreateScript_cursor
    DEALLOCATE CreateScript_cursor
/* 임시파일 제거 */
select @sql = 'del ' + @tmpFileName
exec master..xp_cmdshell @sql, no_output
/* DMO 객체 제거 */
exec @rc = sp_OAMethod @ObjectServer, 'Disconnect'
if @rc <> 0 or @@error <> 0 goto Error
exec @rc = sp_OADestroy @ObjectServer
if @rc <> 0 or @@error <> 0 goto Error
/* 임시 테이블 제거 */
drop table #ObjectList
/* 필요하면 에러로그를 남기셔서 확인하셔도 될듯..;; */
Error:
    return @sql