'SQL' 카테고리의 글 목록

728x90

󰊱 [오라클] SQL 예시

① 테이블 정의서 추출 : ‘OWNER명’을 수정하여 수행

테이블 정의서 추출 SQL

SELECT S1.OWNER --OWNER명
, S1.TABLE_NAME --영문테이블명
, S2.COMMENTS --한글테이블명
, S1.NUM_ROWS --테이블볼륨
, S3.CREATED --등록일자
, S3.LAST_DDL_TIME --수정일자
FROM ALL_TABLES S1
, ALL_TAB_COMMENTS S2
, ALL_OBJECTS S3
WHERE 1=1
AND S1.OWNER = S2.OWNER
AND S1.TABLE_NAME = S2.TABLE_NAME
AND S1.OWNER = S3.OWNER
AND S1.TABLE_NAME = S3.OBJECT_NAME
AND S2.TABLE_TYPE = 'TABLE'
AND S1.TABLE_NAME NOT LIKE '%TEMP%'
AND S1.OWNER NOT IN ('SYS')
AND S1.TABLESPACE_NAME NOT IN ('SYSTEM' , 'SYSAUX')
AND S1.OWNER = 'OWNER명' -- Owner명 입력
ORDER BY S1.OWNER, S1.TABLE_NAME

② 컬럼 정의서 추출 : ‘OWNER명’을 수정하여 수행

컬럼 정의서 추출 SQL

--오라클의 DB명은 직접 입력해야함

SELECT A.OWNER AS OWNER명,
A.TABLE_NAME AS 테이블명_1,
A.COLUMN_NAME AS 컬럼영문명_2,
A.COL_CMT AS 한글컬럼명_3,
A.NULLABLE AS NULL여부_6,
A.DATA_TYPE AS 데이터타입_7,
A.LEN 데이터길이_8,
B.POS AS PK정보_9,
C.FK_POS FK정보_10,
A.DATA_DEFAULT AS 제약조건_11,
A.COLUMN_ID AS 컬럼순서
FROM
(SELECT S1.OWNER ,
S1.TABLE_NAME,
S3.COMMENTS AS TAB_CMT,
S1.COLUMN_NAME,
S2.COMMENTS AS COL_CMT,
S1.DATA_TYPE,
CASE WHEN S1.DATA_PRECISION IS NOT NULL THEN DATA_PRECISION||','||DATA_SCALE
ELSE TO_CHAR(S1.DATA_LENGTH)
END AS LEN,
NULLABLE,
COLUMN_ID,
DATA_DEFAULT
FROM ALL_TAB_COLUMNS S1,
ALL_COL_COMMENTS S2,
ALL_TAB_COMMENTS S3
WHERE S1.OWNER = S2.OWNER
AND S1.TABLE_NAME = S2.TABLE_NAME
AND S1.COLUMN_NAME = S2.COLUMN_NAME
AND S1.OWNER = S3.OWNER
AND S1.TABLE_NAME = S3.TABLE_NAME ) A,
(SELECT T1.OWNER, T1.TABLE_NAME, T2.COLUMN_NAME, 'PK'||POSITION AS POS
FROM (SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'P' )T1,
(SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME, COLUMN_NAME, POSITION
FROM USER_CONS_COLUMNS ) T2
WHERE T1.OWNER = T2.OWNER
AND T1.TABLE_NAME = T2.TABLE_NAME
AND T1.CONSTRAINT_NAME = T2.CONSTRAINT_NAME ) B,
(SELECT T1.OWNER, T1.TABLE_NAME, T2.COLUMN_NAME, 'FK'||POSITION AS FK_POS
FROM (SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'R' )T1,
(SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME, COLUMN_NAME, POSITION
FROM USER_CONS_COLUMNS ) T2
WHERE T1.OWNER = T2.OWNER
AND T1.TABLE_NAME = T2.TABLE_NAME
AND T1.CONSTRAINT_NAME = T2.CONSTRAINT_NAME ) C
WHERE A.OWNER = B.OWNER(+)
AND A.TABLE_NAME = B.TABLE_NAME(+)
AND A.COLUMN_NAME = B.COLUMN_NAME(+)
AND A.OWNER = C.OWNER(+)
AND A.TABLE_NAME = C.TABLE_NAME(+)
AND A.COLUMN_NAME = C.COLUMN_NAME(+)
AND A.OWNER = 'OWNER명'
ORDER BY A.TABLE_NAME, A.COLUMN_ID

