'프로그래밍/MS_SQL' 카테고리의 글 목록

프로그래밍/MS_SQL

mysql procedure

GOHA 2022. 8. 2. 16:06

2022. 8. 2. 16:06

728x90

-- 같은이름의프로시저있으면삭제

DROP PROCEDURE IF EXISTS cursor_test;

DELIMITER $$

CREATE PROCEDURE cursor_test()

BEGIN

-- 반복문변수선언

DECLARE done INTEGER DEFAULT 0;

-- 커서에서사용할변수선언

DECLARE v_aaa varchar(10);

DECLARE v_bbb varchar(10);

-- 커서에서사용할Select 테이블선언

DEClARE openCursor CURSOR FOR SELECT aaa, bbb FROM testTable;

-- 반복문핸들러선언

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;

-- 커서오픈

OPEN openCursor;

-- 반복문시작

read_loop: LOOP

-- 커서에서데이터가져옴

FETCH openCursor INTO v_aaa, v_bbb;

-- 반복문종료시조건

IF done THEN

LEAVE read_loop;

END IF;

-- 조건에맞는데이터데이터찾아서변수에임시저장

Select @codeName := codeName From codeTable Where codeCode = v_bbb;

-- 테이블에저장

insert into `insertTable` (`iCode`, `iCodeName`) values(v_bbb, @codeName);

-- 반복문끝

END LOOP read_loop;

-- 커서해제

CLOSE openCursor;

END$$

DELIMITER ;

-- 프로시저실행

CALL cursor_test();

728x90

'프로그래밍 > MS_SQL' 카테고리의 다른 글

MSSQL 테이블 COMMENT 보기 (0)	2015.04.29
MSSQL IDENTITY 값 재 설정 (0)	2015.04.27
MSSQL 자동 증가 시드값 가져오기 (0)	2015.04.27
sqlexpress 백업 및 복원 (0)	2015.04.24
mssql 파일 크기 줄이기 (0)	2015.04.14

MSSQL 테이블 COMMENT 보기

GOHA 2015. 4. 29. 13:27

2015. 4. 29. 13:27

728x90

-- 테이블 정보 보기

SELECT * FROM sysobjects WHERE xtype='U';

SELECT * FROM INFORMATION_SCHEMA.TABLES;

SELECT * FROM ::FN_LISTEXTENDEDPROPERTY(NULL, 'User','dbo','table', null, NULL, NULL) order by objname

SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', '[테이블명]', 'column', default)

728x90

저작자표시 비영리 변경금지

'프로그래밍 > MS_SQL' 카테고리의 다른 글

mysql procedure (0)	2022.08.02
MSSQL IDENTITY 값 재 설정 (0)	2015.04.27
MSSQL 자동 증가 시드값 가져오기 (0)	2015.04.27
sqlexpress 백업 및 복원 (0)	2015.04.24
mssql 파일 크기 줄이기 (0)	2015.04.14

MSSQL IDENTITY 값 재 설정

GOHA 2015. 4. 27. 14:59

2015. 4. 27. 14:59

728x90

identity 값 확인

> SELECT @@IDENTITY

identity 값 자동 설정, 끄기

> SET IDENTITY_INSERT 테이블 ON

> SET IDENTITY_INSERT 테이블 OFF

identity 값 재설정

> DBCC CHECKIDENT(테이블 , reseed, 번호)

identity 값을 현재 값 중 최대 컬럼값으로 설정

> DBCC CHECKIDENT(테이블 , reseed )

[출처] mssql identity 값 재설정 하기|작성자 제니

728x90

저작자표시 비영리 변경금지

'프로그래밍 > MS_SQL' 카테고리의 다른 글

mysql procedure (0)	2022.08.02
MSSQL 테이블 COMMENT 보기 (0)	2015.04.29
MSSQL 자동 증가 시드값 가져오기 (0)	2015.04.27
sqlexpress 백업 및 복원 (0)	2015.04.24
mssql 파일 크기 줄이기 (0)	2015.04.14

MSSQL 자동 증가 시드값 가져오기

GOHA 2015. 4. 27. 14:41

2015. 4. 27. 14:41

728x90

MS SQL 자동 증가 시드값 가져오는 방법

1. IDENT_CURRENT('특정테이블명')

- 특정 테이블에 한정할 수 있음

- 특정 세션과 범위에 있는 테이블에 대한 마지막 ID 값을 반환함

view plain copy to clipboard print ?

SELECT IDENT_CURRENT('테이블명')

SELECT IDENT_CURRENT('테이블명')

2. @@IDENTITY

- 전체 범위에 대한 현제 세션에 있는 테이블에 대해 생성된 마지막 ID 값을 반환함

view plain copy to clipboard print ?

