MSSQL과 관련하여 자주 쓰는 스크립팅 정리. 구글링해서 본인이 직접 수정하여 사용한 경우가 많아 참조링크가 남지않았다...
-- 테이블 리스트 조회
DECLARE @DBNAME NVARCHAR(200) = 'DB'
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = @DBNAME
AND TABLE_SCHEMA = 'dbo'
ORDER BY TABLE_NAME-- 테이블 명세
DECLARE @DBNAME NVARCHAR(200) = 'DB'
SELECT (SELECT VALUE FROM ::FN_LISTEXTENDEDPROPERTY (NULL, 'SCHEMA', 'DBO', 'TABLE', A.TABLE_NAME , DEFAULT, DEFAULT)) -- 테이블 명
,A.TABLE_NAME -- 테이블 ID
, (SELECT DISTINCT VALUE FROM ::FN_LISTEXTENDEDPROPERTY (NULL, 'SCHEMA', 'DBO', 'TABLE', A.TABLE_NAME , 'COLUMN', DEFAULT)
WHERE OBJNAME = A.COLUMN_NAME collate latin1_general_cs_as
) -- 컬럼 명
, A.COLUMN_NAME -- 컬럼ID
,A.DATA_TYPE --DATATYPE
,A.CHARACTER_MAXIMUM_LENGTH --DATATYPE
,(CASE WHEN (SELECT COUNT(*)
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME), 'ISPRIMARYKEY') = 1
AND TABLE_NAME = A.TABLE_NAME
AND COLUMN_NAME = A.COLUMN_NAME)
= 1 THEN 'Y'
ELSE '' END
) -- PK
,'' --FK
,IS_NULLABLE -- NULL 허용
FROM INFORMATION_SCHEMA.COLUMNS A
WHERE A.TABLE_CATALOG = @DBNAME
AND A.TABLE_SCHEMA = 'dbo'
ORDER BY A.TABLE_CATALOG, A.TABLE_SCHEMA, A.TABLE_NAME, A.COLUMN_NAME-- 테이블 주석 조회
DECLARE @DBNAME NVARCHAR(200) = 'DB'
SELECT OBJTYPE, OBJNAME, NAME, VALUE
FROM ::FN_LISTEXTENDEDPROPERTY (NULL, 'SCHEMA', 'DBO', 'TABLE', @DBNAME, DEFAULT, DEFAULT)-- 컬럼 코멘트 조회
DECLARE @TABLENAME NVARCHAR(200) = 'TABLE'
SELECT OBJTYPE, OBJNAME, NAME, VALUE
FROM ::FN_LISTEXTENDEDPROPERTY(NULL, 'SCHEMA', 'DBO', 'TABLE', @TABLENAME, 'COLUMN', DEFAULT)-- 링크드 서버 테이블 주석 조회 하기.
EXECUTE [링크드이름].[링크드DB명].dbo.sp_executesql
N'SELECT OBJTYPE, OBJNAME, NAME, VALUE
FROM ::FN_LISTEXTENDEDPROPERTY (NULL, ''SCHEMA'', ''DBO'', ''TABLE'', ''@TABLENAME'', DEFAULT, DEFAULT)'
-- 해당 테이블 혹은 컬럼이 사용된 프로시져 조회.
DECLARE @TABLENAME NVARCHAR(200) = 'TABLE'
select SO.NAME
from syscomments SC
join sysobjects so on sc.id = so.id
where so.xtype='p'
and sc.text like '%' + @TABLENAME + '%'-- 특정 컬럼 사용하는 테이블 확인
DECLARE @COLUMNNAME NVARCHAR(200) = 'COLUMN'
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 = @COLUMNNAME-- 뷰에서 사용된 테이블과 컬럼 정보 가져오기
SELECT vcu.VIEW_NAME, vcu.TABLE_NAME, vuc.COLUMN_NAME
FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE AS vcu-- 프로시져 변경 이력
DECLARE @PROCEDURENAME NVARCHAR(200) = 'PROCEDURE'
SELECT *
FROM sys.sql_modules sm
inner join sys.all_objects ao
on sm.object_id = ao.object_id
WHERE name = @PROCEDURENAME
order by modify_date desc
-- 프로시저 정보 조회
DECLARE @PROCEDURENAME NVARCHAR(200) = 'PROCEDURE'
SELECT 'PARAMETER_NAME' = A.NAME,
'TYPE' = TYPE_NAME(A.USER_TYPE_ID),
'LENGTH' = A.MAX_LENGTH,
'NULLABLE' = T.IS_NULLABLE,
'PREC' = CASE WHEN TYPE_NAME(A.SYSTEM_TYPE_ID) = 'UNIQUEIDENTIFIER' THEN A.PRECISION
ELSE ODBCPREC(A.SYSTEM_TYPE_ID, A.MAX_LENGTH, A.PRECISION)
END,
'SCALE' = ODBCSCALE(A.SYSTEM_TYPE_ID, A.SCALE),
'PARAM_ORDER' = A.PARAMETER_ID,
'COLLATION' = CONVERT(SYSNAME,
CASE
WHEN A.SYSTEM_TYPE_ID IN (35, 99, 167, 175, 231, 239) -- STRING type
THEN SERVERPROPERTY('COLLATION')
END)
FROM SYS.PARAMETERS A
INNER JOIN SYS.TYPES AS T
ON T.SYSTEM_TYPE_ID = A.SYSTEM_TYPE_ID
AND T.USER_TYPE_ID = A.USER_TYPE_ID
WHERE OBJECT_ID = OBJECT_ID(@PROCEDURENAME)
-- 특정기간에 해당하는 모든 일자 구하기
SELECT CONVERT (VARCHAR, DATEADD(D,NUMBER, '시작일'), 112)
FROM master..spt_values
WHERE TYPE = 'P'
AND NUMBER <= DATEDIFF(D, '시작일','종료일') -- 체결문자 반복 만들기
WITH TEMP AS
(
SELECT 1 AS SEQ
UNION ALL
SELECT TEMP.SEQ + 1 AS SEQ
FROM TEMP
WHERE TEMP.SEQ < (26 * 61)
)
SELECT SEQ, SEQ / 61 + 1 AS CH ,(SEQ) % 61 AS NUM
, CHAR(((SEQ) / 61) +1 +64)
+ REPLICATE('0', 3 - LEN(CONVERT(nvarchar , (SEQ) % 61 ))) + CONVERT(nvarchar , (SEQ) % 61 ) AS 변환값
FROM TEMP OPTION( MAXRECURSION 0) -- 재귀호출 100 이상인경우 옵션 추가 필요 기본 100-- 최근 쿼리 실행 이력 확인
SELECT TOP 100
QS.creation_time,
--SQL
SUBSTRING(ST.text,(QS.statement_start_offset/2)+1,
((CASE QS.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
ELSE QS.statement_end_offset END - QS.statement_start_offset)/2) + 1
) AS statement_text,
--실행한 SQL
ST.text,
--실행 계획
QS.total_worker_time,
QS.last_worker_time,
QS.max_worker_time,
QS.min_worker_time
FROM
sys.dm_exec_query_stats QS
--키워드
CROSS APPLY
sys.dm_exec_sql_text(QS.sql_handle) ST
ORDER BY
QS.creation_time DESC
-- 일련번호 반복
DECLARE @int INT
DECLARE @int2 INT
set @int = 65
WHILE( @int <= 78 )
BEGIN
SET @int2 = 1
WHILE( @int2 < 60 )
BEGIN
SELECT CHAR(@int) + CONVERT(nvarchar , @int2)
-- ASCII & NUMBER
SET @int2 = @int2 +1
END
SET @int = @int +1
END
-- IDENTITY COLUMN (자동증가컬럼)
DECLARE @TABLENAME NVARCHAR(200) = 'TABLE'
-- 현재 최종 증가값 확인
DBCC CHECKIDENT(@TABLENAME, NORESEED)
-- 최종 증가값 0으로 설정. 테이블 데이터 전부 삭제 후, 실행
DBCC CHECKIDENT(@TABLENAME, RESEED, 0)-- PK FK 전부 조회 .
SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc IN ('FOREIGN_KEY_CONSTRAINT','PRIMARY_KEY_CONSTRAINT')-- LOCK 확인 해제,
EXEC SP_LOCK -- LOCK 확인 , @spid 확인.
DBCC INPUTBUFFER( @SPID ) -- LOCK 쿼리 조회
KILL @SPID -- 해당 LOCK 해제
평범한 개발자