󰊲 [MYSQL] SQL 예시

① 테이블 정의서 추출 : ‘OWNER명’을 수정하여 수행

테이블 정의서 추출 SQL

SELECT table_schema 'OWNER명'
, TABLE_NAME '영문테이블명',
, TABLE_COMMENT '한글테이블명'
, TABLE_ROWS '테이블볼륨'
, CREATE_TIME '등록일자'
, UPDATE_TIME '수정일자'
FROM information_schema.TABLES
WHERE table_schema='OWNER명' --OWNER명 입력
AND TABLE_TYPE = 'BASE TABLE'

② 컬럼 정의서 추출 : ‘OWNER명’을 수정하여 수행

컬럼 정의서 추출 SQL

SELECT
t1.table_schema 'OWNER명',
t1.table_name '테이블명_1',
column_name '영문컬럼명_2',
column_comment '한글컬럼명_3',
CASE
WHEN is_nullable = 'YES' THEN 'Y'
WHEN is_nullable = 'NO' THEN 'N'
END AS 'Null여부_6',
COLUMN_TYPE '데이터타입_7',
column_key 'PK정보_9',
column_default '제약조건_11',
extra 'Extra',
ORDINAL_POSITION
FROM
(SELECT
table_schema, table_name, table_comment
FROM information_schema.TABLES) t1,
(SELECT
table_schema, table_name, ORDINAL_POSITION, column_name, COLUMN_TYPE,column_key, extra, is_nullable, column_default, column_comment
FROM information_schema.COLUMNS) t2
WHERE
t1.table_schema = t2.table_schema
AND t1.table_name = t2.table_name
AND t1.TABLE_SCHEMA = 'OWNER명 입력'
ORDER BY t1.table_name, ordinal_position

󰊳 [MSSQL] SQL 예시

테이블 정의서 추출 SQL

SELECT
A.TABLE_CATALOG, --OWNER명
DB_NAME() AS DBNAME, --DB명
A.TABLE_NAME, --테이블명
C.VALUE AS TABLE_COMMENT, --테이블 COMMENT
i.rows, --테이블의 건수
J.create_date, --테이블생성일시
J.modify_date --테이블변경일시

FROM INFORMATION_SCHEMA.tables A
INNER JOIN sysobjects o ON o.name = a.TABLE_NAME
INNER JOIN sysindexes i ON o.id = i.id
INNER JOIN sys.objects j ON J.NAME = A.TABLE_NAME
LEFT OUTER JOIN
(
SELECT object_id(objname) AS TABLE_ID, VALUE
FROM ::FN_LISTEXTENDEDPROPERTY(NULL, 'User','dbo','table',NULL, NULL, NULL)
) C ON object_id(A.TABLE_NAME) = C.TABLE_ID
WHERE i.indid < 2
AND o.xtype = 'U'
AND A.TABLE_NAME <> 'sysdiagrams'
AND A.TABLE_NAME NOT LIKE '%temp%'
AND A.TABLE_NAME NOT LIKE '%test%'
AND A.TABLE_CATALOG = 'OWNER명' --OWNER명 입력
ORDER BY A.TABLE_CATALOG, DB_NAME(), A.TABLE_NAME

① 테이블 정의서 추출 : ‘OWNER명’을 수정하여 수행

② 컬럼 정의서 추출 : ‘OWNER명’을 수정하여 수행

컬럼 정의서 추출 SQL

