MS-SQL 2000 에서 Stored Procedure 형상관리를 위한 Query

MS-SQL 2000 에서 Stored Procedure 형상관리를 위한 Query

 

작성자 : 이승연

 

근간에 Stored Procedure 관리상 ISSUE가 제기가 되었던 적이 있었다. 이유인 즉, DBA가 해당 Database에 Stored Procedure가 무엇이 있는지 또, 그 Stored Procedure가 언제 생겼는지, 언제 수정이 되었는지 전혀 관리가 되고 있지 않았다. 이에 다음과 같은 T-SQL문을 작성하게 되었고, 일정 주기 마다 실행함으로써 약간의 관리적인 ISSUE를 줄여보자는 의미에게 작성하게 되었다.

 

※ MS-SQL 2000에서는 MS-SQL 2005처럼 DDL Trigger도 없고, sysobjects의 schema_var과 refdate의 필드의 의미가 없어 기대한 만큼의 Stored Procedure의 형상관리가 어렵다.

 

Master Database에 Table 생성하기

/*                   작성자: 이승연                   작성일: 2009년07월06일                   작성내용: 저장프로시져형상관리를위한테이블생성 */ CREATE TABLE [tbl_db_spList] (                   [idx] [int]identity(1,1) NOT NULL ,                   [str_today] [varchar] (10) COLLATE Korean_Wansung_CI_AS NOT NULL ,                   [str_today_time] [smalldatetime] NOT NULL ,                   [dbname] [varchar] (20) COLLATE Korean_Wansung_CI_AS NOT NULL ,                   [spname] [varchar] (50) COLLATE Korean_Wansung_CI_AS NOT NULL ,                   [spcreate] [smalldatetime] NOT NULL,                   --CONSTRAINT [PK_tbl_db_spList] PRIMARY KEY  CLUSTERED                   --(                   --               [idx] DESC                   --) WITH  FILLFACTOR = 100  ON [PRIMARY] ) ON [PRIMARY] GO /*                   인덱스str_today와 dbname에 NonClustered Index로Fillfactor 100%로생성 */ CREATE  CLUSTERED INDEX [CX_tbl_db_spList_str_today] ON [dbo].[tbl_db_spList]([str_today] DESC ) WITH  FILLFACTOR = 100 ON [PRIMARY] GO CREATE INDEX [NCX_tbl_db_spList_dbname] ON [dbo].[tbl_db_spList]([dbname] DESC ) WITH  FILLFACTOR = 100 ON [PRIMARY] GO CREATE INDEX [NCX_tbl_db_spList_spname] ON [dbo].[tbl_db_spList]([spname] DESC ) WITH  FILLFACTOR = 100 ON [PRIMARY] GO

 

먼저, 위와 같이 Stored Procedure 목록을 저장할 테이블을 만든다. 이 테이블을 Stored Procedure의 목록만을 저장하는 목적으로 일정한 기간별로 목록을 비교하여 추가/삭제 된 Stored Procedure들이 무엇인지 파악하기 위한 것이다.

 

이제 실제로 실행되면서 Stored Procedure 목록을 저장시키는 Stored Procedure을 보겠다. 본 Stored Procedure는 sp_msforeachdb라는 문서화 되지 않은 System Stored Procedure을 사용했으며, MS-SQL 2005와 달리 MS-SQL 2000 에서는 사용자 Schema의 개념이 없기 때문에 부득이 하게 Stored Procedure의 이름에서 (‘sp_’, ‘xp_’, ‘ms_’) 와 같은 것들은 System Stored Procedure로 간주하고 목록에서 제외 시켰다.

 

Master Database에 Stored Procedure 목록 저장시키는 Stored Procedure 생성하기

CREATE PROCEDURE usp_db_spList_list /*                   작성자: 이승연                   작성일: 2009년07월06일                   작성내용: 저장프로시저형상관리를위한저장프로시저생성                   작동내용: 현재DBMS에속해있는DB안의모든SP의리스트를master..tbl_db_spList에저장한다. */ AS BEGIN                   SET NOCOUNT ON                   exec sp_msforeachdb '                   use ?                   INSERT INTO master.dbo.tbl_db_spList                   SELECT                                         CONVERT(varchar(10), getdate(), 120) AS str_today                  -- 실행된날짜검색조건을위해2009-00-00 형식을취함                   ,                 getdate() AS str_today                                                  -- 실행된시각                   ,                 ''?''                                                                                  -- 실행되는데이터베이스이름                   ,                 so.name                                                                          -- 저장프로시져이름                   ,                 so.crdate                                                                         -- 저장프로시져생성일                   FROM sysobjects  AS so INNER JOIN (SELECT id FROM syscomments GROUP BY id) sc                   ON so.id = sc.id                   WHERE xtype = ''P'' and left(so.name, 3) not in (''sp_'', ''xp_'',''ms_'')                   ORDER BY so.name ASC                   ' END

 

