Vous êtes ici :
Cette requête produit une liste détaillée des tables et index présents dans la base courante.
Transact-SQL
|
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 |
/*------------------------------------------------------------------- [SCRIPT] tables and index [DATABASE] current [DESCRIPTION] informations about tables and index in the current database [MAJ PAR] DATAFLY - Arian Papillon -------------------------------------------------------------------*/ SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT OBJECT_NAME(o.object_id) AS object_name , SCHEMA_NAME(o.schema_id) AS schema_name , ISNULL( ( SELECT SUM(rows) FROM sys.indexes ixx JOIN sys.partitions px ON ixx.object_id = px.object_id AND ixx.index_id = px.index_id WHERE ixx.object_id = o.object_id AND ixx.index_id < 2 ) , ( SELECT MAX(rows) FROM sys.indexes ixx JOIN sys.partitions px ON ixx.object_id = px.object_id AND ixx.index_id = px.index_id WHERE ixx.object_id = o.object_id )) AS TableRowCount -- for in-memory optimized tables with columnstore , i.name AS index_name , CASE WHEN ISNULL( OBJECTPROPERTY(o.object_id, 'TableIsMemoryOptimized'), 0) = 1 AND i.type_desc = 'HEAP' THEN CAST('MEMORY OPTIMIZED' AS NVARCHAR(60)) ELSE i.type_desc END AS type_desc -- Colonnes , SUBSTRING( ( SELECT ',' + SC.name AS [text()] FROM sys.index_columns IC JOIN sys.columns SC ON IC.object_id = SC.object_id AND IC.column_id = SC.column_id WHERE IC.object_id = o.object_id AND IC.index_id = i.index_id AND IC.is_included_column = 0 ORDER BY IC.key_ordinal FOR XML PATH('')) , 2 , 1000) IndexCols , SUBSTRING( ( SELECT ',' + SC.name AS [text()] FROM sys.index_columns IC JOIN sys.columns SC ON IC.object_id = SC.object_id AND IC.column_id = SC.column_id WHERE IC.object_id = o.object_id AND IC.index_id = i.index_id AND IC.is_included_column = 1 ORDER BY IC.key_ordinal FOR XML PATH('')) , 2 , 1000) IncludedCols -- autres infos , i.index_id , OBJECTPROPERTY(o.object_id, 'IsView') AS IsView , ISNULL(OBJECTPROPERTY(o.object_id, 'TableIsMemoryOptimized'), 0) AS InMemoryOpt , i.has_filter , i.is_primary_key , i.is_unique_constraint , i.is_unique , ps.partition_number , ps.used_page_count AS PageCount , ps.used_page_count * 8 AS SizeKB , i.fill_factor , sp.name AS FileGroupName -- utilisation des index , s.user_seeks , s.user_scans , s.user_lookups , s.user_updates FROM sys.objects o LEFT JOIN sys.indexes i ON o.object_id = i.object_id LEFT JOIN sys.dm_db_index_usage_stats s ON s.index_id = i.index_id AND s.object_id = i.object_id AND s.database_id = DB_ID() JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id JOIN sys.allocation_units a ON p.partition_id = a.container_id JOIN sys.data_spaces sp ON a.data_space_id = sp.data_space_id LEFT JOIN sys.dm_db_partition_stats AS ps ON i.object_id = ps.object_id AND i.index_id = ps.index_id AND p.partition_id = ps.partition_id LEFT JOIN sys.key_constraints kc ON o.object_id = kc.parent_object_id AND kc.type = 'PK' WHERE o.type IN ( 'U', 'V' ) AND OBJECTPROPERTY(o.object_id, 'IsMSShipped') = 0 AND OBJECT_NAME(o.object_id) <> 'sysdiagrams' -- seulement tables utilisateur -- AND OBJECT_NAME(o.object_id) = '???' -- tri par nom ORDER BY SCHEMA_NAME(o.schema_id) , OBJECT_NAME(o.object_id) , i.index_id ASC; |
Table of Contents