본문 바로가기

MSSQL/T-SQL

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

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

 

작성자 : 이승연

 

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

 

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

 

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 ,

                  [spcreate] [smalldatetime] NOT NULL,

                  --CONSTRAINT [PK_tbl_db_spList] PRIMARY KEY  CLUSTERED

                  --(

                  --               [idx] DESC

                  --) WITH  FILLFACTOR = 100  ON [PRIMARY]

) ON [PRIMARY]

GO

/*

                  인덱스str_todaydbnameNonClustered 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의 목록만을 저장하는 목적으로 일정한 기간별로 목록을 비교하여 추가/삭제 된 Stored Procedure들이 무엇인지 파악하기 위한 것이다.

 

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

 

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

CREATE PROCEDURE usp_db_spList_list

/*

                  작성자: 이승연

                  작성일: 20090706

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

                  작동내용: 현재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에서는 BCPFolder 생성을 위하여 부득이하게 xp_cmdShell를 사용하게 되었으며, 실행하기 전에 xp_cmdShell DBMS에 등록을 시키고, 작업이 종료되면 DBMS에서 등록 해제시키는 구문을 추가하였다. 그리고 CMD창에서 BCP로 작업이 실행이 될 때 DBMS권한 문제로 인하여 Builtin인 계정 추가/삭제 구문과 그 Builtin계정에 sysadmin 권한 부여/삭제 구문을 추가하였다.

 

Master DatabaseStored 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_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)

 

                  -- 전체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 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

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