Search

'MS-SQL'에 해당되는 글 3건

  1. 2012.04.05 Check procedure
  2. 2009.09.09 Database File Size 알기
  3. 2009.06.07 시스템 뷰를 활용한 검색 쿼리 구현. (2)

Check procedure

Database 2012. 4. 5. 16:34 Posted by Jay Choi


1. 사용중인 테이블 조회

WITH sp_list AS (
    SELECT o.name AS proc_name
           , oo.name AS table_name
           , Row_number() OVER(partition by o.name, oo.name ORDER BY o.name, oo.name) AS row
      FROM sysdepends d
           INNER JOIN sysobjects o ON o.id = d.id
           INNER JOIN sysobjects oo ON oo.id = d.depid
     WHERE o.xtype = 'P'
)
SELECT proc_name, table_name
  FROM sp_list
 WHERE row = 1
 ORDER BY proc_name, table_name


2. 인덱스 조각화율 체크

SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'Production.Product'), NULL, NULL, NULL) AS a
    JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
GO


퍼왔음...

댓글을 달아 주세요

Database File Size 알기

Database 2009. 9. 9. 23:56 Posted by Jay Choi

select name, convert(numeric,convert(numeric,size) * (8 * 1024)) as sizeByte
from sys.database_files with(nolock)

일단은 바쁘니 이렇게 저장...
DB 파일 용량 알아오기...
사용량 증가 그래프가 필요 할 때 짭짤 할 듯...

댓글을 달아 주세요

시스템 뷰를 활용한 검색 쿼리 구현.

Database 2009. 6. 7. 02:29 Posted by Jay Choi

초기에 DB 스키마를 설계하고 개발이 진행되거나, 혹은 DB 관리 시
점점 늘어만 가는 Table과 Procedure, Function 에서 내가 수정해야 하는 것을
딱 집어서 가져오기가 힘겨워 진다.
그래서 알게 된 것이 System Table 과 System Procedure를 이용 하는 것이다.
(오... 이런게 있었어...ㅠㅠ, 사실 안 지는 좀 됐지만, 정리해 놓는 것이 귀찮았음.)

Management Studio에서 필터를 사용 했지만,
마우스로 손이 가서 일일이 내용에 맞는 필터를 사용하는 귀찮음이란...ㅠㅠ
(그래도 그것이 전부 인 줄 알았었다.)

그러다가 알게 된 것이 System Table...
'SYSOBJECTS 이거 좀 뒤벼보면 뭔가 나오겠구나~~~!'
아니나 다를까... 많은 분들이 사용 하고 계시더군...
나만 몰랐어...ㅠㅠ


이것이 현재 내가 사용 하는 가장 유용하고 짭짤한 프로시저 이다.

-- [프로시저 이름으로 프로시저/함수 내용 가져오기]
-- * 프로시저 이름 : sp_helptext 
-- * 프로시저 내용 : System Procedure
-- * 사용 방법

	exec sp_helptext [Procedure Name]

프로시저/함수 이름만 알면, 내용 전체를 주욱 가져 올 수 있다. 참 좋군...

그럼 이런 질문이 나올 것이다.
질문자 : "그렇다면, 프로시저/함수의 전체 이름을 알아야 사용 할 수 있는 거야...?"
답변자 : "네.. 그렇지요!"
질문자 : "아놔 그럼 그것을 어떻게 다 기억 하고 있니...?"
답변자 : "..."

그래서 이제 부터 필요한 것이...
위에서 언급한 SYSOBJECTS를 검색해서 원하는 것들을 찾는 방법이다.
MSDN에서 sysobjecets를 검색하면...
http://msdn.microsoft.com/en-us/library/ms177596.aspx  (영문)
http://msdn.microsoft.com/ko-kr/library/ms177596(SQL.90).aspx (한글)

흠... 자세한 사항은 읽어보시고...
한 가지 주의를 해서 볼 사항은 sys.objects 이다.
sysobjects는 호환성 뷰일 뿐이고~~~!
"제대로 볼라면 objects로 보시오."
그건 일단 나중에 함 살펴 보고...
http://msdn.microsoft.com/en-us/library/ms190324.aspx (영문)
http://msdn.microsoft.com/ko-kr/library/ms190324(SQL.90).aspx (한글)


그리 하여 첫 번째로 내가 만들어서 사용할 쿼리는 특정 단어를 포함 하는 테이블 찾기.

-- [제목에 특정 단어가 포함 된 테이블 찾기]
-- * 사용 방법

	SELECT name
	FROM DBO.SYSOBJECTS
	WHERE xtype = 'U' and name like '%'+[특정 단어]+'%'
	ORDER BY name
