SQL Server 문서화 되지 않은 시스템 저장 프로시저
이 문서에서 SQL SERVER 2000의 문서화되지 않은 유용한 저장 프로시저에 대해서 말하고자 한다.
■ sp_MSget_qualified_name
sp_MSget_qualified_name 저장 프로시저는 Object ID 값으로 해당 개체의 전체 이름([사용자].[개체명]의 형식)을 얻는데 쓰인다.
문법
sp_MSget_qualified_name object_id, qualified_name
where
object_id - is the object id. object_id is int.
qualified_name - is the qualified name of the object. qualified_name is nvarchar(512).
아래 예제는 pubs database에서 authors 테이블의 전체 이름을 구하는 것이다.
USE pubs
GO
declare @object_id int, @qualified_name nvarchar(512)
select @object_id = object_id('authors')
EXEC sp_MSget_qualified_name @object_id, @qualified_name outputselect @qualified_name
GO
-- 결과 :
--------------------------------------
[dbo].[authors]
■ sp_MSdrop_object
sp_MSdrop_object 프로시저는 개체(테이블, 뷰, 저장 프로시저, 트리거)를 삭제하는 곳에 쓰인다. 개체 ID, 개체 이름, 개체 소유자(Owner) 값을 주어 삭제 할 수 있으며, 이들 값이 명확하지 않을 경우 어떤 개체의 삭제도 일어나지 않는다.
문법
sp_MSdrop_object [object_id] [,object_name] [,object_owner]
where
object_id - is the object id. object_id is int,
with a default of NULL.
object_name - is the name of the object. object_name is sysname,
with a default of NULL.
object_owner - is the object owner. object_owner is sysname,
with a default of NULL.
-- 다음은 pubs database의 titleauthor 테이블을 삭제하는 예제이다..
USE pubs
GO
declare @object_id int
select @object_id = object_id('titleauthor')
EXEC sp_MSdrop_object @object_idGO
* 주: 다음과 같은 방법으로도 삭제 가능합니다.(titleauthor의 owner가 dbo라고 가정)
EXEC sp_MSdrop_object null, titleauthor
EXEC sp_MSdrop_object null, titleauthor, dbo
■ sp_gettypestring
sp_gettypestring
저장 프로시저는 테이블 컬럼의 타입 정보를 반환한다.
문법
sp_gettypestring tabid, colid, typestring
where
tabid - is the table id. tabid is int.
colid - is the column id. colid is int.
typestring - is the type string. It's output parameter.
typestring is nvarchar(255).
-- 다음 예제는 pubs database에서 authors 테이블의 컬럼 ID가 2인
-- 컬럼의 타입 정보를 구하는 것이다.
USE pubs
GO
declare @tabid int, @typestring nvarchar(255)
select @tabid = object_id('authors')
EXEC sp_gettypestring @tabid, 2, @typestring output
select @typestring
GO
-- 결과는 다음과 같다.
-------------------------------
varchar(40)
■ sp_MSgettools_path
sp_MSgettools_path 저장 프로시저는 SQL SERVER 2000의 툴과 유틸리티가 설치된 로컬 경로를 반환한다.
문법
sp_MSgettools_path install_path
where
install_path - is the installation path. It's output parameter.
install_path is nvarchar(260).
-- 다음 예제는 SQL SERVER 2000의 툴과 유틸리티가 설치된 로컬 경로를 리턴한다.
USE master
GO
declare @install_path NVARCHAR(260)
EXEC sp_MSgettools_path @install_path OUTPUT
select @install_path
GO
-- 결과는 다음과 같다.
------------------------------------------------------------
C:\Program Files\Microsoft SQL Server\80\Tools
■ sp_MScheck_uid_owns_anything
sp_MScheck_uid_owns_anything 저장 프로시저는 주어진 사용자가 소유자(Owner)로 있는 모든 개체의 리스트를 출력한다.
문법
sp_MScheck_uid_owns_anything uid
where uid - is the User ID, unique in this database. uid is smallint.
-- 다음 예제는 pubs database에서 사용자 ID가 1인 사용자(dbo)가
-- 소유자로 있는 모든 오브젝트를 구한다.
USE pubs
GO
EXEC sp_MScheck_uid_owns_anything 1
GO
* 주: 사용자의 ID번호는 USER_ID 함수를 통해 얻을 수 있다.
예)
SELECT USER_ID('dbo') -- dbo 에 해당하는 사용자 ID
SELECT USER_ID() - 현재 사용자의 사용자 ID
■ sp_columns_rowset
sp_columns_rowset 저장 프로시저는 컬럼의 길이, 타입, 이름 등을 포함한 매우 자세한 컬럼 정보를 반환한다.
문법
sp_columns_rowset table_name [, table_schema ] [, column_name]
where
table_name - is the table name. table_name is sysname.
table_schema - is the table schema. table_schema is sysname,
with a default of NULL.
column_name - is the column name. column_name is sysname,
with a default of NULL.
-- 예제(pubs 데이터베이스 authors 테이블의 모든 컬럼에 대한 정보를 반환)
USE pubs
GO
EXEC sp_columns_rowset 'authors'
GO
* 주 : Table_schema는 테이블은 소유자(owner)를 뜻함.
■ sp_fixindex
sp_fixindex 저장 프로시저를 사용하여 손상된 시스템 테이블을 인덱스를 새로 작성함으로써 고칠 수 있다.
문법
sp_fixindex dbname, tabname, indid
where
dbname - is the database name. dbname is sysname.
tabname - is the system table name. tabname is sysname.
indid - is the index id value. indid is int
* 주의. 이 저장 프로시저를 사용하기 전에 데이터베이스를
single user mode로 설정해야 한다.
-- 다음은 예제이다.
USE pubs
GO
EXEC sp_fixindex pubs, sysindexes, 2
GO
* 참고
관련 문서에 의하면 위의 구문으로 인덱스를 새로 생성하지 못 할 경우
DBCC 명령을 적용 해 볼것을 권하고 있다.
예) sysobjects 테이블의 넌-클러스터 인덱스
DBCC DBREPAIR(dbid, REPAIRINDEX, sysobjects, 2)
위의 구문으로는 sysindexes 혹은 sysobjects 테이블의 클러스터 인덱스는 새로 생성할 수 없다고 한다.
그리고 위의 방법으로도 인덱스가 새로 생성되지 않을 경우 새로운 database를 만들고 EM 등을 통해
모든 데이터를 옮겨 데이터베이스를 새로 구축할 것을 권장하고 있다.
■ sp_Msforeachdb
가끔 시스템상의 모든 database의 목록을 얻어 각각의 database 마다 명령을 처리해야 할 경우가 있다. 보통 이때는 커서를 이용하여 구현하게 되는데, sp_Msforeachdb 저장 프로시저를 이용하면 이런 문제가 간단히 해결된다.
다음 예제는 sp_Msforeachdb 저장 프로시저를 이용하여 SQL SERVER의 모든 database 목록을 출력하고 각각의 database마다 DBCC CHECKDB 명령을 실행하는 것이다.
EXEC sp_MSforeachdb @command1= "print '?' DBCC CHECKDB ('?')"
■ sp_Msforeachtable
가끔 현재 database에 속한 모든 테이블 목록을 얻어 각각의 테이블마다 명령을 처리해야 할 경우가 있다. 보통 이때는 커서를 이용하여 구현하게 되는데, sp_MSforeachtable 저장 프로시저를 이용하면 이런 문제가 간단히 해결된다.
다음 예제는 sp_MSforeachtable 저장 프로시저를 이용하여 현재 database 상의 모든 테이블 목록을 출력하고 각각의 테이블 마다 DBCC DBREINDEX 명령을 실행하는 것이다.
EXEC sp_MSforeachtable @command1 = "print '?' DBCC DBREINDEX ('?')"
EXEC sp_MSforeachtable 'Select Top 10 ''?'', * From ? ORDER BY [index] DESC '
■ sp_Mshelpcolumns
sp_Mshelpcolumns 저장 프로시저는 컬럼들의 길이, 타입, 이름, 계산된 칼럼 여부 등을 정보를 포함한 완전한 테이블 스키마 정보를 리턴한다.
문법
sp_MShelpcolumns tablename [, flags] [, orderby] [, flags2]
Where
tablename - is the table name. tablename is nvarchar(517).
flags - flags is int, with a default of 0.
orderby - orderby is nvarchar(10), with a default of NULL.
flags - flags2 is int, with a default of 0.
-- pubs database에서 authors 테이블의 컬럼 정보를 얻기 위해서는
-- 아래의 스크립트를 실행하면 된다.
USE pubs
GO
EXEC sp_MShelpcolumns 'authors'
GO
* 주:
앞서 설명한 sp_columns_rowset 도 테이블의 컬럼 정보를 리턴하지만,
sp_Mshelpcolumns 의 경우 EM에서 테이블 디자인 화면을 통해 확인 수 있는 값들에
가까운 정보를 리턴한다.
■ sp_Mshelpindex
sp_Mshelpindex 저장 프로시저는 테이블 이름이 주어지면 해당 테이블에 존재하는 인덱스의 이름, 상태, 채우기 비율(fill-factor), 인덱스 컬럼 이름, 파일 그룹 등의 정보를 얻을 수 있다.
문법
sp_MShelpindex tablename [, indexname] [, flags]
Where
tablename - is the table name. tablename is nvarchar(517).
indexname - is the index name. indexname is nvarchar(258),
with a default of NULL.
flags - flags is int, with a default of NULL.
--다음 예제는 pubs database에서 authors 테이블의 인덱스 정보를 구하는 것이다.
USE pubs
GO
EXEC sp_MShelpindex 'authors'
GO
■ sp_Mshelptype
sp_Mshelptype 저장 프로시저는 시스템 데이터 타입과 사용자 정의 데이터 타입에 대한 유용한 정보를 반환한다.
문법
sp_MShelptype [typename] [, flags]
where
typename - is the type name. typename is nvarchar(517),
with a default of NULL.
flags - flags is nvarchar(10), with a default of NULL.
-- 다음 예제는 pubs database의 모든 시스템 데이터 타입과
-- 사용자 정의 타입에 대한 정보를 구하는 것이다.
USE pubs
GO
EXEC sp_MShelptype
GO
■ sp_Msindexspace
sp_Msindexspace 저장 프로시저는 특정 테이블에 속한 모든 인덱스의 크기(kb)를 리턴한다.
문법
sp_MSindexspace tablename [, index_name]
Where
tablename - is the table name. tablename is nvarchar(517).
index_name - is the index name. index_name is nvarchar(258),
with a default of NULL.
-- 예제: pubs database의 authors 테이블의 인덱스를 정보를 구함
USE pubs
GO
EXEC sp_MSindexspace 'authors'
GO
결과는 다음과 같다.
Index ID Index Name Size (KB) Comments
1 UPKCL_auidind 16 Size excludes actual data.
2 aunmind 16 (None)
■ sp_Mskilldb
sp_Mskilldb 저장 프로시저는 데이터베이스를 서스팩트 모드(suspect mode)로 설정하고 DBCC DBREPAIR 명령을 이용하여 데이터베이스를 삭제한다. sp_Mskilldb 저장 프로시저는 반드시 master database 컨텍스트 상에서 실행되어야 한다. 본 저장 프로시저의 사용에는 각별의 주의가 요망된다.
문법
sp_MSkilldb dbname
where dbname - is the database name. dbname is nvarchar(258).
예제) pubs database를 삭제한다.
USE master
GO
EXEC sp_MSkilldb 'pubs'
GO
* 주:
sp_Mskilldb 저장 프로시저를 이용하여 데이터베이스를 삭제할 경우 실제 디스크 드라이브 상에
저장되어 있는 데이터 파일과 로그파일까지 모두 삭제되어 복구가 불가능 하다.
따라서 본 저장 프로시저의 이용에는 각별의 주의가 필요하다.
■ sp_Mstablespace
sp_Mstablespace 저장 프로시저는 테이블의 행의 수, 데이터 사이즈(space), 인덱스 사이즈 정보를 반환한다.
문법
sp_MStablespace name [, id]
Where
name - is the table name. name is nvarchar(517).
id - id is int, with a default of NULL.
-- 예: pubs database에서 authors 테이블의 크기를 구한다.
USE pubs
GO
EXEC sp_MStablespace 'authors'
GO
-- 결과는 다음과 같다.
Rows DataSpaceUsed IndexSpaceUsed
----------- ------------- --------------
23 8 32
■ sp_tempdbspace
sp_tempdbspace 저장 프로시저는 tempdb database의 총 크기와 사용된 용량(space used)을 얻는데 쓰인다. 이것은 아무 인수없이 사용된다.
문법
sp_tempdbspace
-- 예제
EXEC sp_tempdbspace
다음은 결과이다.
database_name database_size spaceused
------------- ----------------------- -----------------------------
tempdb 9.750000 .562500
■ sp_who2
sp_who2 저장 프로시저는 SQL SERVER 2000 현재 사용자 정보와 sp_who의 결과가 비슷한 프로세스 정보를 리턴한다. 하지만 sp_who 결과보다 더욱 자세한 내용을 포함하고 있다. sp_who2는 sp_who에 비해 CPUTime, DiskIO, LastBatch 그리고 ProgramName 등의 추가적인 정보를 제공한다.
문법
sp_who2 [loginame]
Where
loginame - the user's login name. If not specified, the procedure
reports on all active users of SQL Server.