MSSQL - TIP 모음(tips for mssql)

MSSQL - TIP 모음(tips for mssql)

-- https://msdn.microsoft.com/ko-kr/library/ms176007(v=sql.90).aspx

-- object의 정의
exec sp_helptext N'NAME'

-- object의 종속성 정보 in current DB
exec sp_depends N'NAME'

-- view의 메타데이터 업데이트
exec sp_refreshview N'VIEW_NAME'

-- 테이블 존재시 삭제
IF OBJECT_ID('dbo.Scores', 'U') IS NOT NULL
	DROP TABLE dbo.Scores; 

-- 임시 테이블 존재시 삭제
IF OBJECT_ID('tempdb.dbo.#T', 'U') IS NOT NULL
	DROP TABLE #T;
    
-- DB List
SELECT name FROM master.dbo.sysdatabases
EXEC sp_databases

-- Encryption & Decryption
SELECT * FROM SYS.SYMMETRIC_KEYS
SELECT * FROM SYS.ASYMMETRIC_KEYS
SELECT * FROM SYS.CERTIFICATES

-- https://learn.microsoft.com/ko-kr/sql/relational-databases/linked-servers/create-linked-servers-sql-server-database-engine?view=sql-server-ver16
-- MSSQL 연결된 서버 확인
SELECT * FROM master.dbo.sysservers WHERE srvname = ''
-- MSSQL 연결계정 확인
SELECT * FROM master.sys.linked_logins WHERE server_id = ''
-- MSSQL 연결된 서버 생성
EXEC sp_addlinkedserver @server = '[연결된 서버별칭]', @srvproduct = '', @provider = 'SQLOLEDB', @datasrc = '[서버 아이피]', @catalog = '[데이터 베이스명]'
-- MSSQL 연결계정 생성
EXEC sp_addlinkedsrvlogin @rmtsrvname= '[연결된 서버별칭]', @useself= 'false', @rmtuser = '[사용자 이름]', @rmtpassword = '[사용자 암호]'
-- MSSQL 연결계정 삭제
EXEC sp_droplinkedsrvlogin @rmtsrvname= '[연결된 서버별칭]', @locallogin = NULL
-- MSSQL 연결된 서버 삭제
EXEC sp_dropserver @server = '[연결된 서버별칭]'

-- Lock 걸린 프로세스의 SPID 확인
EXEC sp_lock
EXEC SP_WHO2
SELECT * FROM SYS.sysprocesses WHERE blocked > 0

-- 해당 SPID 변화 조사
dbcc opentran

-- 해당 SPID의 쿼리 조사
dbcc inputbuffer ( #SPID# )

-- 해당 Lock을 발생한 쿼리가 중요하지 않을 경우 Kill
kill #SPID#

-- Lock으로 의심되는 spid만 추출
select p.status,  p.program_name, p.hostname
, p.spid, p.blocked, p.kpid, p.cpu, p.physical_io, p.waittype, p.waittime, p.lastwaittype, p.waitresource, p.dbid
, p.uid, p.memusage, p.login_time, p.last_batch
, p.ecid, p.open_tran, p.sid, p.hostprocess
, p.cmd, p.nt_domain, p.nt_username, p.net_address, p.net_library, p.loginame
, p.context_info, p.sql_handle, p.stmt_start, p.stmt_end
FROM master..sysprocesses p
where (status like 'run%' or waittime > 0 OR blocked <> 0 OR open_tran <> 0
OR EXISTS(SELECT * FROM master..sysprocesses p1 where p.spid = p1.blocked and p1.spid <> p1.blocked)
  )
AND spid > 50
AND spid <> @@spid
ORDER BY CASE WHEN status like 'run%' THEN 0 ELSE 1 END
, waittime DESC, open_tran desc

-- DB별 Log Size, Log Space 사용량
DBCC SQLPERF(LOGSPACE)

-- Log 파일 정보
DBCC loginfo
exec sp_helpfile

-- Log 상태 (log_reuse_wait확인)
select * from sys.databases

-- 쿼리 실행 이력
SELECT P.spid,E.name as DBname,P.login_time, P.last_batch, P.status,P.program_name,P.cmd,P.loginame,C.client_net_address,D.text
FROM sys.sysprocesses AS P
INNER JOIN sys.dm_exec_connections AS C
ON P.spid = C.session_id
CROSS APPLY sys.dm_exec_sql_text (P . sql_handle) D
INNER JOIN sys.databases AS E
on D.dbid = E.database_id
ORDER BY login_time

-- 쿼리 사용 분석
set statistics io on 
set statistics profile on 
set statistics time on
{QUERY}
set statistics io off
set statistics profile off
set statistics time off

-- 버퍼 비우기
CHECKPOINT;
GO 
DBCC DROPCLEANBUFFERS; 
GO

-- foreign key 제약조건 걸린 table 확인
SELECT f.name, OBJECT_NAME(f.parent_object_id) TableName, COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName
FROM sys.foreign_keys AS f INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id INNER JOIN sys.tables t ON t.OBJECT_ID = fc.referenced_object_id
WHERE OBJECT_NAME (f.referenced_object_id) = '테이블명'

-- 외래키 제약조건 확인
sp_helpconstraint @objname = '테이블명'

-- DB복원 후 복원 중 표시 해제
RESTORE DATABASE LAIGODB WITH RECOVERY

-- Foreign Key 추가
ALTER TABLE [target_table] ADD CONSTRAINT [FK_Name] FOREIGN KEY([FK_column]) REFERENCES [ref_table] (ref_column)
{ON DELETE CASCADE ON UPDATE CASCADE}

-- 테이블 코멘트 추가
EXEC sp_addextendedproperty 'MS_Description', '테이블설명', 'USER', DBO, 'TABLE', 테이블이름
-- 테이블 코멘트 삭제
EXEC sp_dropextendedproperty 'MS_Description', 'SCHEMA', DBO, 'TABLE', 테이블이름, DEFAULT, DEFAULT
-- 칼럼 코멘트 추가
EXEC sp_addextendedproperty 'MS_Description', '컬럼설명', 'USER', DBO, 'TABLE', 테이블이름, 'COLUMN', 칼럼이름
-- 칼럼 코멘트 삭제
EXEC sp_dropextendedproperty 'MS_Description', 'SCHEMA', DBO, 'TABLE', 테이블이름, 'COLUMN', 칼럼이름

-- auto increment 초기화
DBCC CHECKIDENT( [table_name] , RESEED, 0) -- 1부터 시작