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

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

 

작성자 : 이승연

 

MS-SQL 2000 버전과 2005 버전의 가장 큰 차이점을 MS사에서 2005버전으로 넘어오면서 많은 시스템뷰와 카탈로그뷰가 추가되었다는 점이다. 물론, 만들고자 하는 Stored Procedure 형상관리에 필요한 유용한 시스템뷰를 사용할 수가 있다. 그럼 간략하게 Stored Procedure 형상관리를 위해 사용되었던 명령어를 비교하여 보자.

 

MS-SQL 2000	MS-SQL 2005
sysobjects	sys.objects
syscomments	sys_modules

 

이와 관련하여 MS-SQL 2000에서 사용하던 System Table을 MS-SQL 2005에서 System View으로 매칭된 내용이 TechNet에 정보가 공개가 되어 있다.

[참고] http://technet.microsoft.com/ko-kr/library/ms187997.aspx

 

System Table에서 System View으로 이름과 명칭만 바뀐 것이 아니고 필드의 내용들도 바뀌었다.

그 중에 우리가 주목해야 할 정보는 sys_modules 에 modify_data 필드 이다. 이 필드는 해당 오프젝트가 변경 될 때마다 그 시간을 기록을 한다. MS-SQL 2000에서도 비슷한 필드가 존재하긴 하지만 생성일과 같은 일로 기록이 된 후에 해당 오브젝트가 수정이 되어도 변동이 되지 않은 필드이다.

 

그래서 Stored procedure 목록을 저장하는 Table에 Modify 정보를 기록할 수 있는 필드를 추가한다.

또한, type_desc 라는 필드도 추가한다. 이 필드는 오브젝트의 형태를 나타내어 준다.

 

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 ,                   [object_type] [varchar] (50) COLLATE Korean_Wansung_CI_AS NOT NULL ,                   [spcreate] [smalldatetime] NOT NULL,                   [spmodify] [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을 만들기 전에 MS-SQL 2000용 버전을 만들 때 기억을 해야 한다. 우리가 MS-SQL 2000 버전을 만들 때 2000에서는 Schema라는 개념이 없기 때문에 Stored Procedure 이름에서 (‘sp_’, ‘xp_’, ‘ms_’) 와 같은 이름을 제외 시켰던 것을 기억 할 것이다. 하지만, MS-SQL 2005 버전 부터는 Schema라는 개념이 도입이 되었고, sys.object 의 schema_id 필드가 바로 그 것이다. dbo 사용자가 오브젝트를 만들시에는 해당 schema_id가 1이 된다.

 

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

CREATE PROCEDURE ups_db_spList_list /*                   작성자: 이승연                   작성일: 2009년월일                   작성내용: 저장프로시저형상관리를위한저장프로시저생성                   작동내용: 현재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.type_desc                                                 -- object 타입                   ,                 so.create_date                                              -- 생성일                   ,                 so.modify_date                                            -- 수정일                   FROM sys.objects AS so INNER JOIN sys.sql_modules AS ss                   ON so.object_id = ss.object_id                   WHERE so.schema_id = 1 AND type = ''P''                   ' END

 

마지막으로 stored procedure 생성문을 저장시키는 stored procedure를 만들어 보자. 이것도 MS-SQL 2000와 마찬가지로 xp_cmdShell을 사용하였다. 2005에서는 SSIS라는 기능이 새로 추가가 되어 xp_cmdShell을 사용하지 않고 SSIS로 개발을 하면 되지만, 일단은 xp_cmdshell을 사용하여 작성하였다.

 

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_configure 'show advanced option', '1'; RECONFIGURE;               -- xp_cmdshell를쓰기위하여등록                   EXEC sp_configure 'xp_cmdshell' , '1'; RECONFIGURE;                                                                                                                            -- 해당날짜폴더를생성한다.                   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 ss.definition    FROM '+@dbname+'.sys.objects AS so INNER JOIN '+@dbname+'sys.sql_modules AS ss ON so.object_id = ss.object_id WHERE so.schema_id = 1 AND type = ''P''" 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_configure 'show advanced option', '1'; RECONFIGURE;                   EXEC sp_configure 'xp_cmdshell' , '0'; RECONFIGURE; END

 

위 Transact-SQL 에서도 MS-SQL 2000와 다른 Query문이 몇 가지 보일 것 이다. 그것은 sp_configure 이다. 2005부터는 해당 시스템 저장프로시저를 이용하여 DBMS을 옵션을 추가하거나 변경할 수가 있다. 이 옵션들을 다 나열을 할 수는 없고, 2000에서는 30여 가지를 설정할 수 있는데, 2005에서는 60여 가지를 설정할 수 있다고 한다. 그 만큼 보안에 큰 비중을 둔 것 같다.

 

여기까지가 “MS-SQL 2000 에서 Stored Procedure 형상관리를 위한 Query” 와 동일한 작업이었고, 2005에서는 Trigger을 이용하여 해당 Database을 감시(Audit)할 수가 있다. MS-SQL 2008에서는 이러한 감시기능이 추가가 되었지만, 2005에는 없기 때문에 DDL Trigger을 이용하여 만들어야 한다. 해당 감시 Trigger을 MS-SQL 2000에서 사용할 수 없는 것은 2000에서는 DDL Trigger가 존재하지 않고 DML Trigger만 존재하기 때문이다. DDL Trigger는 2005에서 새로 추가된 기능이다.

 

 

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

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

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