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