Vous êtes ici :
Ce script produit un rapport synthétique du nombre de tables d’une base de données, en précisant combien ont des clés primaires, de quel type (clustered, nonclusterd), combien ont des contrainte unique, combien n’ont pas d’autre index ou pas d’index du tout, etc…
Ce script est utilisé dans les rapports personnalisés de management studio SSMSInfoReports : voir https://github.com/datafly/SSMSInfoReports
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 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 |
/*------------------------------------------------------------------- [SCRIPT] Database PK and index information [DATABASE] current [DESCRIPTION] Rapport de synthèse sur la présence de PK, index, etc. [MAJ PAR] DATAFLY - Arian Papillon -------------------------------------------------------------------*/ SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT DISTINCT PK , COUNT(*) OVER ( PARTITION BY PK ) AS [Nb Tables] , SUM(CASE WHEN NbNonClustIndexes = 0 AND HasClustIndex = 0 THEN 1 ELSE 0 END) OVER ( PARTITION BY PK ) AS [Without Any Index] , SUM(HasClustIndex) OVER ( PARTITION BY PK ) AS [Has Clustered Index] , COUNT(*) OVER ( PARTITION BY PK ) - SUM(HasClustIndex) OVER ( PARTITION BY PK ) AS [Has No Clustered Index] , SUM(CASE WHEN NbNonClustIndexes = 0 THEN 1 ELSE 0 END) OVER ( PARTITION BY PK ) AS [Without Any NonClust Index] , SUM(HasUniqueConstraint) OVER ( PARTITION BY PK ) AS [Has Unique Constraint] , SUM(HasIdentity) OVER ( PARTITION BY PK ) AS [Has Identity Column] , SUM(HasFullText) OVER ( PARTITION BY PK ) AS [Has FullText Index] , SUM(HasRowGuidCol) OVER ( PARTITION BY PK ) AS [Has RowGuid Column] , SUM(HasTextImage) OVER ( PARTITION BY PK ) AS [Has TextImage Column] , SUM(HasTimeStamp) OVER ( PARTITION BY PK ) AS [Has TimeStamp Column] , SUM(HasTrigger) OVER ( PARTITION BY PK ) AS [Has After Trigger] , SUM(IsMemoryOptimized) OVER ( PARTITION BY PK ) AS [IsMemoryOptimized] FROM ( SELECT [Schema] , TableName , Id , IsIdxView , CASE WHEN IsIdxView = 1 THEN 'Idx View' WHEN HasPK = 0 THEN 'No PK' WHEN HasPK = 1 AND HasClustPK = 1 THEN 'Clustered' ELSE 'NonClustered' END AS PK , HasClustIndex , NbNonClustIndexes , HasFullText , CASE WHEN IsIdxView = 1 THEN 0 ELSE HasRowGuidCol END AS HasRowGuidCol , CASE WHEN IsIdxView = 1 THEN 0 ELSE HasTextImage END AS HasTextImage , CASE WHEN IsIdxView = 1 THEN 0 ELSE HasTimeStamp END AS HasTimeStamp , CASE WHEN IsIdxView = 1 THEN 0 ELSE HasIdentity END AS HasIdentity , HasTrigger , CASE WHEN IsIdxView = 1 THEN 0 ELSE HasUniqueConstraint END AS HasUniqueConstraint , IsMemoryOptimized FROM ( SELECT --t.TABLE_CATALOG as 'DATABASE' -- Tables t.TABLE_SCHEMA AS 'Schema' , t.TABLE_NAME AS TableName , OBJECT_ID( '[' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME + ']') AS Id , OBJECTPROPERTY( OBJECT_ID( '[' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME + ']') , 'IsView') AS IsIdxView , OBJECTPROPERTY( OBJECT_ID( '[' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME + ']') , 'TableHasPrimaryKey') AS HasPK , ISNULL( OBJECTPROPERTY( OBJECT_ID( '[' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME + ']') , 'TableIsMemoryOptimized') , 0) AS IsMemoryOptimized , ( SELECT OBJECTPROPERTY(object_id, 'CnstIsClustKey') FROM sys.objects WHERE parent_object_id = OBJECT_ID( '[' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME + ']') AND type = 'PK' ) AS HasClustPK , CASE WHEN OBJECTPROPERTY( OBJECT_ID( '[' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME + ']') , 'IsView') = 1 THEN 1 ELSE OBJECTPROPERTY( OBJECT_ID( '[' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME + ']') , 'TableHasClustIndex') END AS HasClustIndex , ( SELECT COUNT(*) FROM sys.indexes WHERE object_id = OBJECT_ID( '[' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME + ']') AND index_id > 1 AND type = 2 ) AS NbNonClustIndexes , OBJECTPROPERTY( OBJECT_ID( '[' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME + ']') , 'TableHasActiveFulltextIndex') AS HasFullText , OBJECTPROPERTY( OBJECT_ID( '[' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME + ']') , 'TableHasRowGuidCol') AS HasRowGuidCol , OBJECTPROPERTY( OBJECT_ID( '[' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME + ']') , 'TableHasTextImage') AS HasTextImage , OBJECTPROPERTY( OBJECT_ID( '[' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME + ']') , 'TableHasTimeStamp') AS HasTimeStamp , OBJECTPROPERTY( OBJECT_ID( '[' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME + ']') , 'TableHasIdentity') AS HasIdentity , OBJECTPROPERTY( OBJECT_ID( '[' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME + ']') , 'HasAfterTrigger') AS HasTrigger , OBJECTPROPERTY( OBJECT_ID( '[' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME + ']') , 'TableHasUniqueCnst') AS HasUniqueConstraint FROM INFORMATION_SCHEMA.TABLES t JOIN sys.indexes i ON OBJECT_ID( '[' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME + ']') = i.object_id AND i.index_id < 2 WHERE ( t.TABLE_TYPE = 'BASE TABLE' OR t.TABLE_TYPE = 'VIEW' ) AND OBJECTPROPERTY( OBJECT_ID( '[' + t.TABLE_SCHEMA + '].[' + t.TABLE_NAME + ']') , 'IsMSShipped') = 0 AND t.TABLE_NAME <> 'sysdiagrams' -- seulement tables utilisateur AND t.TABLE_NAME <> 'dtproperties' GROUP BY OBJECT_ID( '[' + t.TABLE_SCHEMA + '].' + t.TABLE_NAME) , t.TABLE_CATALOG , t.TABLE_SCHEMA , t.TABLE_NAME ) AS TablesProperties ) AS ListTables; |
Table of Contents