sp_procedures 만들어보기 > db

이제.. sp_procedures를 개발해 보자 아주 간단하다. ㅋㅋㅋ

시간 관계상 다른 설명은 빼고... 소스부터 보자..

select
 convert(sysname, db_name()) as QUALIFIER_NAME,
 convert(sysname,user_name(uid)) as OWNER_NAME,
 name AS PROC_NAME
from
  sysobjects
where xtype='P'

위 처럼 간단하다... 내용은 직접 복사해서 돌려보시길..

여기서 db_name()은 현재 사용하고 있는 DB의 이름을 반환하는 함수이다. 데이터베이스도 시스템 테이블에 저장이 되어져 있는데 그건 master 데이터베이스에 존재한다. 테이블명은 sysdatabases 이고.. 이 테이블을 검색하면 데이터베이스에 관한 정보들을 알아 볼수 있다..

또... user_name() 함수는 ID나 현재 로그인한 사용자의 이름을 반환한다... 아주 좋은...함수이지용~~ 저는 이걸 sysusers에서 검색할려고 했으나.. 함수로 이미 만들어져 있었네요..

어차피 저 함수를 열어보면 sysusers를 검색할거긴 하지만.. 있는걸 사용해야지.....

아 그리고 위 소스에서 보이는 sysname이라는 데이터 형은 아래와 같다고 한다..

sysname은 nvarchar(128)과 같은 기능의 시스템 제공 사용자 정의 데이터 형식으로서 데이터베이스 개체 이름을 참조할 때 사용됩니다.

그러니깐 데이터베이스 객체의 이름을 참조할때 사용한다... 저걸 사용하면 좋겠군 ㅋㅋ

몰랐던 내용~~~

이제... 위 소스를 proc으로만 생성하면된다... 그러나... 기능하나 정도는 들어가야하지 않을까??

일단... owner와 name 정도로 검색을 할 수 있는 기능을 추가해 보겠다..

CREATE PROC sp_procedures
 @owner_name sysname = null,
 @proc_name varchar(200) = null
as
 -- 검색 조건이 없는경우
 IF @owner_name IS NULL AND @proc_name IS NULL
 BEGIN
  select
   convert(sysname, db_name()) as QUALIFIER_NAME,
   convert(sysname,user_name(uid)) as OWNER_NAME,
   name AS PROC_NAME
  from
    sysobjects
  where xtype='P'
 END
 -- 프로시져 이름으로만 검색하는경우
 IF @owner_name IS NULL AND @proc_name IS NOT NULL
 BEGIN
  select
   convert(sysname, db_name()) as QUALIFIER_NAME,
   convert(sysname,user_name(uid)) as OWNER_NAME,
   name AS PROC_NAME
  from
    sysobjects
  where
   xtype='P' AND [name] LIKE @proc_name
 END
 -- owner로만 검색하는경우
 IF @owner_name IS NOT NULL AND @proc_name IS NULL
 BEGIN
  select
   convert(sysname, db_name()) as QUALIFIER_NAME,
   convert(sysname,user_name(uid)) as OWNER_NAME,
   name AS PROC_NAME
  from
    sysobjects
  where
   xtype='P' AND user_name(uid) like @owner_name
 END
 -- 둘다로 검색하는 경우
 IF @owner_name IS NOT NULL AND @proc_name IS NOT NULL
 BEGIN
  select
   convert(sysname, db_name()) as QUALIFIER_NAME,
   convert(sysname,user_name(uid)) as OWNER_NAME,
   name AS PROC_NAME
  from
    sysobjects
  where
   xtype='P'
   AND user_name(uid) like @owner_name
   AND name LIKE @proc_name
 END

다 만들어 졌따.. 위에 소스가 좀 보기 거시기 해도 빨리 만드느라 어쩔 수 없었다.. 이제 sp_tables와 매우 배슷한.. 프로시져 sp_procedure가 완성이되었다. ㅋㅋ

사용법은

exec sp_procedure 'dbo', 'rsp_%' 이처럼 사용하면된다. 아니면
exec sp_procedure 만 써도 된다...

지금 회사 같은 경우 owner가 여러가지로 존재해 찾아내는것도 힘들었다.. 이제.. 이 프로시져로 단번에 검색 할 수 있어서 편하다..

그리고 이걸 시스템 procedure로 사용하고 싶다... 어떤 database던지 그냥 sp_procedures만 날리면 프로시져들이 다 나왔으면 좋겠다.... 고 생각하면 어떻게 하느냐????

일단 master로 가서.... 프로시져를 생성하라...그럼 된다.. 예전에 .. 그렇게 알고 있었는데.. 젠장 테스트를 해볼 수 가 없네 -_-;;; 일단 믿어보시라 ㅋㅋㅋ

그리고 아주 아주 아주 중요한 내용...

프로시져를 만들때 이름에 "sp_" 를 사람들이 아무렇지 않게 사용한다... 이거 이거 시스템에 과부화를 주는 아주 착한짓이다.. 절대 프로시져 이름을 생성할때... "sp_"로 시작 하지 말기 바란다.. 왜 냐하면 "sp_"로 시작하게 되면 DB는 이것을 시스템 프로시져로 인식을 해 master에 있는 sysobjects를 뒤지게 된다.

그리고 거기에 없으면... 아 ... 로그인한 데이터베이스 인가보다 하고 다시 로그인하고 있는 데이터베이스의 sysobjects를 뒤져서 실행하게 된다. 정말 어이없지만... 그렇게 될 수 밖에 없다...

그래서 나 같은 경우는 "rsp_"로 시작한다. ㅋㅋㅋ rocky_stored_procedure ㅋㅋㅋㅋ

데이터베이스를 공부해 가고 있지만 느끼는건... 사소한 실수에도 데이터베이스는 아주 민감하게 반응한다는것이다...

조심스레 살살... 여자다루듯이 말이다~~~