MSSQL 검색/업무에 도움되는 쿼리

-- 실행중인쿼리조회

SELECT sqltext.TEXT, req.session_id, req.status, req.command, req.cpu_time, req.total_elapsed_time FROM sys.dm_exec_requests req 
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

--프로시저내용조회

EXEC UP_SP_HELP_TABLE

--테이블 정보 보기

EXEC UP_SP_HELP_TABLE T1

-- 실행중인 쿼리 보기

SELECT sqltext.TEXT, req.session_id, req.status, req.command, req.cpu_time, req.total_elapsed_time 
FROM sys.dm_exec_requests req CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

-- LIKE 포함하는 프로시저 검색

SELECT OBJECT_NAME(object_id), 
 OBJECT_DEFINITION(object_id)
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%into TA11%'

-- 설명 추가

--열 설명 추가
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'설명|
|
|' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T2', @level2type=N'COLUMN',@level2name=N'IDATE'

-- 테이블 설명 추가
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'설명', 
                                @level0type=N'SCHEMA', @level0name=N'dbo', 
                                @level1type=N'TABLE',  @level1name=N'T2';

--백업

--백업쿼리
SELECT *
INTO T16_BAK
FROM T16 WHERE PNO ='101';

-- 백업 테이블에 데이터 복사
INSERT INTO T16_BAK
SELECT *FROM T16 WHERE PNO ='101'

--원복 

INSERT
INTO T2 --넣을 테이블
SELECT *
FROM T2_D --넣고 싶은 데이터가 있는 테이블
WHERE NO = '';

--테이블 구조만 복사

select * into T1_BAK
from T1
where 1 = 2 --데이터가 나오지 않음을 이용

--테이블정보

SELECT DOMAIN_NAME, COLUMN_NAME, * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ''

--SP 이름 정의

SELECT OBJECT_NAME(object_id), OBJECT_DEFINITION(object_id) 
FROM sys.procedures WHERE OBJECT_DEFINITION(object_id) LIKE '%%'

-사용되고 있는 SP 이름 검색

SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%%'
AND ROUTINE_TYPE='PROCEDURE'
ORDER BY ROUTINE_NAME

--프로시저,함수 수정이력 

SELECT A.TEXT, *
FROM SYSCOMMENTS A,
  SYSOBJECTS B
WHERE B.XTYPE  IN ('P','FN')
AND A.ID = B.ID
AND B.NAME = ''

SELECT AO.NAME, MODIFY_DATE AS UPDATE_DATE ,*
FROM SYS.SQL_MODULES SM, SYS.ALL_OBJECTS AO 
WHERE SM.OBJECT_ID = AO.OBJECT_ID AND AO.TYPE = 'P'
AND AO.NAME = '' 
ORDER BY MODIFY_DATE DESC

--쿼리이력

SELECT 
	db_name(st.dbid) DBName 
	, object_schema_name(objectid, st.dbid) SchemaName 
	, object_name(objectid, st.dbid) SPName 
	, qs.total_elapsed_time 
	, creation_time 
	, last_execution_time 
	, text 
FROM 
	sys.dm_exec_query_stats qs 
	CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle)st 
	JOIN sys.dm_exec_cached_plans cp ON qs.plan_handle = cp.plan_handle
ORDER BY last_execution_time desc

--에러내역 한글로 조회하기

SELECT TEXT
FROM  SYS.MESSAGES
WHERE  LANGUAGE_ID  = 1042
AND   MESSAGE_ID  = --에러코드

--프로시저 텍스트로 봄

EXEC sp_helptext 'SP_';

--임시테이블 조회

USE tempdb;
SELECT name
FROM sys.tables
WHERE name LIKE '#%';

--테이블별 레코드건수

USE db명
GO
SELECT B.NAME ,A.ROWS 
 FROM SYSINDEXES A  INNER JOIN  SYSOBJECTS B 
ON  A.ID = B.ID 
WHERE B.XTYPE = 'U' AND A.INDID < 2 AND ROWS > 0 ORDER BY B.NAME

--DB 캐쉬 버퍼 삭제

dbcc dropcleanbuffers

dbcc freeproccache

--프로시저내용조회

EXEC UP_SP_HELP_TABLE

--테이블의 간단한 정보

SP_SPACEUSED

--해당 테이블의 인덱스 정보

SP_HELPINDEX

--인덱스나 프로시저 등 스크립트 출력

SP_HELPTEXT

--트리거목록

SELECT 'SP_HELPTEXT ' + T.NAME
FROM  SYS.SYSOBJECTS AS T
INNER JOIN  SYS.SYSOBJECTS AS O
ON  T.PARENT_OBJ = O.ID
WHERE T.XTYPE ='TR' AND O.NAME LIKE '테이블명'

--트리거가 있는 테이블을 반환

SELECT DISTINCT  TOP 5  T.NAME AS TABLENAME
FROM SYS.OBJECTS T
INNER JOIN SYS.TRIGGERS TR
ON T.OBJECT_ID = TR.PARENT_ID
ORDER BY TABLENAME;

--컬럼명있는 테이블 검색

SELECT A.NAME AS COLUMNAME, B.NAME AS TABLENAME
FROM SYSCOLUMNS A LEFT JOIN SYSOBJECTS B
ON A.ID=B.ID
WHERE A.NAME='' --컬럼명

--접속자 확인

SELECT 
DB_NAME(DBID) AS WINPLUS_SIS, 
COUNT(DBID) AS 접속자수
FROM SYS.SYSPROCESSES 
WHERE  DBID > 0 
GROUP BY DBID ;

SELECT S.SPID, S.LOGINAME, S.LOGIN_TIME, S.LAST_BATCH, C.CLIENT_NET_ADDRESS 
FROM SYS.SYSPROCESSES S, SYS.DM_EXEC_CONNECTIONS C

-- 에러로그

MASTER..SP_ENUMERRORLOGS

SP_READERRORLOG 1

 

 

 

 

 

https://sagittariusof85s.tistory.com/225 참고 블로그