MS-SQL 기본 정리

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