ms-sql 명령어

blog.daum.net/magixtech/1906523

목차
1-1 DB정보 알기
1-2 DBCC
1-3 로그백업
1-4 데이타베이스 백업
1-5 변수의 크기
1-6 문자열 변형
1-7 UNION 사용
1-8 테이타입력
1-9 데이타베이스 의존성확인
1-10 동적쿼리
1-11 CASE
1-12 동적테이블
1-13 테스트 테이타 입력
1-14 블로킹 모니터링
1-15 function
1-16 전표번호 생성
2-1 테이블생성
2-2 identity()함수
2-3 매월 첫날과 마지막날
2-4 원격쿼리

--------------------------------------------------------------------------------

1-1 DB정보 알기

--시스템에 생성된 테이블 보기
select * from sysobjects where type='U'

--버전알아내기
select @@version

--서비스 실행상태보기
net start   -- 현재 시작되어 있는 서비스
net start  mssqlserver
net start mssql$inst

net stop sqlagent

 

1-2 DBCC

-- 데이타베이스 용량 줄이기
dbcc shrinkdatabase(testdb,10)

-- 데이타베이스 파일 줄이기 10M로 줄이기
dbcc shrinkfile(testdb,10)

--테이블 검사
dbcc checktable

dbcc checkdb

--테이블, 색인의 조각비율
dbcc showcontig

--테이블 색인을 재작성 filefactor 20
dbcc dbreindex(test,'',20)

dbcc shwo_statistics

---지정한 시스템 테이블의 일관성과 테이블간의 일치하지 않는 내용 검사
dbcc checkcatalog test

--I/O량 확인
set statistics io on

 

1-3 로그백업

--로그백업
backup log pubs to pubslog

--로그를 그냥 비울때
backup log pubs with no_log
backup log pubs with truncate_only

--로그 점유율
dbcc sqlperf(logspace)

 

1-4 데이타베이스 백업

--백업
backup database pubs to disk='c:\temp\pubs' with init

--복원
restore database pubs to disk='c:\temp\pubs'

 

1-5 변수의 크기

bigint  8바이트 정수
int  4바이트 정수
smallint  2바이트 정수
tinyint  1바이트 정수
bit  0,1
float  부동소숫점(15자리)
real  부동소숫점(7자리)
datetime  8바이트 날짜시간
smalldatetime 4바이트 날짜시간
char, varchar 8000자이하
nchar,nvarchar 4000자 이하 유니코드
text  8000자 이상
Ntext  4000자 이상 유니코드
binary, varbinary  8000 바이트 이하 이진
image  8000 바이트 이상 그림
money  8바이트 화폐
smallmoney 4바이트 화폐
table  테이블자체
sql_variant text, ntext, image, timestamp 를 제외한 모든 자료형 
cursor  커서
timestamp 유일한수

 

1-6 문자열 변형

convert(char(30), title)  --30글자짜리 문자열로 변형
substring(title,1,30)   -- 전체문장중 30글자만 표시하고 나머지는 공백으로 남겨둠 
cast(title as char(30))  -- 문자열변경

replicate(0,5)   --0을 5번 반복

select getdate()
--날짜
select convert(varchar(30), getdate(),111)
select convert(varchar(30), getdate(),112)
--시간
select convert(varchar(30), getdate(),108)

-- 앞자리 0으로 채우기
create function fn_FillStr
(
 @No bigint,
 @Length int,
 @ReplaceChar char(1)
)
returns varchar(8000)
as
begin
 return 
 ( 
  replicate(@ReplaceChar, @Length - len(convert(varchar(20), @No))) + Convert(varchar(20), @No)
 )
end 
go
select dbo.fn_FillStr(123,6,'0')