SELECT @@IDENTITY()

SELECT @@IDENTITY()

3. SCOPE_IDENTITY()

- 현제 세션, 범위에 있는 테이블에 대해 생성된 마지막 ID 값을 반환함

view plain copy to clipboard print ?

SELECT SCOPE_IDENTITY()

SELECT SCOPE_IDENTITY()

4. 각 함수 및 전역변수 차이점

- IDENT_CURRENT 는 세션과 상관없이 특정 테이블 시드값을 가져옴

- @@IDENTITY,SCOPE_IDENTITY() 은 해당 세션만 유효

- @@IDENTITY 연쇄적으로 발생해도 최종 시드값을 가져옴

- SCOPE_IDENTITY() 연쇄적으로 발생해도 최초 시드값을 가져옴

view plain copy to clipboard print ?

-- 1부터 시작하는 IDENTITY 값을 가지고 있는 TEMP1 테이블
CREATE TABLE TEMP1 ( Seq INT IDENTITY(1,1) )
GO
-- 1000부터 시작하는 IDENTITY 값을 가지고 있는 TEMP2 테이블
CREATE TABLE TEMP2 ( Seq INT IDENTITY(1000,1) )
GO
-- 트리거 만들기
-- #TEMP1 에 값이 들어가면 TEMP2 에도 값을 넣어주는 트리거만들기
CREATE TRIGGER TEMP1_I ON TEMP1 FOR INSERT
AS
BEGIN
INSERT INTO TEMP2 DEFAULT VALUES
END
----------------------------------------------------------------------------
--
-- 테스트 상황 1
-- 같은 세션인 경우
-- TEMP1에 값을 넣었을때 IDENT_CURRENT('특정테이블명'), @@IDENTITY, SCOPE_IDENTITY()
-- 각각 어떤 값을 가져오느냐 테스트
--
----------------------------------------------------------------------------
-- 1. INSERT
INSERT INTO TEMP1 DEFAULT VALUES
-- 2. @@IDENTITY
SELECT @@IDENTITY --> 결과값: 1000, TEMP2의 마지막 Identity값 가져옴
-- 3. SCOPE_IDENTITY()
SELECT SCOPE_IDENTITY() --> 결과값: 1, TEMP1의 마지막 Identity값 가져옴
-- 4. IDENT_CURRENT('특정테이블명')
SELECT IDENT_CURRENT('TEMP1') --> 결과값: 1, TEMP1의 마지막 Identity값 가져옴
SELECT IDENT_CURRENT('TEMP2') --> 결과값: 1000, TEMP2의 마지막 Identity값 가져옴
----------------------------------------------------------------------------
--
-- 테스트 상황 2
-- 다른 세션인 경우
-- 다른 세션에서 값을 넣었을때 IDENT_CURRENT('특정테이블명'), @@IDENTITY, SCOPE_IDENTITY()
-- 각각 어떤 값을 가져오느냐 테스트
--
----------------------------------------------------------------------------
-- 1. @@IDENTITY
SELECT @@IDENTITY --> 결과값: NULL
-- 2. SCOPE_IDENTITY()
SELECT SCOPE_IDENTITY() --> 결과값: NULL
-- 3. IDENT_CURRENT('특정테이블명')
SELECT IDENT_CURRENT('TEMP1') --> 결과값: 1, TEMP1의 마지막 Identity값 가져옴
SELECT IDENT_CURRENT('TEMP2') --> 결과값: 1000, TEMP2의 마지막 Identity값 가져옴

출처 : http://rocabilly.tistory.com/26

728x90

저작자표시 비영리 변경금지

'프로그래밍 > MS_SQL' 카테고리의 다른 글

MSSQL 테이블 COMMENT 보기 (0)	2015.04.29
MSSQL IDENTITY 값 재 설정 (0)	2015.04.27
sqlexpress 백업 및 복원 (0)	2015.04.24
mssql 파일 크기 줄이기 (0)	2015.04.14
MSSQL COMMENTS 생성 (0)	2015.04.13

sqlexpress 백업 및 복원

GOHA 2015. 4. 24. 11:52

2015. 4. 24. 11:52

728x90

Express 버전의 경우 서버 에이젼트가 설치되지 않아 작업스케쥴링을 설정하는것이 불가능합니다..

그래서 배치파일("sqlcmd")과 프로시져를 이용한 백업 스크립트를 작성하고 윈도우 스케쥴링을 통해 자동 백업을 실행 할 수 있습니다.

