[스크랩] 문서화되지 않은 시스템 저장 프로시저

 

http://ddoung2.tistory.com/56

 

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


문서화 되지 않은 시스템 저장프로시저들을 정리 해놓은 것이다.
이 문서는 아주 오래전에 개인 비밀 카페에 있던 글을 정리하는 것이다.
그래서 출처가 어디인지는 잘 모르겠다. -0-;; 그때 당시 비밀카페인지라 출처까지 적어 놓지는 않았다.



문서화 되지 않은 확장프로시저나 저장프로시저는 다음 버전에서 삭제될 가능성이 있기에 알아서 사용해야 한다고 한다. 또한, 문서화 되지 않은 명령어를 쓰다가 문제가 생겼을시 MS는 어떠한 책임도 지지 않는다고 한다니 음...

 

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.

 

 

http://yseven95.egloos.com/viewer/959071

 

여기서는 문서화되어 있지는 않지만 사용하면 유용한 확장 스토어드 프로시져를 소개하고자 한다. SQL Server 7.0 및 SQL Server 2000 모두에서 잘 동작하는 것들이다. 레지스트리 관련된거 말고 ㅋㅋㅋ

sp_MSgetversion

이 확장 스토어드 프로시져는 SQL Server의 현재 버전을 알기 위해 사용된다. SQL Server의 현재 버전을 알려면 다음과 같이 실행하면 된다.

EXEC master..sp_MSgetversion

<주의> SQL Server의 현재 버전을 알기 위한 더 좋은 방법은 select문을 사용하는 것이다:

select @@version

xp_dirtree

이 확장 스토어드 프로시져는 전달된 디렉토리의 모든 서브디렉토리 리스를 얻기 위해 사용된다. 가령, C:\MSSQL7 디렉토리의 모든 서브디렉토리 리스트를 얻으려면 다음과 같이 하면 된다:

EXEC master..xp_dirtree 'C:\MSSQL7'

xp_enum_oledb_providers

이 확장 스토어드 프로시져는 모든 OLE DB provider를 얻기 위해 사용된다. xp_enum_oledb_providers는 Provider Name, Parse Name 및 Provider Description을 결과로 돌려 준다.

모든 OLE DB provider를 얻으려면 다음과 같이 하면 된다:

EXEC master..xp_enum_oledb_providers

xp_enumcodepages

이 확장 스토어드 프로시져는 모든 코드 페이지, 문자 집합 및 그 설명를 얻기 위해 사용 된다. 다음은 그 예이다.

EXEC master..xp_enumcodepages

xp_enumdsn

이 확장 스토어드 프로시져는 모든 시스템 DSN 리스트 및 그 설명을 얻기 위해 사용된다. 시스템 DSN 리스트를 얻으려면 다음과 같이 하면 된다:

EXEC master..xp_enumdsn

xp_enumerrorlogs

이 확장 스토어드 프로시져는 마지막 변경일과 함께 모든 에러 로그 리스트를 얻기 위해 사용된다. 다음은 그 예이다.

EXEC master..xp_enumerrorlogs

xp_enumgroups

이 확장 스토어드 프로시져는 윈도 NT 그룹과 그 설명을 얻기 위해 사용된다.

다음은 그 예이다:

EXEC master..xp_enumgroups

xp_fileexist

이 확장 스토어드 프로시져는 디스크 상에 특정 파일이 존재하는지를 알려 준다.

문법은 다음과 같다:

EXECUTE xp_fileexist filename [, file_exists INT OUTPUT]

만일 c:에 boot.ini 파일이 있는지 알아보려면 다음과 같이 하면 된다:

EXEC master..xp_fileexist 'c:\boot.ini'

xp_fixeddrives

이 확장 스토어드 프로시져는 로컬 PC에 붙어 있는 모든 디스크 리스트와 각 하드디스크의 남은 공간을 알려 준다. 다음은 사용 방법이다:

EXEC master..xp_fixeddrives

xp_getnetname

이 확장 스토어드 프로시져는 현재 연결되어 있는 SQL Server의 WINS 네임을 알려 준다. 다음은 그 예이다:

EXEC master..xp_getnetname

xp_readerrorlog

이 확장 스토어드 프로시져는 에러 로그 파일 내용을 알려 준다. 에러 로그 파일 위치는 디폴트로 "C:\MSSQL7\Log" 디렉토리이다. 사용 방법은 다음과 같다:

EXEC master..xp_readerrorlog

xp_regdeletekey

이 확장 스토어드 프로시져는 레지스트리 키를 삭제할 것이다. 그러므로 이 프로시져를 사용할 때는 주의를 기울여야 한다.

문법:

EXECUTE xp_regdeletekey [@rootkey=]'rootkey',

[@key=]'key'

만일 'HKEY_LOCAL_MACHINE'의 'SOFTWARE\Test' 키를 삭제하려면 다음과 같이 하면 된다:

EXEC master..xp_regdeletekey

@rootkey='HKEY_LOCAL_MACHINE',

@key='SOFTWARE\Test'

xp_regdeletevalue

이 확장 스토어드 프로시져는 레지트스리 키의 특정 값을 삭제한다. 사용시 주의를 기울여야 한다.

문법 :

EXECUTE xp_regdeletevalue [@rootkey=]'rootkey',

[@key=]'key',

[@value_name=]'value_name'

만일 'HKEY_LOCAL_MACHINE'의 'SOFTWARE\Test' 키에서 'TestValue' 값을 삭제하려면 다음과 같이 하면 된다:

EXEC master..xp_regdeletevalue

@rootkey='HKEY_LOCAL_MACHINE',

@key='SOFTWARE\Test',

@value_name='TestValue'

xp_regread

이 확장 스토어드 프로시져는 레지스트리 값을 읽어 들이기 위해 사용된다.

문법 :

EXECUTE xp_regread [@rootkey=]'rootkey',

[@key=]'key'

[, [@value_name=]'value_name']

[, [@value=]@value OUTPUT]

만일 'HKEY_LOCAL_MACHINE'의 'SOFTWARE\Test' 키에서 'TestValue' 값을 @test 변수로 읽어들이려면 다음과 같이 하면 된다:

DECLARE @test varchar(20)

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',

@key='SOFTWARE\Test',

@value_name='TestValue',

@value=@test OUTPUT

SELECT @test

xp_regwrite

이 확장 스토어드 프로시져는 레지스트리 기록을 위해 사용된다.

문법 :

EXECUTE xp_regwrite [@rootkey=]'rootkey',

[@key=]'key',

[@value_name=]'value_name',

[@type=]'type',

[@value=]'value'

만일 'HKEY_LOCAL_MACHINE'의 'SOFTWARE\Test' 키에 있는 'TestValue' 값에 'Test'라고 기록하고 싶다면 다음과 같이 하면 된다:

EXEC master..xp_regwrite

@rootkey='HKEY_LOCAL_MACHINE',

@key='SOFTWARE\Test',

@value_name='TestValue',

@type='REG_SZ',

@value='Test'

xp_subdirs

이 확장 스토어드 프로시져는 전달된 디렉토리의 서브디렉토리 리스트를 돌려 준다. xp_dirtree와는 달리 xp_subdirs는 detph=1인 디렉토리들만 반환한다.

다음과 같이 사용한다:

EXEC master..xp_subdirs 'C:\MSSQL7'

네이버 뒤지다가 퍼왔다..
말그대로 불펌이지 ㅋㅋㅋ