--3자리마다 콤마찍기
create function fn_CommaFormat
(
 @strNumber varchar(30),
 @chrComma char(1)
)
returns varchar(50)
as
begin
 if len(@strNumber) >3
  set @strNumber = dbo.fn_CommaFormat(substring(@strNumber,1,len(@strNumber)-3),',') + @chrComma + Right(@strNumber,3)
  return(@strNumber)
end
go
select dbo.fn_CommaFormat(12345678,',')
go

 

1-7 UNION 사용

UNION ALL -- 중복데이터도 모두 가져옴
UNION  -- 중복데이터는 삭제

 

1-8 테이타입력

--새로운 테이블에 입력 : 색인이나 키는 복사하지 않는다.
select * into test2
from t1

-- 이미 존재하는 테이블에 테이터 입력
insert test2
 select * 
 from test1

 

1-9 데이타베이스 의존성확인

sp_help test
sp_helpconstraint test
sp_depends test

sp_helpindex test

--모든 제약 중지
alter table test 
nocheck constraint all

--모든 제약 시작
alter table test
check constraint all

 

1-10 동적쿼리

declare @sql varchar(255)
set @sql = 'select count(*) from '
set @sql = @sql + 'titles'

exec(@sql)

 

1-11 CASE

select ... ,
 case whe sex=1 then '남' else '여' end as '성별'
from ...

 

1-12 동적테이블

declare @i int, @sql varchar(1000)
set @i=0
while @i < 30
begin
 set @i = @i + 1
 set @sql = 'create table a' + convert(varchar(3),@i) + ' ('
 set @sql = @sql + 'id int)'
 select @sql
 exec (@sql)
end
go

select name from sysobjects where type='U' and like 'a%' order by name

 

1-13 테스트 테이타 입력

--테이블만들기
use tempdb
drop table test
go
create table test
(
 id int indentity,
 name char(80) default 'default name',
 date smalldatetime default getdate()
)
create clustered index test_idx
on test(id)
go

--실행코드
set nocount on

declare @i smallint
set @i=0

while @i < 10000
begin
 set @i=@i+1
 insert test default values
end

set nocount off

select max(id() from test --결과확인

 

1-14 블로킹 모니터링

sp_who
sp_block
kill 51
dbcc inputbuffer (52)

-- 분산트랜젝션 처리를 하는 도중 오류가 발생하면 자동적으로 롤백처리
set xact_abort on

 

1-15 function

create function fn_test(@id varchar(4)) return table
as
return
(
 select * from where id=@id
)

--사용
select * from dbo.fn_test('123')

 

1-16 전표번호 생성

--날짜와 번호로 생성
declare @OrderNum bigint
--select @OrderNum = max(OrderNum) from orders
set @OrderNum=2

if @OrderNum is null or @OrderNum = ''
 set @OrderNum = substring(convert(char(8), getdate(),112),1,8) + '00001'
else
 set @OrderNum = substring(convert(char(8), getdate(),112),1,8) +
 right(replace(str(convert(int,right(@OrderNum,5))+1), ' ','0'),5)

select @OrderNum

 

2-1 테이블생성

create database test on primary
(
 NAME = N'test_data',
 FILENAME = N'c:\temp\test_Data.MDF',
 SIZE=5,
 MAXSIZE=100,
 FILEGROWTH = 10%
)
log on
(
 NAME = N'test_log',
 FILENAME = N'c:\temp\test_Log.MDF',
 SIZE=2,
 FILEGROWTH = 1MB
)

 

2-2 identity()함수

select indentity(int,1,1) as Num, Name
into #test
from test

 

2-3 매월 첫날과 마지막날

declare @CurrDate datetime
set @CurrDate = getdate()
select 
dateadd(dd, -1*day(@CurrDate)+1, @CurrDate) as 시작일,
dateadd(mm, 1, @CurrDate) - day(@CurrDate) as 마지막일

 

2-4 원격쿼리

select * 
from openquery([killer\sql2000],'select * from pubs.dbo.sales')

select * 
from [killer\sql2000].pubs.dbo.sales