ms-sql 사용하는데요.
데이터베이스 안에 있는 모든 테이블의 트리거와 index 등록상태를 알고 싶어서요.
어떻게 하면 되는지요...
조언 부탁드립니다.^^
SELECT NAME TRIGGER_NAME , OBJECT_NAME(PARENT_OBJ) TABLE_NAME , CASE OBJECTPROPERTY(ID,'EXECISTRIGGERDISABLED') WHEN 0 THEN 'ENABLED' ELSE 'DISABLED' END STATUS FROM SYSOBJECTS WHERE TYPE ='TR' GO
IF OBJECT_ID('INDEXVIEW','V') IS NOT NULL DROP VIEW INDEXVIEW GO
CREATE VIEW DBO.INDEXVIEW
AS /**************************************************************************************************** ** Creation Date: ? ** Modif Date : Nov. 27, 2002 ** Created By : avigneau ** Database : any ** Description : Reports on all indexes and / or heaps on user tables within a database ** Parameters : none ** Compatibility: SQL Server 6.X, 7.0, 2000 ** Remark : System tables are used to be compatible with version 6.x. But I believe it would still be difficult to obtain the same results using INFORMATION_SCHEMA views and new object and system property functions. ** Example : SELECT 'Showing All Indexes' AS Comments, I.* FROM dbo.INDEXVIEW I SELECT 'Showing Tables with Missing Clustered index' AS Comments, I.* FROM dbo.INDEXVIEW I WHERE ClusterType = 'HEAP' SELECT 'Showing Tables with Missing Primary Keys' AS Comments, I.* FROM dbo.INDEXVIEW I LEFT OUTER JOIN dbo.INDEXVIEW I2 ON I.TableID = I2.TableID AND I2.UniqueType = 'PRIMARY KEY' WHERE I2.TableID IS NULL SELECT 'Showing Possible Redundant Index keys' AS Comments, I.* FROM dbo.INDEXVIEW I JOIN dbo.INDEXVIEW I2 ON I.TableID = I2.TableID AND I.ColName1 = I2.ColName1 AND I.IndexName <> I2.IndexName SELECT 'Showing Possible Reverse Index keys' AS Comments, I.* FROM dbo.INDEXVIEW I JOIN dbo.INDEXVIEW I2 ON I.TableID = I2.TableID AND I.ColName1 = I2.ColName2 AND I.ColName2 = I2.ColName1 AND I.IndexName <> I2.IndexName ************************************************************************************************/
SELECT o.id AS TableID ,u.name Owner,o.name TableName, i.Indid AS IndexID , CASE i.name WHEN o.name THEN '** NONE **' ELSE i.name END AS IndexName, CASE i.indid WHEN 1 THEN 'CLUSTERED' WHEN 0 THEN 'HEAP' ELSE 'NONCLUSTERED' END AS ClusterType, CASE WHEN (i.status & 2048) > 0 THEN 'PRIMARY KEY' WHEN (i.status & (2|4096)) > 0 THEN 'UNIQUE' ELSE '' END AS UniqueType, CASE WHEN (i.status & (2048)) > 0 OR ((i.status & (4096)) > 0 ) THEN 'CONSTRAINT' WHEN i.indid = 0 THEN ' ' ELSE 'INDEX' END AS IndexType, -- This following part is non essential -- It is a pre char aggregate I use in other scripts -- to generate create and drop scripts CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 1) IS NULL THEN '' ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 1) END + CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 2) IS NULL THEN '' ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,2) END + CASE WHEN INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 3) IS NULL THEN '' ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,3) END + CASE WHEN INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 4) IS NULL THEN '' ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,4) END + CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 5) IS NULL THEN '' ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,5) END + CASE WHEN INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 6) IS NULL THEN '' ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,6) END + CASE WHEN INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 7) IS NULL THEN '' ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 7) END + CASE WHEN INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 8) IS NULL THEN '' ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,8) END + CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 9) IS NULL THEN '' ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,9) END + CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 10) IS NULL THEN '' ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,10) END + CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 11) IS NULL THEN '' ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,11) END + CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 12) IS NULL THEN '' ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,12) END + CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 13) IS NULL THEN '' ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,13) END + CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 14) IS NULL THEN '' ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,14) END + CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 15) IS NULL THEN '' ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,15) END + CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 16) IS NULL THEN '' ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,16) END AS AllColName, -- CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 1) IS NULL THEN '' ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,1) END AS ColName1, CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 2) IS NULL THEN '' ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,2) END AS ColName2, CASE WHEN INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 3) IS NULL THEN '' ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,3) END AS ColName3, CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 4) IS NULL THEN '' ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,4) END AS ColName4, CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 5) IS NULL THEN '' ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,5) END AS ColName5, CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 6) IS NULL THEN '' ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,6) END AS ColName6, CASE WHEN INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 7) IS NULL THEN '' ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,7) END AS ColName7, CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 8) IS NULL THEN '' ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,8) END AS ColName8, CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 9) IS NULL THEN '' ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,9) END AS ColName9, CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 10) IS NULL THEN '' ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,10) END AS ColName10, CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 11) IS NULL THEN '' ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,11) END AS ColName11, CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 12) IS NULL THEN '' ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,12) END AS ColName12, CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 13) IS NULL THEN '' ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,13) END AS ColName13, CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 14) IS NULL THEN '' ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,14) END AS ColName14, CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 15) IS NULL THEN '' ELSE INDEX_COL(u.name+'.'+QUOTENAME (o.name), i.indid,15) END AS ColName15, CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 16) IS NULL THEN '' ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,16) END AS ColName16 FROM sysobjects o (NOLOCK) LEFT OUTER JOIN sysindexes i (NOLOCK) ON o.id = i.id JOIN sysusers u (NOLOCK) ON o.uid = u.uid WHERE o.type = 'U' AND i.indid < 255 AND o.name NOT IN ('dtproperties') AND i.name NOT LIKE '_WA_Sys_%' -- because of SQL Server 7.0
GO
SELECT 'Showing All Indexes' AS Comments, I.* FROM dbo.INDEXVIEW I GO SELECT 'Showing Tables with Missing Clustered index' AS Comments, I.* FROM dbo.INDEXVIEW I WHERE ClusterType = 'HEAP' GO SELECT 'Showing Tables with Missing Primary Keys' AS Comments, I.* FROM dbo.INDEXVIEW I LEFT OUTER JOIN dbo.INDEXVIEW I2 ON I.TableID = I2.TableID AND I2.UniqueType = 'PRIMARY KEY' WHERE I2.TableID IS NULL GO SELECT 'Showing Possible Redundant Index keys' AS Comments , I.* FROM dbo.INDEXVIEW I JOIN dbo.INDEXVIEW I2 ON I.TableID = I2.TableID AND I.ColName1 = I2.ColName1 AND I.IndexName <> I2.IndexName ORDER BY I.TableName,I.IndexName GO SELECT 'Showing Possible Reverse Index keys' AS Comments , I.* FROM dbo.INDEXVIEW I JOIN dbo.INDEXVIEW I2 ON I.TableID = I2.TableID AND I.ColName1 = I2.ColName2 AND I.ColName2 = I2.ColName1 AND I.IndexName <> I2.IndexName GO
감사합니다. ^^