1. 테이블 구조 보기(DESC)
Exec sp_help [UserTableName]
2. Stored Procedure 목록 보기 / 검색
SELECT * FROM sysobjects
3. Stored Procedure에서 select후의 레코드셋의 개수를 알고 싶은 경우
SELECT @@rowcount
4.Stored Procedure 삭제 하기
(1) 일반 삭제
Drop Proc [StoredProcedureName]
(2) 존재 검사후 삭제
IF exists (select * from sysobjects where name='sp_extract_rand_problem')
Begin
Drop Proc sp_extract_rand_problem
End
Go
5. Stored Procedre 작성중 쿼리에 변수 사용 못하는 경우
varchar형 변수에 쿼리 저장한후 EXEC함수로 실행한다
예)
Declare @sql Varchar(200)
SET @sql = 'SELECT TOP ' + CONVERT(varchar(5),@pagesize) + ' * FROM MyBoard '
SET @sql = @sql + 'WHERE board_idx not in (SELECT TOP ' + CONVERT(varchar(5),(@GotoPage-1) *@pagesize)
SET @sql = @sql + ' board_idx FROM MyBoard ORDER BY b_ref desc, b_step) order by b_ref desc, b_step'
--print @sql
EXEC (@sql)
6. Query Analyzer 에서 sql문 실행하면 메시지 텝에 (30 row(s) affected) 같은 메시지가 나오지 못하게 하기 #
set NoCount On
7. Local Variable로 Text 타입을 사용 못하므로 varchar형으로 대체해서 사용함
8. Stored Procedure 내에서 동적 쿼리 사용하는 예
Create Proc sp_dynamic_sql
@strTableName varchar(20) ,
@intBoard_Idx int ,
@prev_idx int OUtput
As
Begin
--꼭 nvarchar 이어야 함
declare @stmt as nvarchar(400)
declare @params as nvarchar(500)
--stmt와 paranms에 임시 output 변수를 사용하면 됨, 여러개의 파라미터 경우는 "," 로 구분한다.
set @stmt = 'Select @idx = Min(board_idx) from ' + @strTableName + ' where board_idx > ' + CONVERT(VARCHAR(10),@intBoard_idx)
set @params = '@idx as int OUTPUT' -- OUTPUT 키워드에 주의
exec sp_executesql @stmt, @params, @idx = @prev_idx OUTPUT
End
9. 테이블 목록(MySql의 show tables기능)
exec sp_tables
10. 데이타 베이스 목록및 정보(MySql의 show database 기능)
exec sp_databases / exec SP_HELPDB
11. FK 관계가 설정된 값 변경하기(참고: http://www.sqler.pe.kr/sql2k/63.asp )
참조받는 쪽의 값을 변경하는 경우 참조하는쪽 테이블에 CASCADE 설정 필요함
--ex(sql.pe.kr에서 발췌)- --tempdb를 사용한다. USE tempdb GO
--학과 테이블을 생성한다. CREATE TABLE 학과( 학과ID varchar(5) primary key , 학과명 varchar(10) not null ) GO
--학생 테이블을 생성한다. --UPDATE는 새로운 CASCADE 옵션으로, --DELETE는 아무 처리도 하지않게(기본 옵션)으로 처리 한다. CREATE TABLE 학생( 학번 int primary key , 이름 varchar(12) not null , 학과ID varchar(5) FOREIGN KEY REFERENCES 학과 (학과ID) ON UPDATE CASCADE ON DELETE NO ACTION ) GO
--학과에 정보를 삽입 한다. INSERT 학과 VALUES ('ITE', '정보통신') INSERT 학과 VALUES ('EC', '전자계산') GO
--학생 테이블에 참조하는 학과(학과ID)컬럼의 값으로 삽입한다. INSERT 학생 VALUES (941234, '코난', 'ITE') INSERT 학생 VALUES (950083, '세린', 'EC') GO
--데이터를 조회한다. SELECT * FROM 학과 SELECT * FROM 학생
--참조제이 걸려 있는 학과(학과ID) 에 --CASCADE 옵션이 걸려 있는 UPDATE를 수행한다. UPDATE 학과 SET 학과ID = 'AAA' WHERE 학과ID = 'ITE' GO
--데이터를 조회한다. CASCADE 옵션이 잘 동작한다. SELECT * FROM 학생 SELECT * FROM 학과
--삭제를 해 보자. DELETE 학과 WHERE 학과ID = 'EC' --참조 제약으로 삭제가 불가 하다는 에러메세지가 나온다.
12. 커서 이용하기(참고: http://www.sqler.pe.kr mssql2000강좌)
dbo.student_tbl에 있는 데이타를 jls.member_tbl에 인서트 하는 루틴
declare cur_test CURSOR
For select student_id,pwd,'S' from dbo.student_tbl
--For select teacher_id ,pwd,'T' from dbo.teacher_tbl
Open cur_test
declare @member_id varchar(20),
@passwd varchar(20),
@member_type varchar(1),
@sql varchar(200)
FETCH NEXT FROM cur_test INTO @member_id, @passwd,@member_type
WHILE (@@FETCH_STATUS = 0)
BEGIN
-- Set @member_id = 't'+cast(@member_id as varchar(20))
set @sql = 'insert into jls.member_tbl (member_id,passwd,member_type)values ('''+@member_id+''', '''+@passwd+''','''+@member_type+''' )'
EXEC (@sql)
FETCH NEXT FROM cur_test INTO @member_id, @passwd,@member_type
END
close cur_Test
deallocate cur_test
ps) 커서 선언시 테이블 조건에 사용할 값에 변수를 직접 사용할 수는 있으나 테이블 명에 직접 변수 사용은 불가능 하므로 다음처럼 해서 커서를 선언 할 수 있다.
Declare @SQL VARCHAR(400)
SET @SQL = 'declare cur_problem CURSOR '
SET @SQL = @SQL + 'For SELECT problem_num,a.test_cont_info_id FROM '+@strTestTableName+' As a, test_cont_info_tbl As B WHERE course_code='+@strCourseCode
EXEC(@SQL)
13. 데이터 베이스 계정 정보
exec sp_helplogins <--- 데이타베이스에 상관없이 로긴할 수 있는 넘들 다 보여준다.
exec sp_helpuser <--- 현재 데이타베이스의 유저들을 보여준다.
14. Procedure 에서 변수에 SELECT 결과 할당하기
(변수 이용하는 동적 쿼리는 5, 8 번 참고)
Declare @name VARCHAR(30)
SELECT @name FROM member WHERE id='user_id' Print @name
15. 컬럼 이름 변경(column name)
test테이블의 name 컬럼명을 user_name으로 변경
EXEC sp_rename 'test.name', 'user_name', 'COLUMN'
16. column이 null이면 기본 값을 주는 함수
ISNULL ( check_expression , replacement_value )
17. sp 작성시 유의점
sp 작성시에는 상황에 따라서 적당한 return 값을 갖도록 한다. 그래야 sp 사용시 발생하는 문제점을 빨리 알 수 있다.
18. IDENTITY 초기화
DBCC CHECKIDENT (t_mark_histw_tbl, RESEED, 0)//table_name, RESEED, 초기값
19. ALTER문 정리
ㄱ. column 속성 변경 : ALTER TABLE MyTable ALTER COLUMN NullCOl NVARCHAR(20) NOT NULL
20. Index에 따르지 않고 정렬하기
상황 : study_contents_id가 93,92,94인 레코드를 조회 하는데 결과의 정렬이 93,92,94로 되기를 원하지만 study_contents_id가 index여서 92,93,94 순으로 오름차순 정렬이 된다.
해결책 : study_contents_id 값에 따라서 정렬하고자 하는 순서를 order_seq에 부여한다. 그리고 order_seq를 정렬조건으로 사용한다.
SELECT study_contents_id ,
CASE study_contents_id
WHEN 93 THEN 1
WHEN 92 THEN 2
WHEN 94 THEN 3
END AS order_seq
,course_code, FROM study_contents_tbl WHERE study_contents_id IN (93,92,94,) ORDER BY order_seq ASC
21. 로그인 이름이 비어있는 사용자 정보 수정하기.이상하게 EM에서는 수정이 안됨
(1). 로그인 계정(로그인 이름) 생성 : sp_addlogin 생성할유저명, 비빌번호, 디폴트DB
예) EXEC sp_addlogin 'con_user', 'con_pass', 'test_db'
(2). 데이터 베이스 로그인 계정 변경 : sp_change_users_login 액션, 데이터베이스의사용자이름
예) EXEC sp_change_users_login 'Update_One', 'con_user', 'con_user'
관련 게시물 : http://sqler.pe.kr/web_board/view_list.asp?id=45680&read=45&pagec=&found=is&part=myboard1&ser=yes
22. 객체 생성 스크립트
sp_helptext 함수를 이용하거나 syscomments 시스템 테이블을 이용하면 됨.
ㄱ.프로시져
select text from syscomments where id in (select id from sysobjects where xtype='p' and name NOT LIKE 'dt%')
ㄴ.트리거
select text from syscomments where id in (select id from sysobjects where xtype='tr' and name NOT LIKE 'dt%')
ㄷ.뷰
select text from syscomments where id in (select id from sysobjects where xtype='v' and name NOT LIKE 'sys%')
23. 서버에 생성된 데이터 베이스 정보
--서버단위
--데이터베이스 서버 정보
select srvname, srvproduct, providername, datasource from master..sysservers
--데이터베이스 로그 공간
DBCC sqlperf(logspace) /*각 데이터 베이스의 트랜젹션 로그 공간 */
--각 데이터베이스 정보(데위터베이스명, 생성일, 파일)
select name, crdate, filename from master..sysdatabases
--데이터베이스 단위
--sp_spaceused , DBCC sqlperf(logspace)를 이용
declare cur_name CURSOR
For select name from master..sysdatabases
Open cur_name
declare @name varchar(100)
declare @command varchar(200)
Fetch Next From cur_Name Into @name
while(@@FETCH_STATUS =0)
begin
print @name
set @command = 'use ['+ @name+'] exec sp_spaceused' --use구문과 프로시져가 동일한exec에서 실행 되도록 해야함
exec(@command)
/*sp_spaceused 각 데이터 베이스의 사용 공간*/
FETCH NEXT FROM cur_name INTO @name
end
close cur_name
deallocate cur_name
--데이터베이스 단위
--테이블 목록
--exec sp_tables [dbname]
select * from INFORMATION_SCHEMA.TABLES
--테이블 단위
--컬럼 목록 및 자료형
--exec sp_columns [tablename]
select * from INFORMATION_SCHEMA.COLUMNS where table_name = 'blog_data'
24. 데이터베이스 풀 백업
--pubs데이터 베이스를 e:\backup\backup-file로 백업하는 명령
--백업 파일 경로 지정시 드라이브의 루트를 설정하면 장치 오류가 발생하여 백업이 되지 않음으로
--임의의 디렉토리를 만들어서 사용한다.
BACKUP DATABASE pubs TO DISK = 'e:\backup\backup-file' WITH INIT |