상세한 내용은 MS Support Url을 참조해주세요. ( http://support.microsoft.com/kb/2019698/en-us Viewer )

1. 백업을 위한 프로시져 생성

// Copyright © Microsoft Corporation.  All Rights Reserved.
// This code released under the terms of the
// Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.)
USE [master] 
GO 
/****** Object:  StoredProcedure [dbo].[sp_BackupDatabases] ******/ 
SET ANSI_NULLS ON
GO 
SET QUOTED_IDENTIFIER ON
GO 
  
-- ============================================= 
-- Author: Microsoft 
-- Create date: 2010-02-06
-- Description: Backup Databases for SQLExpress
-- Parameter1: databaseName 
-- Parameter2: backupType F=full, D=differential, L=log
-- Parameter3: backup file location
-- =============================================
  
CREATE PROCEDURE [dbo].[sp_BackupDatabases]  
            @databaseName sysname = null,
            @backupType CHAR(1),
            @backupLocation nvarchar(200) 
AS
  
       SET NOCOUNT ON; 
            
            DECLARE @DBs TABLE
            (
                  ID int IDENTITY PRIMARY KEY,
                  DBNAME nvarchar(500)
            )
            
             -- Pick out only databases which are online in case ALL databases are chosen to be backed up
             -- If specific database is chosen to be backed up only pick that out from @DBs
            INSERT INTO @DBs (DBNAME)
            SELECT Name FROM master.sys.databases
            where state=0
            AND name=@DatabaseName
            OR @DatabaseName IS NULL
            ORDER BY Name
            
            -- Filter out databases which do not need to backed up
            IF @backupType='F'
                  BEGIN
                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','AdventureWorks')
                  END
            ELSE IF @backupType='D'
                  BEGIN
                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')
                  END
            ELSE IF @backupType='L'
                  BEGIN
                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')
                  END
            ELSE
                  BEGIN
                  RETURN
                  END
            
            -- Declare variables
            DECLARE @BackupName varchar(100)
            DECLARE @BackupFile varchar(100)
            DECLARE @DBNAME varchar(300)
            DECLARE @sqlCommand NVARCHAR(1000) 
        DECLARE @dateTime NVARCHAR(20)
            DECLARE @Loop int                 
                        
            -- Loop through the databases one by one
            SELECT @Loop = min(ID) FROM @DBs
  
      WHILE @Loop IS NOT NULL
      BEGIN
  
-- Database Names have to be in [dbname] format since some have - or _ in their name
      SET @DBNAME = '['+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+']'
  
-- Set the current date and time n yyyyhhmmss format
      SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + '_' +  REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')  
  
-- Create backup filename in path\filename.extension format for full,diff and log backups
      IF @backupType = 'F'
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_'+ @dateTime+ '.BAK'
      ELSE IF @backupType = 'D'
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_DIFF_'+ @dateTime+ '.BAK'
      ELSE IF @backupType = 'L'
            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_LOG_'+ @dateTime+ '.TRN'
  
-- Provide the backup a name for storing in the media
      IF @backupType = 'F'
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' full backup for '+ @dateTime
      IF @backupType = 'D'
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' differential backup for '+ @dateTime
      IF @backupType = 'L'
            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' log backup for '+ @dateTime
  
-- Generate the dynamic SQL command to be executed
  
       IF @backupType = 'F'
                  BEGIN
               SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'
                  END
       IF @backupType = 'D'
                  BEGIN
               SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH DIFFERENTIAL, INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'       
                  END
       IF @backupType = 'L'
                  BEGIN
               SET @sqlCommand = 'BACKUP LOG ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'       
                  END
  
-- Execute the generated SQL command
       EXEC(@sqlCommand)
  
-- Goto the next database
SELECT @Loop = min(ID) FROM @DBs where ID>@Loop
  
END

2. 백업 프로시져 호출을 위한 bat 파일을 생성합니다.

( 아래 스크립트는 MS 사이트에서 가져온 내용이고.. 위의 프로시져를 보시는편이 더 쉽게 이해하실수 있을 것 같습니다....)

-- 전체 백업 , 윈도우 인증을 통한 SQL EXPRESS의 모든 대상 백업.
sqlcmd -S .\EXPRESS –E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @backupType='F'"
 
-- 차등 백업 ,  Login 인증을 통한 SQL EXPRESS의 모든 대상 백업.
sqlcmd -U SQLLogin -P password -S .\SQLEXPRESS -Q "EXEC sp_BackupDatabases @backupLocation ='D:\SQLBackups', @BackupType=’D’"
 
-- 로그 전체 백업 , 윈도우 인증을  SQL EXPRESS의 모든 대상 백업.
sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\',@backupType='L'"
 
-- 전체 백업 , 지정된 DB 를 윈도우 인증을 통해 백업
sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @databaseName=’USERDB’, @backupType='F'"

실제사용한 백업 배치파일 스크립트

1	`sqlcmd -S .\SQLEXPRESS -E -Q` `"EXEC sp_BackupDatabases @backupLocation='E:\uhoon_DB_BACKUP\', @databaseName='db_name', @backupType='F'"`

3. 위의 스크립트를 통해 생성한 배치파일을 실행하는것으로 백업이 가능하며

윈도우 스케쥴링에 등록하여 자동 백업이 가능하게 됩니다.

출처 - http://www.uhoon.co.kr/mssql/1301

728x90

저작자표시 비영리 변경금지

'프로그래밍 > MS_SQL' 카테고리의 다른 글

MSSQL IDENTITY 값 재 설정 (0)	2015.04.27
MSSQL 자동 증가 시드값 가져오기 (0)	2015.04.27
mssql 파일 크기 줄이기 (0)	2015.04.14
MSSQL COMMENTS 생성 (0)	2015.04.13
mssql 테이블 용량 및 row 갯수 (0)	2015.03.24

mssql 파일 크기 줄이기

GOHA 2015. 4. 14. 14:21

2015. 4. 14. 14:21

728x90

MS-SQL MDF/LDF 파일 크기 줄이기

초기 설정값의 실수, 혹은 어쩌다가 데이터가 대박 큰게 들어왔다가 사라졌을 때, DB의 디스크 용량을 줄이고 싶을 때가 있다. 이럴때 사용하는 것.

-- DB 정보보기

SP_HELPDB db_name

-- DB 로그 비우기

BACKUP LOG db_name WITH TRUNCATE_ONLY

-- DB 의 LDF 파일의 크기를 5MB로 줄인다.

DBCC SHRINKFILE (logfile_name, 5)

-- MDF 파일 크기 줄이기

DBCC SHRINKDATABASE(db_name)

출처 : http://coolsoo7221.blogspot.kr/

728x90

저작자표시 비영리 변경금지

'프로그래밍 > MS_SQL' 카테고리의 다른 글

MSSQL 자동 증가 시드값 가져오기 (0)	2015.04.27
sqlexpress 백업 및 복원 (0)	2015.04.24
MSSQL COMMENTS 생성 (0)	2015.04.13
mssql 테이블 용량 및 row 갯수 (0)	2015.03.24
오라클 재귀쿼리를 MSSQL 재귀쿼리로 만드는 방법 (0)	2015.02.13

MSSQL COMMENTS 생성

GOHA 2015. 4. 13. 15:27

2015. 4. 13. 15:27

728x90

MSSQL SCRIPT로 COMMENT 달기

/* 테이블 주석 */
exec sp_addextendedproperty 'MS_DESCRIPTION','[테이블주석]','user', 'dbo', 'table', '[테이블명]'
go

/* 컬럼 주석 */
exec sp_addextendedproperty 'MS_DESCRIPTION','[컬럼주석]','user', 'dbo','table', '[테이블명]', 'column', '[컬럼명]'

728x90

저작자표시 비영리 변경금지

'프로그래밍 > MS_SQL' 카테고리의 다른 글

sqlexpress 백업 및 복원 (0)	2015.04.24
mssql 파일 크기 줄이기 (0)	2015.04.14
mssql 테이블 용량 및 row 갯수 (0)	2015.03.24
오라클 재귀쿼리를 MSSQL 재귀쿼리로 만드는 방법 (0)	2015.02.13
Session 관리 ( Inproc,StateServer,SQL Server ) (0)	2013.06.14

mssql 테이블 용량 및 row 갯수

GOHA 2015. 3. 24. 13:58

2015. 3. 24. 13:58

728x90

-- 테이블별 사용 용량

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

, table_size = ltrim(str(sum(cast(reserved as bigint)) * 8192 / 1024.,15,0) + 'KB')

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

-- 용량별 소팅

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

, table_size = convert(int, ltrim(str(sum(cast(reserved as bigint)) * 8192 / 1024., 15, 0))), UNIT = 'KB'

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 table_size DESC

-- 테이블별 Row 수

SELECT o.name

, i.rows

FROM sysindexes i

INNER JOIN

sysobjects o

ON i.id = o.id

WHERE i.indid < 2

AND o.xtype = 'U'

ORDER BY i.id

출처 : http://unions5.tistory.com/92

728x90

저작자표시 비영리 변경금지

'프로그래밍 > MS_SQL' 카테고리의 다른 글

mssql 파일 크기 줄이기 (0)	2015.04.14
MSSQL COMMENTS 생성 (0)	2015.04.13
오라클 재귀쿼리를 MSSQL 재귀쿼리로 만드는 방법 (0)	2015.02.13
Session 관리 ( Inproc,StateServer,SQL Server ) (0)	2013.06.14
mssql 2008 로그 축소 (0)	2013.04.29

PREV 이전 1 2 3 NEXT 다음

사랑하기 위해 필요한 것들