SQL 스크립트 생성 저장 프로시저

- sp_CreateScriptFile

SQL Server의 개체들에 대한 정의를 파일로 출력하여 주는 저장 프로시저 입니다.
코드를 보시면 아실 수 있겠지만, 뷰, 규칙, 기본값, 트리거, CHECK 제약 조건, DEFAULT 제약 조건, 저장 프로시저에 대해서만 작업이 가능합니다.

1. 먼저, 하단의 저장 프로시저를 master 데이터베이스에 생성합니다.

 

USE master
GO

CREATE PROCEDURE sp_CreateScriptFile
 @FilePath VARCHAR(1000),
 @objname nvarchar(776) 
/*
author : mssql.org
usage : exec sp_CreateScriptFile @FilePath='C:\', @objname = 'sp_CreateScriptFile'

date : 2005-06-24
*/
AS
SET NOCOUNT on

DECLARE @LineText nvarchar(4000)
 , @File VARCHAR(1000)
 , @dbname sysname
 , @FS INT
 , @RC INT
 , @FileID INT

SET @dbname = db_name()

IF object_id(@objname) IS NULL
BEGIN
 RAISERROR(15009, -1, -1, @objname, @dbname)
 RETURN
END

IF NOT EXISTS (SELECT 1 FROM dbo.syscomments WHERE id = object_id(@objname))
BEGIN
 RAISERROR('스크립팅은 뷰, 규칙, 기본값, 트리거, CHECK 제약 조건, DEFAULT 제약 조건, 저장 프로시저만 가능합니다.', 16, 1)
 RETURN
END

SET @File = @FilePath + @objname + '.sql'

IF @FilePath IS NULL
BEGIN
 raiserror(10035, 16, 1)
 RETURN
END

EXEC @RC = sp_OACreate  'Scripting.FileSystemObject' , @FS OUT

IF @RC <> 0
 BEGIN
 RAISERROR('파일 시스템 개체 생성 오류.', 16, 1)
 RETURN
 END

EXEC @RC = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @File, 8, 1

IF @RC <> 0
 BEGIN
 RAISERROR('지정된 파일 열기 오류.', 16, 1)
 RETURN
 END

PRINT @objname + ' file created - ' + @File

SET @LineText = '/*
Created : ' + CONVERT(VARCHAR(24), GETDATE(), 121) + '
*/'

EXEC @RC = sp_OAMethod @FileID, 'WriteLine', Null, @LineText

IF @RC <> 0
 BEGIN
  RAISERROR('파일 오류.', 16, 1)
  RETURN
 END

DECLARE cur_object_def CURSOR LOCAL
FOR SELECT text
 FROM dbo.syscomments
 WHERE id = OBJECT_ID(@objname) and encrypted = 0
 ORDER BY number, colid
FOR READ onLY

OPEN cur_object_def

FETCH NEXT FROM cur_object_def into @LineText

WHILE @@fetch_status >= 0
BEGIN
 EXEC @RC = sp_OAMethod @FileID, 'WriteLine', Null , @LineText

 IF @RC <> 0
  BEGIN
   RAISERROR('파일 오류.', 16, 1)
   PRINT 'Error:  Writing string data to file'
   CLOSE  cur_object_def
   DEALLOCATE  cur_object_def
   RETURN
  END
 FETCH NEXT FROM cur_object_def into @LineText
END

CLOSE  cur_object_def
DEALLOCATE cur_object_def

EXECUTE sp_OADestroy @FileID
EXECUTE sp_OADestroy @FS
GO

 

2. 이제 원하는 저장 프로시저를 파일로 작성해도록 하죠.
pubs 데이터베이스의 reptq1이라는 저장 프로시저를 C:\tmp\ 폴더에 작성하고자 한다면, 다음의 그림과 같이 쿼리를 작성하여 실행 합니다.

 

use pubs

go

exec sp_CreateScriptFile @FilePath='C:\TMP\', @objname='reptq1'

go

 

--> 이렇게 하면 reptq1 file created - C:\tmp\reptq1.sql 이라는 결과문이 나와야 정상임.

 

3. C:\tmp\ 폴더에는 저장 프로시저의 이름과 동일한 sql 파일이 작성되어 집니다.

 

4. sql 파일을 확인해보면...

 

/*

Created : 2005 어쩌구..

*/

CREATE PROCEDURE reptq1 AS

select pub_id, title_id, price, pubdate

from titles

where price is NOT NULL,

order by pub_id,

COMPUTE avg(price) by pub_id

COMPUTE avg(price)

~~~~~~~~~~~~~~~~~ > 어쩌구 생략..

 

- 전체 저장 프로시저 스크립팅

이번에는 pubs 데이터베이스에 정의되어 있는 모든 저장 프로시저들을 파일로 내려보도록 하죠...

 

declare @dt table(seq int identity, objname sysname)
declare @max int, @seq int, @objname sysname

insert @dt(objname)
select name
from dbo.sysobjects
where xtype='P'

select @max = @@identity, @seq = 1

while @max >= @seq
begin
 select @objname = objname, @seq = @seq + 1 from @dt where seq = @seq
 exec sp_CreateScriptFile 'C:\tmp\', @objname
end
GO 

 

쿼리를 실행해보면, 물론 잘 수행됩니다. ^^

 

물론, 저장 프로시저에서 전체를 하나의 파일로 작성하는 코드를 구현할 수 있겠지만... 귀찮기도 하구요... 사실 제가 필요한 건, 프로시저당 하나의 파일로 만드는 거였거든요. ^^

해당 기능이 필요하시다면, sp_CreateScriptFile 프로시저가 어느 정도 힌트는 될 것 같습니다.