여기에서 주목 할 점은 xtype 이다.
'U'는 사용자 정의 테이블 이란 뜻이다.
즉, 타입으로 구분하여 사용한다는 것.
개체 유형(object type)은 다음과 같다.
FN = SQL 스칼라 함수
P = SQL 저장 프로시저
AF = 집계 함수(CLR)
C = CHECK 제약 조건
D = DEFAULT(제약 조건 또는 독립 실행형)
F = FOREIGN KEY 제약 조건
PK = PRIMARY KEY 제약 조건
PC = 어셈블리(CLR) 저장 프로시저
FS = 어셈블리(CLR) 스칼라 함수
FT = 어셈블리(CLR) 테이블 값 함수
R = 규칙(이전 스타일, 독립 실행형)
RF = 복제 필터 프로시저
S = 시스템 기본 테이블
SN = 동의어
SQ = 서비스 큐
TA = 어셈블리(CLR) DML 트리거
TR = SQL DML 트리거
IF = SQL 인라인 테이블 값 함수
TF = SQL 테이블 값 함수
U = 테이블(사용자 정의)
UQ = UNIQUE 제약 조건
V = 뷰
X = 확장 저장 프로시저
IT = 내부 테이블
위 쿼리와 응용하여
-- [제목에 특정 단어가 포함 된 프로시저 찾기]
-- * 사용 방법
	SELECT name
	FROM DBO.SYSOBJECTS
	WHERE xtype = 'P' and name like '%'+[특정 단어]+'%'
	ORDER BY name


-- [제목에 특정 단어가 포함 된 스칼라 함수 찾기]
-- * 사용 방법
	SELECT name
	FROM DBO.SYSOBJECTS
	WHERE xtype = 'FN' and name like '%'+[특정 단어]+'%'
	ORDER BY name


요로코럼 만들고 이것을 프로시저로 만들어서
사용 한다면 완전 편하다.

이렇게만 이용하면, 뭔가가 아쉽다.
바로 내용 검색이다.
내용에 특정 단어가 포함 된 프로시저 및 함수가 찾고 싶을 때가 있을 것이다.

이때는 syscomments와 조인 하여 검색 하면 해당 프로시저를 찾을 수가 있을 것이다.
http://msdn.microsoft.com/en-us/library/aa260393.aspx (영문)
http://msdn.microsoft.com/ko-kr/library/ms186293(SQL.90).aspx (한글)

여기서 중요한 점 또 발견 한 것이... sys.sql_modules.
이것 역시 나중에 시간 날 때... 고고싱~~~! 관련 링크는 다음과 같다.
http://msdn.microsoft.com/en-us/library/ms175081.aspx (영문)
http://msdn.microsoft.com/ko-kr/library/ms175081(SQL.90).aspx (한글)

 

-- [내용에 특정 단어가 포함 된 프로시저 찾기]
-- * 사용 방법
	SELECT DBO.SYSOBJECTS.name 
	FROM DBO.SYSOBJECTS, DBO.SYSCOMMENTS
	WHERE DBO.SYSOBJECTS.id = DBO.SYSCOMMENTS.id 
	   and DBO.SYSOBJECTS.[type] = 'P' 
	   and DBO.SYSCOMMENTS.[text] like '%'+[특정 단어]+'%'
	ORDER BY DBO.SYSOBJECTS.name


-- [내용에 특정 단어가 포함 된 스칼라 함수 찾기]
-- * 사용 방법
	SELECT DBO.SYSOBJECTS.name 
	FROM DBO.SYSOBJECTS, DBO.SYSCOMMENTS
	WHERE DBO.SYSOBJECTS.id = DBO.SYSCOMMENTS.id 
	   and DBO.SYSOBJECTS.[type] = 'FN' 
	   and DBO.SYSCOMMENTS.[text] like '%'+[특정 단어]+'%'
	ORDER BY DBO.SYSOBJECTS.name


이 외에도 여러 형태로 입맛에 맞게 만들어서 사용 하면
삽질 시간이 많이 줄어들 듯 하다.


p.s 1 : MS-SQL의 버전이 올라가면서 시스템 테이블을 숨기고,
이것을 뷰로 제공함으로써 보안을 강화 한 것 같다.
왜 그렇게 했는지는 시간 날 때 뒤져 봐야 할 듯...

p.s 2 : 글 하나 쓰고 지쳐버림... gg...

댓글을 달아 주세요

  1. hugyou 2009.06.19 14:36  댓글주소  수정/삭제  댓글쓰기

    SQL 2005 이상이라면 select * from sys.procedures, select * from sys.triggers 이런거 한번 써바바 :)

    • Jay Choi 2009.07.01 17:44 신고  댓글주소  수정/삭제

      흠... 프로시저 내용 검색 쿼리를 바꿔 볼 수 있겠군...
      프로시저만 따로 뷰를 제공 하는군...
      다음에 함 고쳐봐야지...