MS-SQL 유용한 명령어

** SQL에러코드 메세지 찾기

SELECT * FROM sys.messages where language_id=1042 and message_id = 에러코드

**DB 용량확인

sp_spaceused

select top 10

 table_name = convert(varchar(30), min(o.name))

 , table_size = convert(int, ltrim(str(sum(reserved) * 8.192 / 1024., 15, 0))), UNIT = 'MB'

from sysindexes i

 inner join sysobjects o on (o.id = i.id)

where i.indid in (0, 1, 255)

 and o.xtype = 'U'

group by i.id

order by 2 desc

** 남은 하드 용량 확인

master.dbo.xp_fixeddrives

** 프로시저 전체 보기(스크립트 포함)

select ROUTINE_NAME, ROUTINE_DEFINITION   

  from information_schema.routines   

 where routine_type = 'PROCEDURE';

** DB Identity 초기화

DBCC CHECKIDENT('[Table]', RESEED, 0)

** SQL 메모리점유 퍼센트순위 

select

       PERCENT_RANK() over (order by requiredTime) as ranking, requiredTime

from SURVEY.dbo.T_SamplingList_2014000028

where requiredTime is not null