[TIP_MSSQL] tips for mssql #tip #mssql #helptext #depends #refreshview

- 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

- Linked server 
- 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부터 시작