|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 |
/* Find the heap in each database (tables without a clustered index). Original code from: //sqlserverpedia.com/wiki/Find_Tables_Without_Primary_Keys Modified to 1. Return additional columns (object_id, type, create_date, modify_date, last_user_update), 2. Add table aliases to all columns. 3. Join to sys.schemas to get the proper schema name (instead of using schema_name function). 4. Use o.name (instead of using the object_name function). 5. Add check to not include tables with a type of "S" (system base tables, ie: sys.sysfiles1). 6. Get results for all databases, not just the active one. 7. Put results from all databases into one temp table and return them in one query (makes copying into Excel easier). 8. Calculate the last_user_access (max of the four last_user_ columns). */ IF OBJECT_ID('tempdb..#Heaps') IS NOT NULL DROP TABLE #Heaps; SELECT DBName = db_name(), s.name AS [schema], o.name AS [table], o.object_id, o.type, o.create_date, o.modify_date, p.rows, ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates, ius.last_user_seek, ius.last_user_scan, ius.last_user_lookup, ius.last_user_update INTO #Heaps FROM sys.indexes i JOIN sys.objects o ON i.object_id = o.object_id JOIN sys.schemas s ON o.schema_id = s.schema_id JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id LEFT JOIN sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id WHERE i.type_desc = 'HEAP' AND 1=2 ORDER BY rows DESC; EXECUTE sp_msforeachdb 'INSERT INTO #Heaps SELECT DBName = ''?'', s.name AS [schema], o.name AS [table], o.object_id, o.type, o.create_date, o.modify_date, p.rows, ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates, ius.last_user_seek, ius.last_user_scan, ius.last_user_lookup, ius.last_user_update FROM [?].sys.indexes i JOIN [?].sys.objects o ON i.object_id = o.object_id JOIN [?].sys.schemas s ON o.schema_id = s.schema_id JOIN [?].sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id LEFT JOIN [?].sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id WHERE i.type_desc = ''HEAP'' AND o.type NOT IN (''S'') ORDER BY rows desc'; SELECT h.*, MAX(ca1.last_user_access) AS last_user_access FROM #Heaps h -- unpivot these four date columns so we can get a max from them. CROSS APPLY (SELECT h.last_user_seek UNION ALL SELECT h.last_user_scan UNION ALL SELECT h.last_user_lookup UNION ALL SELECT h.last_user_update) ca1 (last_user_access) WHERE h.DBName NOT IN ('master','model','msdb','tempdb') GROUP BY h.DBName, h.[schema], h.[table], h.object_id, h.type, h.create_date, h.modify_date, h.rows, h.user_seeks, h.user_scans, h.user_lookups, h.user_updates, h.last_user_seek, h.last_user_scan, h.last_user_lookup, h.last_user_update ORDER BY last_user_access DESC, h.DBName, h.[schema], h.[table]; |
(2017-2022)
- Comments (0)
- Trackbacks (0)
No one has commented yet.
No trackbacks yet.
