자주 사용하는 쿼리문들 (Queries Frequently Used)

table comment  테이블 코멘트 설명 주석

SELECT OBJTYPE, OBJNAME, NAME, VALUE 
FROM   ::FN_LISTEXTENDEDPROPERTY(NULL, 'SCHEMA', 'DBO', 'TABLE', '테이블이름', DEFAULT, DEFAULT) ;

-- OR

SELECT OBJTYPE, OBJNAME, NAME, VALUE 
FROM   ::FN_LISTEXTENDEDPROPERTY(NULL, 'SCHEMA', 'DBO', 'TABLE', '테이블이름', 'column', DEFAULT) ;

 

 

프로시저 / 함수 내용 조회

1] sp_helptext 이용 (추천)

sp_helptext 프로시저 or 함수 이름

 

2] select 문 이용

SELECT B.text 
FROM       sys.sysobjects  AS A WITH (NOLOCK) 
INNER JOIN sys.syscomments AS B WITH (NOLOCK) ON A.id = B.id 
WHERE A.name = '프로시져 or 함수 이름' ;

 

 

칼럼명으로 테이블 찾기

 

SELECT T.name AS table_name, C.name AS column_name
FROM       sys.tables  as T
INNER JOIN sys.columns as C on T.object_id = C.object_id
WHERE  C.name = '칼럼명' ;

 

 

alternative to using COUNT(*)  - indid : clustered index 의 개수

 

SELECT rows 
FROM   sysindexes 
WHERE  id=OBJECT_ID ('테이블명') AND indid < 2 ;

 

 

테이블의 칼럼명 추출

 

SELECT LOWER(A.COLUMN_NAME) + ','         -- 대문자로 받으려면 UPPER
FROM   INFORMATION_SCHEMA.COLUMNS A 
WHERE  A.TABLE_NAME = '테이블명' 

 

 

테이블별 데이터 건수 및 사이즈 확인

도움 : windtrap.tistory.com/7

 

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, 2 DESC

 

 

 

일련번호 작업

-- 실행 1 : Base Query

DECLARE @P_SNUMBER varchar(11)
DECLARE @P_STRING  varchar(11) = 'myTest_'

	SET @P_SNUMBER = @P_STRING + '0001';

select @P_SNUMBER as P_SNUMBER

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

-- 실행 2 : 시리얼번호 증가 테스트

DECLARE @P_SNUMBER1 varchar(11)
DECLARE @P_SNUMBER2 varchar(11)
DECLARE @P_STRING   varchar(7) = 'myTest_'

-- 전체 11 자리 / 숫자 4 자리
SET @P_SNUMBER1 = 'myTest_0011';
SET @P_SNUMBER2 = 'myTest_2222';

-- 숫자부분만 끊어서 1 증가
SET @P_SNUMBER1 = SUBSTRING(@P_SNUMBER1, 8, 11) + 1;
SET @P_SNUMBER2 = SUBSTRING(@P_SNUMBER2, 8, 11) + 1;

select @P_SNUMBER1 as P_SNUMBER1,
       @P_SNUMBER2 as P_SNUMBER2;

-- (숫자 전체 자리수 - 숫자수) 만큼 앞에 0 을 붙여서
-- 앞에 @P_STRING 문자열(myTest_) 을 붙여준다
-- REPLICATE(문자(열), 숫자) : 문자(열) 을 숫자 개수 만큼 복제하여 붙여넣기
SET @P_SNUMBER1 = @P_STRING
                + (REPLICATE(0, 4 - LEN(@P_SNUMBER1))
                +  CONVERT(VARCHAR, @P_SNUMBER1));

SET @P_SNUMBER2 = @P_STRING
                + (REPLICATE(0, 4 - LEN(@P_SNUMBER2))
                +  CONVERT(VARCHAR, @P_SNUMBER2));

select @P_SNUMBER1 as P_SNUMBER1,
       @P_SNUMBER2 as P_SNUMBER2;

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

-- 실행 3 : 시리얼번호가 없으면 문자열_0001
--               이미 존재하면 문자열 + 1

DECLARE @P_SNUMBER1 varchar(11)
DECLARE @P_SNUMBER2 varchar(11)
DECLARE @P_STRING   varchar(7) = 'myTest_'


-- 전체 11 자리 / 숫자 4 자리
SET @P_SNUMBER2 = 'myTest_0011';


-- @P_SNUMBER1 처리  (= 시리얼번호가 NULL 인 경우)
IF @P_SNUMBER1 IS NULL
BEGIN
  SET @P_SNUMBER1 = @P_STRING + '0001';
END
ELSE
BEGIN
  SET @P_SNUMBER1 = SUBSTRING(@P_SNUMBER1, 8, 11) + 1;
  SET @P_SNUMBER1 = @P_STRING
                + (REPLICATE(0, 4 - LEN(@P_SNUMBER1))
                +  CONVERT(VARCHAR, @P_SNUMBER1));
END


-- @P_SNUMBER2 처리  (= 시리얼번호가 NULL 이 아닌 경우)
IF @P_SNUMBER2 IS NULL
BEGIN
  SET @P_SNUMBER2 = @P_STRING + '0001';
END
ELSE
BEGIN
  SET @P_SNUMBER2 = SUBSTRING(@P_SNUMBER2, 8, 11) + 1;
  SET @P_SNUMBER2 = @P_STRING
                + (REPLICATE(0, 4 - LEN(@P_SNUMBER2))
                +  CONVERT(VARCHAR, @P_SNUMBER2));
END


select @P_SNUMBER1 as P_SNUMBER1,
       @P_SNUMBER2 as P_SNUMBER2;

 

결과 1    P_SNUMBER ----------------- 1  myTest_0001

결과 2     P_SNUMBER1        P_SNUMBER1 -----------------------------------------     12                      2223     P_SNUMBER1        P_SNUMBER1 -----------------------------------------     myTest_0012        myTest_2223

결과 3     P_SNUMBER1        P_SNUMBER1 -----------------------------------------     myTest_0001         myTest_0012

 

int 순번 칼럼 insert 할때

 

INSERT 테이블명 (
       칼럼1
     , 칼럼2
)
values (
       칼럼1값
     , (SELECT ISNULL(MAX(EXPE_SEQNO), 0)  + 1
        FROM   TEV_EVENT_EXPENS (NOLOCK)
        WHERE  EVENT_MANAGE_SN = @EVENT_MANAGE_SN)
)

 

 

 

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