kay
조회 수 2250 추천 수 0 댓글 0
?

단축키

Prev이전 문서

Next다음 문서

크게 작게 위로 아래로 댓글로 가기 인쇄
?

단축키

Prev이전 문서

Next다음 문서

크게 작게 위로 아래로 댓글로 가기 인쇄


문서화 되지 않은 시스템 저장프로시저들을 정리 해놓은 것이다.
이 문서는 아주 오래전에 개인 비밀 카페에 있던 글을 정리하는 것이다.
그래서 출처가 어디인지는 잘 모르겠다. -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.

?

List of Articles
번호 제목 글쓴이 날짜 조회 수
48 Deletes nodes from an XML instance. 특정 노드 삭제 kay 2015.09.24 294
47 unix_timestamp 을 MSSQL 상에서 YYYY-MM-DD hh:mm:ss 포맷으로 상호 변환 kay 2015.08.26 458
46 MSSQL 쿼리로 CSV 파일 데이터 업로드 하는 방법 ( BULK INSERT ) kay 2015.07.30 701
45 SQL Server Management Studio(SSMS) 메모리 점유율 문제 file kay 2015.07.13 539
44 ASP ADODB 연결 상태 체크 kay 2015.04.13 308
43 ssms Ctrl+E 단축키로 쿼리 실행하기 file kay 2015.04.03 1148
42 [펌] 개인정보 보호를 위한 SQL Server 보안 가이드 file kay 2014.08.12 1093
41 MS-SQL 에서 연결된 서버 (Linked Server )에 MY-SQL DataBase 서버 등록하기 file kay 2014.07.15 3385
40 MS-SQL 랜덤정렬 " NEWID() " kay 2014.04.23 4242
39 ORDER BY CASE WHEN 정렬하기 kay 2014.03.12 4460
38 MS-SQL 설치 후 sa 로그인 활성화 file kay 2014.02.20 3006
37 특정 사용자에게 특정 테이블 , 뷰테이블 등.. 권한주기 file kay 2014.02.12 6370
36 FOR XML을 이용해서 SQL 데이터로 XML 생성하기 file kay 2013.12.18 2806
35 Collation 충돌 해결 및 Collation 변경 kay 2013.12.17 4665
34 SQL Server 상태 모니터링 kay 2013.11.14 5022
» [담아온글] 문서화 되지 않은 시스템 저장프로시저 kay 2013.10.29 2250
32 단어 자동 완성(IntelliSense) 옵션 활성화 및 해제하기 file kay 2013.10.08 5299
31 "sys.dm_exec_connections" SQL 서버 connection 정보 확인하기 kay 2013.09.09 2458
30 테이블 존재 확인하기 1 kay 2013.09.09 2558
29 단순 DB 온라인/오프라인 상태 체크하기 kay 2013.08.27 2734
Board Pagination Prev 1 2 3 Next
/ 3
CLOSE