태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

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

Posted at 2009.07.07 17:06 // in MSSQL/T-SQL // by 건방진연이

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 목록을 저장하는 TableModify 정보를 기록할 수 있는 필드를 추가한다.

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

 

Master Database Table 생성하기

/*

                  작성자: 이승연

                  작성일: 20090706

                  작성내용: 저장프로시져형상관리를위한테이블생성

*/

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 IndexFillfactor 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

/*

                  작성자: 이승연

                  작성일: 20090706

                  작성내용: 저장프로시저형상관리를위한저장프로시저생성

                  작동내용: 현재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)

 

                  -- 전체Databasestored 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을 이용하여 만들어야 한다. 해당 감시 TriggerMS-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

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


댓글을 남겨주세요.