반응형
MSSQL 의 유용한 쿼리문
-- 1. DB 정보
EXEC sp_helpdb
-- 2. SP, Trigger, View Text View
EXEC sp_helptext 'nsp_AccountBlocknFree_Detail' --'SP Name'
-- 3. 모든 SP List
SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE o.xtype='P'
-- 4. 특정 테이블이 들어가는 모든 SP List
SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%nst_AccountBlocknFree%' AND o.xtype='P' -- 'Table Name'
-- 5. 모든 인덱스 리빌드
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
EXEC sp_updatestats
GO
-- 6. SP에서 사용하는 Table List
;WITH stored_procedures AS (
SELECT
oo.name AS table_name,
ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS row
FROM sysdepends d
INNER JOIN sysobjects o ON o.id=d.id
INNER JOIN sysobjects oo ON oo.id=d.depid
WHERE o.xtype = 'P' AND o.name LIKE '%nsp_BoardAnalysis_IN%' ) -- 'SP Name'
SELECT Table_name FROM stored_procedures
WHERE row = 1
-- 7. 모든 테이블의 사이즈(Bytes)
SELECT sob.name AS Table_Name,
SUM(sys.length) AS [Size_Table(Bytes)]
FROM sysobjects sob, syscolumns sys
WHERE sob.xtype='u' AND sys.id=sob.id
GROUP BY sob.name
-- 8. Identity Column이 없는 모든 테이블 List
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE
Table_NAME NOT IN
(
SELECT DISTINCT c.TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS c
INNER
JOIN sys.identity_columns ic
on
(c.COLUMN_NAME=ic.NAME)
)
AND
TABLE_TYPE ='BASE TABLE'
-- 9. Primary Key 와 Foreign Key가 걸린 모든 테이블 및 키이름 List
SELECT DISTINCT
Constraint_Name AS [Constraint],
Table_Schema AS [Schema],
Table_Name AS [TableName]
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
-- 10.특정 테이블에 걸린 Primary , Foreign Key List
SELECT DISTINCT
Constraint_Name AS [Constraint],
Table_Schema AS [Schema],
Table_Name AS [TableName] FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME='nst_Keyword' -- Table Name
-- 11. 모든 테이블의 레코드 수
CREATE TABLE #Tab
(
Table_Name [varchar](max),
Total_Records int
);
EXEC sp_MSForEachTable @command1=' Insert Into #Tab(Table_Name, Total_Records) SELECT ''?'', COUNT(*) FROM ?'
SELECT * FROM #Tab t ORDER BY t.Total_Records DESC;
DROP TABLE #Tab;
-- 12. SQL Server Version
SELECT @@VERSION AS Version_Name
-- 13. SQL Server Language
SELECT @@LANGUAGE AS Current_Language
-- 14. SQL Server Name
SELECT @@SERVERNAME AS 'Server_Name'
-- 15. Current Session ID
SELECT @@SPID AS 'Session_Id'
-- 16. 사용 가능한 HardDisk 공간
EXEC master..xp_fixeddrives
-- 17. 5일 동안 수정된 SP List
SELECT name,modify_date
FROM sys.objects
WHERE type='P'
AND DATEDIFF(D,modify_date,GETDATE())< 5 -- 5일동안 수정한 SP
-- 18. 특정 자료 타입을 사용하는 테이블, 컬럼 이름
SELECT OBJECT_NAME(c.OBJECT_ID) as Table_Name, c.name as Column_Name
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
WHERE t.name = 'bit'
--WHERE t.name = 'int'
--WHERE t.name = 'nvarchar'
-- 19. Primary Key가 없는 모든 테이블 List
SELECT name AS Table_Name
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0
ORDER BY Table_Name
-- 20. Foreign Key가 없는 모든 테이블 List
SELECT name AS Table_Name
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasForeignKey') = 0
ORDER BY Table_Name
-- 21.Identity가 없는 모든 테이블 List
SELECT name AS Table_Name
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasIdentity') = 0
ORDER BY Table_Name
-- 22. 이달의 첫 번째 날짜
SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-(DAY(GETDATE()))+1,GETDATE()),121) First_Date_Current_Month
-- 23. 이달의 마지막 날짜
SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-(DAY(GETDATE())), DATEADD(MONTH,1,GETDATE())),121) Last_Date_Current_Month
-- 24. 이전달의 마지막 날짜
SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-(DAY(GETDATE())),GETDATE()),121) Last_Date_Previous_Month
-- 25. 테이블의 모든 정보
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME='nst_GameNoticeList' -- Table Name반응형