| 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 = '테이블명'
| 테이블별 데이터 건수 및 사이즈 확인 |
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)
)
------------------------------------------
'[prog] SQL - MSSQL' 카테고리의 다른 글
| MSSQL 데이터타입 / 변수사용 (0) | 2020.12.18 |
|---|---|
| Practical SQL Queries for Microsoft SQL Server 2008 R2 (0) | 2020.11.04 |
| SQL SERVER - SSMS tip (0) | 2020.10.15 |
| MSSQL 날짜변환 / 날짜계산CONVERT / DATETIME (0) | 2020.10.11 |
| SQL SERVER / 서버의 DB 를 로컬로 복사(가져오기) (0) | 2020.09.09 |
