문서화 되지 않은 시스템 저장 프로시저

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.