-- 실행중인쿼리조회
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 참고 블로그
댓글