SELECTB.TableName AS [테이블명_1]
, A.COLUMN_NAME AS [영문컬럼명_2]
, ISNULL(B.ColumnDescription,'') AS [한글컬럼명_3]
,CASE A.IS_NULLABLE
WHEN 'YES'THEN 'Y'
ELSE 'N' ENDAS [NULL여부]
, CASE WHEN IdxType = 'C'
THEN 'Y' ELSE '' ENDAS PK정보_9
, CASE WHEN FKey is not null
THEN 'Y' ELSE '' ENDAS FK정보_10
, A.DATA_TYPEAS [데이터타입_7]
, CASE
WHEN A.CHARACTER_MAXIMUM_LENGTH IS NULL THEN
ISNULL(A.NUMERIC_PRECISION, '')
ELSE A.CHARACTER_MAXIMUM_LENGTH
END AS [데이터길이_8]
, ISNULL(A.COLUMN_DEFAULT, '') AS [제약조건_11]
, CASE WHEN refID is not null
THEN object_Name(refID)
ELSE '' ENDAS [참조테이블]
, CASE WHEN refColID is not null
THEN col_name(refID, refColID)
ELSE '' ENDAS [참조컬럼]
, ''AS [유형]
, ''AS [Validation Rule]
,A.ORDINAL_POSITIONAS [NO]
FROM INFORMATION_SCHEMA.COLUMNS AS A WITH (NOLOCK)
INNER JOIN (
SELECTA.nameAS TableName
,B.nameAS ColumnName
,C.Value AS ColumnDescription
, D.FKey
,D.refID
, D.refColID
,E.IdxType
FROMsys.tables AS A WITH (NOLOCK)
INNER JOIN sys.columns AS B WITH (NOLOCK)
ON A.object_id = B.object_id
left outer JOIN sys.extended_properties AS C WITH (NOLOCK)
ON A.object_id = C.major_id
AND B.column_id = c.minor_id
left outer join (
selectf.name AS FKey, f.parent_object_id, fc.parent_column_id, fc.referenced_object_id refID, fc.referenced_column_id refColID
fromsys.foreign_keys f
inner join sys.foreign_key_columns fc on f.object_id = fc.constraint_object_id
) D on A.object_id = D.parent_object_id and B.column_id = D.parent_column_id
left outer join (
select (case i.type when 0 then 'H' when 1 then 'C' else 'N' end) IdxType, i.object_id, c.column_id
fromsys.indexes i
inner join sys.index_columns c on i.index_id = c.index_id and c.object_id = i.object_id
group by i.type, i.object_id, c.column_id
) E on A.object_id = E.object_id and B.column_id = E.column_id
) AS B
ON A.TABLE_NAME = B.TableName
AND A.COLUMN_NAME = B.ColumnName
WHERE A.TABLE_CATALOG = 'OWNER명'
order by 테이블명_1, NO

󰊴 [CUBRID) SQL 예시

① 테이블 정의서 추출 : ‘OWNER명’을 수정하여 수행(버전 10.X 기준)

테이블 정의서 추출 SQL

-10,x 버전
--큐브리드는 시스템 카탈로그에서 테이블건수 및 생성시간 수정시간 조회가 안됨

SELECT A.OWNER_NAME AS OWNER명
, A.CLASS_NAME AS 영문테이블명_2
, A.COMMENT AS 한글테이블명_3
FROM DB_CLASS A
WHERE A.OWNER_NAME = 'OWNER명'
AND A.is_system_class = 'NO'
ORDER BY A.OWNER_NAME ,A.CLASS_NAME

--9.x 버전
--큐브리드는 시스템 카탈로그에서 테이블건수 및 생성시간 수정시간 조회가 안됨

SELECT A.OWNER_NAME AS OWNER명
, A.CLASS_NAME AS 테이블명_2
, G.DESCRIPTION AS 한글테이블명_3 --DBA권한 필요

FROM DB_CLASS A
LEFT JOIN _CUB_SCHEMA_COMMENTS G
ON A.CLASS_NAME = G.TABLE_NAME
AND G.COLUMN_NAME = '*'
WHERE A.OWNER_NAME = 'OWNER명'
AND A.is_system_class = 'NO'
ORDER BY A.OWNER_NAME ,A.CLASS_NAME