마지막으로 해당 Database들의 Stored Procedure의 생성구문을 파일로 만들어서 백업 시키는 Stored Procedure를 보면, 해당 Stored Procedure에서는 BCP와 Folder 생성을 위하여 부득이하게 xp_cmdShell를 사용하게 되었으며, 실행하기 전에 xp_cmdShell를 DBMS에 등록을 시키고, 작업이 종료되면 DBMS에서 등록 해제시키는 구문을 추가하였다. 그리고 CMD창에서 BCP로 작업이 실행이 될 때 DBMS권한 문제로 인하여 Builtin인 계정 추가/삭제 구문과 그 Builtin계정에 sysadmin 권한 부여/삭제 구문을 추가하였다.

 

Master Database에 Stored Procedure 생성구문을 백업하는 Stored Procedure

CREATE PROCEDURE usp_db_spList_file /*                   작성자: 이승연                   작성일: 2009년07월06일                   작성내용: 저장프로시저형상관리를위한저장프로시저생성                   작동내용: 현재DBMS에속해있는DB안의모든SP의생성구문을파일로저장한다.                   버전: MS-SQL 2000 */                   -- D:\DBBackup\SP <-- 파일경로명                   @Filename AS varchar(100) AS BEGIN                   SET NOCOUNT ON                                     DECLARE @SqlQuery varchar(1000)          -- bcp 문을생성하기위한변수                   DECLARE @pathFile varchar(40)                                  -- .bat 경로                   DECLARE @dbname varchar(100)              -- 실행되는데이터베이스이름                   SET @pathFile = 'c:\spList.bat'                                   -- .bat 경로                       EXEC SP_GRANTLOGIN 'BUILTIN\Administrators'                     -- CMD SHELL를권한없이쓰기위하여Builtin 계정생성                   EXEC SP_ADDEXTENDEDPROC 'xp_cmdshell', 'xplog70.dll'      -- xp_cmdshell를쓰기위하여등록                   EXEC SP_ADDSRVROLEMEMBER 'BUILTIN\Administrators', 'sysadmin'   -- CMD SHELL를쓰기위하여sysadmin 권한등록                       -- 해당날짜폴더를생성한다.                   SET @SqlQuery = 'echo '+left(@filename, 2)+'> '+@pathFile                   EXEC master..xp_cmdshell @SqlQuery                     SET @SqlQuery = 'echo cd '+@Filename+'>> '+@pathFile                   EXEC master..xp_cmdshell @SqlQuery                                     SET @SqlQuery = 'echo md '+convert(varchar(10),getdate(),120)+'>> '+@pathFile                   EXEC master..xp_cmdshell @SqlQuery                   EXEC master..xp_cmdshell @pathFile                                     -- 저장경로다시조정                   SET @Filename = @Filename + '\' + CONVERT(varchar(10),getdate(),120)                     -- 전체Database의stored procedure를생성하기위해커서를쓴다.                   -- sp_msforeachdb 문으로해결불가능                   DECLARE usp_cursor CURSOR FAST_FORWARD local                                    FOR SELECT name FROM sysdatabases;                   OPEN usp_cursor;                   FETCH next FROM usp_cursor INTO @dbname                   WHILE @@FETCH_STATUS = 0                   BEGIN                                    SET @SqlQuery = 'echo bcp "SELECT sc.text FROM '+@dbname+'..sysobjects AS so INNER JOIN '+@dbname+'..syscomments AS sc ON so.id = sc.id WHERE so.id > 0 AND xtype = ''P'' and left(so.name, 3) not in (''sp_'', ''xp_'',''ms_'')" queryout "'+@Filename+'\'+@dbname+'_StoredProcedure_'+CONVERT(varchar(10),getdate(),120)+'.txt" -c -t, -U -P -T > '+ @pathFile                                     EXEC master..xp_cmdshell @SqlQuery                                    EXEC master..xp_cmdshell @pathFile                                                                       FETCH next FROM usp_cursor INTO @dbname                   END                   CLOSE usp_cursor                   DEALLOCATE usp_cursor                                       EXEC SP_DROPSRVROLEMEMBER 'BUILTIN\Administrators', 'sysadmin'                   EXEC SP_DROPEXTENDEDPROC 'xp_cmdshell'                   EXEC SP_REVOKELOGIN 'BUILTIN\Administrators' END

 

해당 Table와 Stored Procedure는 Master Database에 생성하여 일주일에 한번씩 실행하여 관리하면 됩니다.

이후에 MS-SQL 2005 버전은 DDL Trigger를 이용하여 MS-SQL 2000보다 좀더 정확하게 Stored Procedure 관리하는 걸 올리겠습니다.

 

--------------------------------------------------------

DBA 이승연

Microsoft Certified Trainer (MCT) SQL Server

GRAVITY co.,Ltd.

Global Business Dept / Tech Support

Tel 02-2132-7351 / Fax 02-2132-7330

MOBILE 010-9138-7789

--------------------------------------------------------