IT 이야기

SQL 작업을 하다보면 테이블이 언제 생성 되었으며, 마지막으로 수정된 날짜 및 인덱스 사이즈을 알고 싶을 때가 있다.

적용 버전은 MSSQL2005, MSSQL2008 버전임.
2000 버전은 프로파일러를 돌려서 방법 외에는 없는 듯함.

--1. 특정 테이블의 정보를 간단하게 정보 DISPLAY
SELECT * FROM sys.dm_db_index_usage_stats WHERE object_id = OBJECT_ID('dbo.TN_KOREAN_WORD');

출처 : http://blog.naver.com/PostView.nhn?blogId=nebadak&logNo=90085971368&redirect=Dlog&widgetTypeCall=true

-- 2. 테이블별 데이터 개수, 인덱스 사이즈, 생성일, 마지막 수정일 DISPLAY
SELECT Schema_name(tbl.schema_id)                                 AS [Schema], 
       tbl.name, 
       Coalesce((SELECT pr.name 
                 FROM   sys.database_principals pr WITH (nolock) 
                 WHERE  pr.principal_id = tbl.principal_id), 
       Schema_name(tbl.schema_id))                                AS [Owner], 
       tbl.max_column_id_used                                     AS [Columns], 
       CAST(CASE idx.index_id 
              WHEN 1 THEN 1 
              ELSE 0 
            END AS BIT)                                           AS 
       [HasClusIdx], 
       Coalesce((SELECT SUM (spart.ROWS) 
                 FROM   sys.partitions spart WITH (nolock) 
                 WHERE  spart.object_id = tbl.object_id 
                        AND spart.index_id < 2), 0)               AS [RowCount], 
       Coalesce((SELECT CAST(v.low / 1024.0 AS FLOAT) * SUM(a.used_pages - CASE 
                                         WHEN a.TYPE <> 1 THEN a.used_pages 
                                         WHEN p.index_id < 2 THEN a.data_pages 
                                         ELSE 0 
                                                                           END) 
                 FROM   sys.indexes AS i WITH (nolock) 
                        JOIN sys.partitions AS p WITH (nolock) 
                          ON p.object_id = i.object_id 
                             AND p.index_id = i.index_id 
                        JOIN sys.allocation_units AS a WITH (nolock) 
                          ON a.container_id = p.partition_id 
                 WHERE  i.object_id = tbl.object_id), 0.0) / 1024 AS [IndexMB], 
       Coalesce((SELECT CAST(v.low / 1024.0 AS FLOAT) * SUM(CASE 
                        WHEN a.TYPE <> 1 THEN a.used_pages 
                                 WHEN p.index_id < 2 THEN a.data_pages 
                                 ELSE 0 
                                                            END) 
                 FROM   sys.indexes AS i WITH (nolock) 
                        JOIN sys.partitions AS p WITH (nolock) 
                          ON p.object_id = i.object_id 
                             AND p.index_id = i.index_id 
                        JOIN sys.allocation_units AS a WITH (nolock) 
                          ON a.container_id = p.partition_id 
                 WHERE  i.object_id = tbl.object_id), 0.0) / 1024 AS [DataMB], 
       tbl.create_date, 
       tbl.modify_date 
FROM   sys.tables AS tbl WITH (nolock) 
       INNER JOIN sys.indexes AS idx WITH (nolock) 
         ON ( idx.object_id = tbl.object_id 
              AND idx.index_id < 2 ) 
       INNER JOIN MASTER.dbo.spt_values v WITH (nolock) 
         ON ( v.NUMBER = 1 
              AND v.TYPE = 'E' ) 
--WHERE tbl.Name like '%tablename%'  
ORDER  BY 8 DESC 

출처 : http://www.sqler.com/266501

데이타베이스 작업을 하다보면 외부 사용자에게 데이타베이스를 열어주고 작업을 할때가 있는데 이런 경우 내부 데이타베이스는 숨기고 싶을때가 있다. 내부 정보를 외부 사람이 보게 만들 필요가 없으니.

이런 경우 외부사용자가 사용하는 데이타베이스만 보이게 하는 기능이 있어서 포스팅 한다.

