Rank: Administration
Medals: Groups: Administrators
Joined: 10/20/2012 Posts: 106 Points: 327 Location: Tbilisi
Thanks: 0 times Was thanked: 0 time(s) in 0 post(s)
|
-- სქემების გათვალისწინებით იგივე BB კოდი:WITH ct AS (SELECT i.object_id, OBJECT_NAME(i.object_id) AS table_name, i.index_id, SUM(i.user_seeks) AS seeks, SUM(i.user_scans) AS scans, SUM(i.user_lookups) AS lookups, sys.schemas.name FROM sys.tables AS t INNER JOIN sys.dm_db_index_usage_stats AS i ON t.object_id = i.object_id INNER JOIN sys.schemas ON t.schema_id = sys.schemas.schema_id GROUP BY i.object_id, i.index_id, sys.schemas.name) SELECT 'drop index ' + QUOTENAME(stats.name) + '.' + QUOTENAME(stats.table_name) + '.' + i.name AS DropIndexStatement, stats.name AS schemaName, stats.table_name AS TableName, i.name AS IndexName, i.type_desc AS IndexType, stats.seeks + stats.scans + stats.lookups AS TotalAccesses, stats.seeks AS Seeks, stats.scans AS Scans, stats.lookups AS Lookups FROM ct AS stats INNER JOIN sys.indexes AS i ON stats.object_id = i.object_id AND stats.index_id = i.index_id WHERE (stats.seeks + stats.scans + stats.lookups = 0) AND (i.type_desc = 'NONCLUSTERED') AND (i.is_primary_key = 0) AND (i.is_unique = 0) AND (stats.table_name NOT LIKE 'sys%') ORDER BY TableName, IndexName
|