-- 큐브리드에서는 전체건수를 제공하지 않는다
--결과를 다시 질의하면 테이블의 건수를 알수있다
select 'select ''' + class_name + ''', count(*) from ' + class_name from db_class where is_system_class='NO'

② 컬럼 정의서 추출 : ‘OWNER명’을 수정하여 수행(버전 10.X 기준)

컬럼 정의서 추출 SQL

--10.x 버전
SELECT A.CLASS_NAME AS 테이블명_1
, B.ATTR_NAME AS 컬럼영문명_2
, B.COMMENT AS 한글컬럼명_3
, B.IS_NULLABLE AS NULL여부_6
, B.DATA_TYPE AS 데이터타입_7
, B.PREC AS 데이터길이_8
, CASE WHEN D.KEY_ATTR_NAME IS NOT NULL THEN 'Y' END AS PK정보_9
, CASE WHEN F.KEY_ATTR_NAME IS NOT NULL THEN 'Y' END AS FK정보_10
, B.DEFAULT_VALUE AS 제약조건_11
, B.DEF_ORDER + 1 AS 컬럼순서
FROM DB_CLASS A
INNER JOIN DB_ATTRIBUTE B
ON B.CLASS_NAME = A.CLASS_NAME
LEFT JOIN DB_INDEX C
ON C.IS_PRIMARY_KEY = 'YES'
AND C.CLASS_NAME = B.CLASS_NAME
LEFT JOIN DB_INDEX_KEY D
ON D.CLASS_NAME = C.CLASS_NAME
AND D.INDEX_NAME = C.INDEX_NAME
AND D.KEY_ATTR_NAME = B.ATTR_NAME
LEFT JOIN DB_INDEX E
ON E.IS_FOREIGN_KEY = 'YES'
AND E.CLASS_NAME = B.CLASS_NAME
LEFT JOIN DB_INDEX_KEY F
ON F.CLASS_NAME = E.CLASS_NAME
AND F.INDEX_NAME = E.INDEX_NAME
AND F.KEY_ATTR_NAME = B.ATTR_NAME
WHERE A.OWNER_NAME = 'OWNER명 입력'
ORDER BY B.DEF_ORDER

--9.x 버전

SELECT A.OWNER_NAME AS OWNER명
A.CLASS_NAME AS 테이블명_1
, B.ATTR_NAME AS 컬럼영문명_2
, G.DESCRIPTION AS 한글컬럼명_3 --DBA권한 필요
, B.IS_NULLABLE AS NULL여부_6
, B.DATA_TYPE AS 데이터타입_7
, B.PREC AS 데이터길이_8
, CASE WHEN D.KEY_ATTR_NAME IS NOT NULL THEN 'Y' END AS PK정보_9
, CASE WHEN F.KEY_ATTR_NAME IS NOT NULL THEN 'Y' END AS FK정보_10
, B.DEFAULT_VALUE AS 제약조건_11
, B.DEF_ORDER + 1 AS 컬럼순서
FROM DB_CLASS A
INNER JOIN DB_ATTRIBUTE B
ON B.CLASS_NAME = A.CLASS_NAME
LEFT JOIN DB_INDEX C
ON C.IS_PRIMARY_KEY = 'YES'
AND C.CLASS_NAME = B.CLASS_NAME
LEFT JOIN DB_INDEX_KEY D
ON D.CLASS_NAME = C.CLASS_NAME
AND D.INDEX_NAME = C.INDEX_NAME
AND D.KEY_ATTR_NAME = B.ATTR_NAME
LEFT JOIN DB_INDEX E
ON E.IS_FOREIGN_KEY = 'YES'
AND E.CLASS_NAME = B.CLASS_NAME
LEFT JOIN DB_INDEX_KEY F
ON F.CLASS_NAME = E.CLASS_NAME
AND F.INDEX_NAME = E.INDEX_NAME
AND F.KEY_ATTR_NAME = B.ATTR_NAME
LEFT JOIN _CUB_SCHEMA_COMMENTS G
ON A.CLASS_NAME = G.TABLE_NAME
AND B.ATTR_NAME = G.COLUMN_NAME
AND G.COLUMN_NAME <> '*'
WHERE A.OWNER_NAME = 'OWNER명 입력'
AND A.is_system_class = 'NO'
ORDER BY A.CLASS_NAME , B.DEF_ORDER + 1

󰊵 [SYBASE] SQL 예시

① 테이블 정의서 추출 : ‘OWNER명’을 수정하여 수행

테이블 정의서 추출 SQL

SELECT user_name(T.creator) as Owner명
, db_name() as DB명
, T.TABLE_NAME as 영문테이블명
, trim(T.remarks) as 한글테이블명
, count as row수
, b.crdate as 테이블등록일시
, b.expdate as 테이블수정일시
from systable as T
JOIN sysobjects as B
on T.object_id = B.id
where T.table_type = 'BASE'
and B.TYPE = 'U'
AND B.UID > 10
and user_name(T.creator) = 'Owner명' --Owner입력
ORDER BY user_name(T.creator), db_name(), T.TABLE_NAME

② 컬럼 정의서 추출 : ‘OWNER명’을 수정하여 수행

컬럼 정의서 추출 SQL

SELECT
user_name(T.creator) AS OWNER명,
T.Table_name AS '테이블명_1',
C.column_name AS '영문컬럼명_2',
trim(C.remarks) as '한글컬럼명_3',
case when C.nulls = 'N' then 'Y' else 'N' end as 'Null여부_6',
D.domain_name as '데이터타입_7',
C.width as '데이터길이_8',
C.Pkey as 'PK정보_9',
C."DEFAULT" as '제약조건_11',
row_number() over (PARTITION BY T.Table_name order by C.Column_id) as '컬럼순서'
from
systable as T
join SYSCOLUMN as C
on (T.table_id = C.table_id)
join SYSDOMAIN as D
on (C.domain_id = D.domain_id)
join sysobjects as E
on T.object_id = E.id
where T.table_type = 'BASE'
AND E.UID > 10
AND E.TYPE='U'
and user_name(T.creator) = 'OWNER 명 입력'
order by
T.table_id, C.column_id ;

󰊶 [TIBERO] SQL 예시

① 테이블 정의서 추출 : ‘OWNER명’을 수정하여 수행

테이블 정의서 추출 SQL

SELECT S1.TABLESPACE_NAME AS OWNER명, -- OWNER명
S1.TABLE_NAME AS 영문테이블명, -- 테이블명(영문),
COMMENTS AS 한글테이블명, -- 테이블명(한글),
NUM_ROWS AS 테이블볼륨, -- Row수.
S3.CREATED AS 생성일자, -- 생성일자
S3.LAST_DDL_TIME AS 수정일자 -- 수정일자
FROM USER_TABLES S1,
USER_TAB_COMMENTS S2,
USER_OBJECTS S3
WHERE S1.TABLE_NAME = S2.TABLE_NAME
AND S2.TABLE_TYPE = 'TABLE'
AND TABLESPACE_NAME IS NOT NULL
AND TABLESPACE_NAME = 'OWNER명' --OWNER명 입력
AND S1.TABLE_NAME = S3.OBJECT_NAME
AND S1.TABLE_NAME NOT LIKE '%temp%'
AND S1.TABLE_NAME NOT LIKE '%test%'
ORDER BY S1.TABLESPACE_NAME , S1.TABLE_NAME

② 컬럼 정의서 추출 : ‘OWNER명’을 수정하여 수행

컬럼 정의서 추출 SQL

SELECT A.OWNER AS OWNER명,
A.TABLE_NAME AS 테이블명_1,
A.COLUMN_NAME AS 컬럼영문명_2,
A.COL_CMT AS 한글컬럼명_3,
A.NULLABLE AS NULL여부_6,
A.DATA_TYPE AS 데이터타입_7,
A.LEN 데이터길이_8,
B.POS AS PK정보_9,
C.FK_POS FK정보_10,
A.DATA_DEFAULT AS 제약조건_11,
A.COLUMN_ID AS 컬럼순서
FROM
(SELECT S1.OWNER ,
S1.TABLE_NAME,
S3.COMMENTS AS TAB_CMT,
S1.COLUMN_NAME,
S2.COMMENTS AS COL_CMT,
S1.DATA_TYPE,
CASE WHEN S1.DATA_PRECISION IS NOT NULL THEN DATA_PRECISION||','||DATA_SCALE
ELSE TO_CHAR(S1.DATA_LENGTH)
END AS LEN,
NULLABLE,
COLUMN_ID,
DATA_DEFAULT
FROM ALL_TAB_COLUMNS S1,
ALL_COL_COMMENTS S2,
ALL_TAB_COMMENTS S3
WHERE S1.OWNER = S2.OWNER
AND S1.TABLE_NAME = S2.TABLE_NAME
AND S1.COLUMN_NAME = S2.COLUMN_NAME
AND S1.OWNER = S3.OWNER
AND S1.TABLE_NAME = S3.TABLE_NAME ) A,
(SELECT T1.OWNER, T1.TABLE_NAME, T2.COLUMN_NAME, 'PK'||POSITION AS POS
FROM (SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'P' )T1,
(SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME, COLUMN_NAME, POSITION
FROM USER_CONS_COLUMNS ) T2
WHERE T1.OWNER = T2.OWNER
AND T1.TABLE_NAME = T2.TABLE_NAME
AND T1.CONSTRAINT_NAME = T2.CONSTRAINT_NAME ) B,
(SELECT T1.OWNER, T1.TABLE_NAME, T2.COLUMN_NAME, 'FK'||POSITION AS FK_POS
FROM (SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'R' )T1,
(SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME, COLUMN_NAME, POSITION
FROM USER_CONS_COLUMNS ) T2
WHERE T1.OWNER = T2.OWNER
AND T1.TABLE_NAME = T2.TABLE_NAME
AND T1.CONSTRAINT_NAME = T2.CONSTRAINT_NAME ) C
WHERE A.OWNER = B.OWNER(+)
AND A.TABLE_NAME = B.TABLE_NAME(+)
AND A.COLUMN_NAME = B.COLUMN_NAME(+)
AND A.OWNER = C.OWNER(+)
AND A.TABLE_NAME = C.TABLE_NAME(+)
AND A.COLUMN_NAME = C.COLUMN_NAME(+)
AND A.OWNER = 'OWNER명 입력'
ORDER BY A.TABLE_NAME, A.COLUMN_ID

728x90

저작자표시

'SQL' 카테고리의 다른 글

MS SQL 미사용 테이블 확인 SQL (0)	2022.03.24
오라클 패턴 검색 TRANSLATE (0)	2022.01.22
오라클 Exists SQL 튜닝 (0)	2022.01.22
오라클 Not In SQL 튜닝 (0)	2022.01.22

728x90

MS-SQL 미사용 테이블 확인 SQL

SQL Server의 SYS.dm_db_index_usage_stats 뷰의 최종접근일시 정보(last_user_scan, last_user_update 등)를 기반으로 미사용 Object를 식별

SELECT  "?" AS dbname,
        A.object_id,
        A.objectname,
        A.rowcnt,
        A.createdate,
        A.modifydate,
        B.database_id,
        B.object_id,
        B.last_user_seek,
        B.last_user_scan,
        B.last_user_lookup,
        B.last_user_update,
        B.last_system_scan
FROM    (
            SELECT
                    A.object_id, -- 오브젝트 아이디
                    object_name(A.object_id) AS objectname, -- 오브젝트 이름
                    max(C.row_count) as rowcnt, -- 오브젝트 행 수
                    max(B.create_date) as createdate, -- 오브젝트 생성일
                    max(B.modify_date) as modifydate -- 오브젝트 마지막 수정일
            FROM    sys.indexes AS A
                    INNER JOIN sys.objects AS B ON A.object_id = B.object_id
                    INNER JOIN sys.dm_db_partition_stats AS C ON C.object_id = B.object_id
            WHERE
                    B.type IN (''U'', ''V'')
            GROUP BY
                    A.object_id
        ) AS A LEFT JOIN (
            SELECT  database_id, -- 데이터베이스 아이디
                    object_id, -- 오브젝트 아이디
                    MAX(last_user_seek) AS last_user_seek, -- 마지막 Seek 일
                    MAX(last_user_scan) AS last_user_scan, -- 마지막 Scan 일
                    MAX(last_user_lookup) AS last_user_lookup, -- 마지막 lookup 일
                    MAX(last_user_update) AS last_user_update, -- 마지막 update 일
                    MAX(last_system_scan) AS last_system_scan -- 마지막 시스템 Scan
            FROM    sys.dm_db_index_usage_stats
            WHERE   db_name(database_id) = "?" -- 해당 데이터베이스
            GROUP BY
                    database_id,
                    object_id
        ) AS B ON A.object_id = B.object_id
WHERE
        B.object_id is null
        AND "?" != ''tempdb''

728x90

저작자표시

'SQL' 카테고리의 다른 글

DBMS별 테이블 컬럼 정의서 추출 - 오라클, MYSQL, MSSQL, CUBRID, SYBASE, TIBERO (0)	2022.03.29
오라클 패턴 검색 TRANSLATE (0)	2022.01.22
오라클 Exists SQL 튜닝 (0)	2022.01.22
오라클 Not In SQL 튜닝 (0)	2022.01.22

728x90

오라클 데이터 패턴 검색

Z : 숫자

C : 영문대문자

c : 영문소문자

특수기호 그대로 표시

나머지 : H (한글)

SELECT

    TRANSLATE(RSDN_RGST_NO, '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz ()*&^%$#@!-_+={}[]|":;<>,.?/~`，。：？（）' || TRANSLATE(RSDN_RGST_NO, '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz ()*&^%$#@!-_+={}[]|":;<>,.?/~`，。：？（）', 'ZZZZZZZZZZCCCCCCCCCCCCCCCCCCCCCCCCCCccccccccccccccccccccccccccB()*&^%$#@!-_+={}[]|":;<>,.?/~`，。：？（）'), 'ZZZZZZZZZZCCCCCCCCCCCCCCCCCCCCCCCCCCccccccccccccccccccccccccccB()*&^%$#@!-_+={}[]|":;<>,.?/~`，。：？（）HHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH'),
    COUNT(*) AS CNT
FROM TB_CUSTOMER10
GROUP BY TRANSLATE(RSDN_RGST_NO, '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz ()*&^%$#@!-_+={}[]|":;<>,.?/~`，。：？（）' || TRANSLATE(RSDN_RGST_NO, '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz ()*&^%$#@!-_+={}[]|":;<>,.?/~`，。：？（）', 'ZZZZZZZZZZCCCCCCCCCCCCCCCCCCCCCCCCCCccccccccccccccccccccccccccB()*&^%$#@!-_+={}[]|":;<>,.?/~`，。：？（）'), 'ZZZZZZZZZZCCCCCCCCCCCCCCCCCCCCCCCCCCccccccccccccccccccccccccccB()*&^%$#@!-_+={}[]|":;<>,.?/~`，。：？（）HHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH');
;

728x90

저작자표시

'SQL' 카테고리의 다른 글

DBMS별 테이블 컬럼 정의서 추출 - 오라클, MYSQL, MSSQL, CUBRID, SYBASE, TIBERO (0)	2022.03.29
MS SQL 미사용 테이블 확인 SQL (0)	2022.03.24
오라클 Exists SQL 튜닝 (0)	2022.01.22
오라클 Not In SQL 튜닝 (0)	2022.01.22

728x90

대량의 data를 를 Exists 으로 연결시 HASH SEMI JOIN 고려
SELECT deptno, dname
FROM dept d
WHERE EXISTS (SELECT /*+ HASH_SJ*/ * FROM emp WHERE deptno = d.deptno AND comm > 500);

728x90

저작자표시

'SQL' 카테고리의 다른 글

DBMS별 테이블 컬럼 정의서 추출 - 오라클, MYSQL, MSSQL, CUBRID, SYBASE, TIBERO (0)	2022.03.29
MS SQL 미사용 테이블 확인 SQL (0)	2022.03.24
오라클 패턴 검색 TRANSLATE (0)	2022.01.22
오라클 Not In SQL 튜닝 (0)	2022.01.22

728x90

대량의 data를 를 NOT IN 으로 연결시 HASH ANTI JOIN 고려
SELECT empno, ename
FROM emp e
WHERE deptno NOT IN (SELECT /*+ HASH_AJ */ deptno FROM dept WHERE loc = ‘CHICAGO’)
AND deptno IS NOT NULL;

728x90

저작자표시

'SQL' 카테고리의 다른 글

DBMS별 테이블 컬럼 정의서 추출 - 오라클, MYSQL, MSSQL, CUBRID, SYBASE, TIBERO (0)	2022.03.29
MS SQL 미사용 테이블 확인 SQL (0)	2022.03.24
오라클 패턴 검색 TRANSLATE (0)	2022.01.22
오라클 Exists SQL 튜닝 (0)	2022.01.22

유영락-정보시스템 감리도구를 통해 생산성 향상

SQL

DBMS별 테이블 컬럼 정의서 추출 - 오라클, MYSQL, MSSQL, CUBRID, SYBASE, TIBERO

'SQL' 카테고리의 다른 글

MS SQL 미사용 테이블 확인 SQL

'SQL' 카테고리의 다른 글

오라클 패턴 검색 TRANSLATE

'SQL' 카테고리의 다른 글

오라클 Exists SQL 튜닝

'SQL' 카테고리의 다른 글

오라클 Not In SQL 튜닝

'SQL' 카테고리의 다른 글

+ Recent posts

티스토리툴바