1.  새로운 사용자 'hide1' 을 만든다. (이때 데이타베이스와 매핑을 하지 않는다)

2. 최상위 레벨에서 SQL (SQLSERVER Name)>[속성]>[사용권한]>[모든 데이타베이스 보이기]
   를 [거부] 체크한다.

3. 새로 데이타베이스를 생성하고, [속성]>[파일]>[소유자]를 새로 생성한 사용자 [hide1]으로 설정
   한다. 

1. 랜덤 쿠폰 번호 발행하기

NEWID() 함수를 이용한 방법을 소개하도록 하겠다.

먼저 NEWID() 함수의 속성부터 알아보도록 하자.

1. NEWID() 는 uniqueidentifier 데이타 형식을 가진다.
2. GUID(전역 고유 식별자) 역할을 하는 16바이트 이진 값을 저장한다.
3. GUID는 고유한 이진 숫자 값이다.
4. 전 세계 모든 컴퓨터는 고유한 GUID 값을 생성한다.
5. GUID의 주된 용도는 여러 사이트에 많은 컴퓨터가 있는 네트워크에서 고유한 식별자를 지정하는 것이다.

실제 SQL 문에서 실행되는 예제를 보도록 하자.

DECLARE @NEWID_TEMP VARCHAR(255)
SET @NEWID_TEMP = NEWID()

SELECT @NEWID_TEMP 
SELECT CONVERT(VARBINARY(255), @NEWID_TEMP)

/*****************************************************************************************
문자열 형식 : ABD26C1E-3781-405E-9D77-4B947DD7CBD0
이진 형식 : 0x41424432364331452D333738312D343035452D394437372D344239343744443743424430
*******************************************************************************************/ 

NEWID 를 이용한 전체 소스 코드임...

SET NOCOUNT ON

DECLARE @MAX_COUNT INT
DECLARE @INDEX INT
DECLARE @COUPON_NUM VARCHAR(10)
DECLARE @COUPON_TYPE VARCHAR(10)
DECLARE @COUPON_PREFIX VARCHAR(10)

SET @MAX_COUNT = 1000
SET @INDEX = 1
SET @COUPON_PREFIX = 'L'
SET @COUPON_TYPE = 'C_TYPE'

WHILE @INDEX <= @MAX_COUNT
BEGIN

	SET @COUPON_NUM = LEFT(REPLACE(NEWID(),'-',''), 10)
						
	INSERT INTO dbo.Dior_Coupon_201104 (COUPON_TYPE, COUPON_NUM)
	VALUES (@COUPON_TYPE, @COUPON_NUM)
	
	SET @INDEX = @INDEX + 1
END

2. 순차적 쿠폰번호 발행하기 

쿠폰 번호를 발행하는데 순자적으로, 즉 ETP0001, ETP0002 ,ETP0003 식으로 발행하는 것을 해 보자.

키 포인트는 숫자를 문자열로 변환하면서 자릿수를 맞추어줘야 한다. 이때 사용하는 함수가 REPLCATE 함수를 이용하면 된다.
전제 소스는 아래와 같다.

SET NOCOUNT ON

DECLARE @MAX_COUNT INT
DECLARE @INDEX INT
DECLARE @COUPON_NUM VARCHAR(10)
DECLARE @COUPON_TYPE VARCHAR(10)
DECLARE @COUPON_PREFIX VARCHAR(10)

SET @MAX_COUNT = 1000
SET @INDEX = 1
SET @COUPON_PREFIX = 'L'
SET @COUPON_TYPE = 'C_TYPE'

WHILE @INDEX <= @MAX_COUNT
BEGIN

	SET @COUPON_NUM = @COUPON_PREFIX + 
                          REPLICATE('0', 4 - DATALENGTH(CONVERT(VARCHAR(4),@INDEX))) + 
                          CONVERT(VARCHAR(4),@INDEX)
						
	INSERT INTO dbo.Dior_Coupon_201104 (COUPON_TYPE, COUPON_NUM)
	VALUES (@COUPON_TYPE, @COUPON_NUM)
	
	SET @INDEX = @INDEX + 1
END