Requêtes de métadonnées des bases MS SQL Server – Partie 1

Publié le 2018-05-27 par SQLpro


Cette première liste de requête utilise essentiellement les vues normalisées INFORMATION_SCHEMA pour trouver des informations utiles à mesurer la qualité de vos bases de données et les documenter.

1 – liste des objets basique d’une base

SELECT type, type_desc AS OBJECT_TYPE, COUNT(*) AS NUMBER 
FROM   sys.objects
WHERE  is_ms_shipped = 0
  AND  type NOT IN ('C', 'D', 'IT', 'PG', 'PK', 'F', 
                 'R', 'RF', 'S', 'SN', 'TR', 'UQ')
GROUP  BY type, type_desc;

Cette requête Transact SQL donne la liste de tous les objets utilisateurs de premier niveau (tables, vues, fonctions utilisateur, séquence, procédures stockées…) d’une base.

VERSION SQL Server acceptées : à partir de SQL Server 2005

2 – liste des schémas SQL utilisateur d’une base

SELECT * 
FROM   INFORMATION_SCHEMA.SCHEMATA 
WHERE  SCHEMA_NAME NOT IN 
       ('INFORMATION_SCHEMA', 'sys', 'guest', 
        'db_owner', 'db_accessadmin', 'db_securityadmin', 
        'db_ddladmin', 'db_backupoperator', 'db_datareader', 
        'db_datawriter', 'db_denydatareader', 'db_denydatawriter');

Cette requête Transact SQL donne la liste de tous les schémas SQL utilisateurs d’une base.

VERSION SQL Server acceptées : à partir de SQL Server 2005

3 – tables dénuées de clef primaire

SELECT T.TABLE_SCHEMA, T.TABLE_NAME
FROM   INFORMATION_SCHEMA.TABLES AS T
WHERE  TABLE_TYPE = 'BASE TABLE'
EXCEPT
SELECT TC.TABLE_SCHEMA, TC.TABLE_NAME
FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
WHERE  TC.CONSTRAINT_TYPE = 'PRIMARY KEY';

Cette requête Transact SQL donne la liste de tous les tables qui n’ont pas de clef primaire.

ATTENTION : une table dénuée de clef primaire sera toujours contre performante quelque soit les circonstances, même si elle n’est utilisé qu’en ajout de ligne…

VERSION SQL Server acceptées : à partir de SQL Server 2005

4 – nombre de colonnes dans une clef primaire

SELECT KCU.TABLE_SCHEMA, KCU.TABLE_NAME, COUNT(*)
FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
       JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
          ON TC.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA 
             AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
WHERE  TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
GROUP  BY KCU.TABLE_SCHEMA, KCU.TABLE_NAME
ORDER  BY 3 DESC;

Cette requête Transact SQL donne le nombre de colonnes composant la clef primaire de chaque table.

ATTENTION : une clef primaire composée de multiple colonne sera toujours contre performante quelque soit les circonstances. Il est vivement conseillé de réduire le nombre de colonnes d’une clef à 2 au maximum pour les tables associatives et une pour toutes les autres tables (en utilisant l’auto incrément). Seul le cas de partitionnement peut conduire à de meilleures performances dans le cas d’une clef composée de deux colonnes.

VERSION SQL Server acceptées : à partir de SQL Server 2005

5 – clef primaire utilisant ou non un autoincrément IDENTITY

SELECT KCU.TABLE_SCHEMA, KCU.TABLE_NAME, 
       MAX(COLUMNPROPERTY(OBJECT_ID(KCU.TABLE_SCHEMA +  '.' 
          +  KCU.TABLE_NAME), COLUMN_NAME, 'IsIdentity')) 
          AS PK_HAS_IDENTITY_PROPERTY
FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
       JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
            ON TC.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA 
            AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
WHERE  TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
GROUP  BY KCU.TABLE_SCHEMA, KCU.TABLE_NAME
ORDER  BY PK_HAS_IDENTITY_PROPERTY DESC;

Cette requête Transact SQL montre, pour chaque clef primaire, si celle-ci comporte une autoincrémentation de type IDENTITY.

VERSION SQL Server acceptées : à partir de SQL Server 2005

6 – table « clustered » ou « heap » ?

SELECT TABLE_SCHEMA, TABLE_NAME, index_id AS TABLE_IS_CLUSTERED
FROM   INFORMATION_SCHEMA.TABLES AS T
       JOIN sys.indexes AS i
            ON OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME) 
               = i.object_id
WHERE  TABLE_TYPE = 'BASE TABLE'
  AND  index_id <= 1
ORDER  BY TABLE_IS_CLUSTERED;

Cette requête Transact SQL montre, pour chaque table si elle est organisée en cluster ou en tas (heap).

ATTENTION : mieux vaudrait que toutes vos tables soient organisées en cluster. C’est le cœur du fonctionnement de SQL Server et il est spécialement optimisé pour ce mode de structuration des tables.

VERSION SQL Server acceptées : à partir de SQL Server 2005

7 – nombre de colonne par table

SELECT T.TABLE_SCHEMA, T.TABLE_NAME, COUNT(*) NUMBER,
       AVG(COUNT(*)) OVER() AS AVERAGE_NUMBER
FROM   INFORMATION_SCHEMA.COLUMNS AS C
       JOIN INFORMATION_SCHEMA.TABLES AS T
            ON C.TABLE_SCHEMA = T.TABLE_SCHEMA
            AND C.TABLE_NAME = T.TABLE_NAME
WHERE   T.TABLE_TYPE = 'BASE TABLE'
GROUP  BY T.TABLE_SCHEMA, T.TABLE_NAME
ORDER  BY NUMBER DESC;

Cette requête Transact SQL montre, pour chaque table le nombre de colonnes.

ATTENTION : mieux vaudrait que toutes vos tables aient peu de colonne. Plus il y a de colonnes, moins les performances seront bonnes car moins d’index pourront être utilisés en recherche (seek) pour y accéder et par conséquent la majorité des lectures se feront par balayage (scan).

VERSION SQL Server acceptées : à partir de SQL Server 2005

8 – types de données « anormaux »

WITH T AS
(
SELECT C.TABLE_SCHEMA, C.TABLE_NAME, COLUMN_NAME, 
       UPPER(C.DATA_TYPE) AS DATA_TYPE,
       CASE
          WHEN (DATA_TYPE LIKE '%char' OR DATA_TYPE LIKE '%binary') 
               AND CHARACTER_MAXIMUM_LENGTH <> -1 
             THEN '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(32)) + ')'
          WHEN (DATA_TYPE LIKE '%varchar' OR DATA_TYPE = 'varbinary')
                AND CHARACTER_MAXIMUM_LENGTH = -1 
             THEN '(max)'
          WHEN DATA_TYPE IN ('numeric', 'decimal')
             THEN '(' + CAST(NUMERIC_PRECISION AS VARCHAR(32)) + ', '
                      + CAST(NUMERIC_SCALE AS VARCHAR(32)) + ')'
          WHEN DATA_TYPE IN ('datetime2', 'time', 'datetimeoffset')
             THEN '(' + CAST(DATETIME_PRECISION AS VARCHAR(32)) + ')'
       END AS DIMENSIONS,
       CASE DATA_TYPE 
          WHEN 'text'
             THEN 'OBSOLETE, must be replaced by VARCHAR(max)'
          WHEN 'ntext'
             THEN 'OBSOLETE, must be replaced by NVARCHAR(max)'
          WHEN 'image'
             THEN 'OBSOLETE, must be replaced by VARBINARY(max)'
          WHEN 'datetime'
             THEN 'IMPRECISE, must be replaced by DATETIME2(n)'
          WHEN 'smalldatetime'
             THEN 'IMPRECISE and LIMTED, must be replaced by DATETIME2(0)'
          WHEN 'sql_variant'
             THEN 'NOT RECOMMANDED IN TABLE, shall be replace by adequate type'
          WHEN 'smallmoney'
             THEN 'LIMTED, shall be replaced by DECIMAL(n, m)'
          WHEN 'money'
             THEN 'UNINTERESTING, shall be replaced by DECIMAL(n, m)'
          WHEN 'timestamp'
             THEN 'USUALLY WRONG (not a datetime), must be replaced by ROWVERSION'
       END AS REMARK
FROM   INFORMATION_SCHEMA.TABLES AS T
       JOIN INFORMATION_SCHEMA.COLUMNS AS C
          ON T.TABLE_SCHEMA = C.TABLE_SCHEMA AND
             T.TABLE_NAME   = C.TABLE_NAME   
WHERE  T.TABLE_TYPE = 'BASE TABLE'
)
SELECT * 
FROM   T
WHERE  REMARK IS NOT NULL
ORDER  BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME;

Cette requête Transact SQL montre, toutes les tables qui ont des colonnes ayant des types anormaux : obsolètes (text, ntext, image…) imprécis (datetime, smalldatetime) ou autres… avec un diagnostic.

ATTENTION : lorsque vos tables contiennent de telles données elle seront fonctionnellement limitées et poseront des problèmes de performance

VERSION SQL Server acceptées : à partir de SQL Server 2008

9 – présence de LOBs dans les tables

SELECT T.TABLE_SCHEMA, T.TABLE_NAME, COLUMN_NAME, 
       DATA_TYPE
FROM   INFORMATION_SCHEMA.COLUMNS AS C
       JOIN INFORMATION_SCHEMA.TABLES AS T
            ON C.TABLE_SCHEMA = T.TABLE_SCHEMA
            AND C.TABLE_NAME = T.TABLE_NAME
WHERE   T.TABLE_TYPE = 'BASE TABLE'
  AND   (DATA_TYPE IN ('text', 'ntext', 'image') 
        OR  CHARACTER_OCTET_LENGTH = -1) 
ORDER   BY T.TABLE_SCHEMA, T.TABLE_NAME, COLUMN_NAME;

Cette requête Transact SQL montre, pour les tables dans lesquelles figurent des « LOBs », c’est à dire des colonnes, non relationnelles permettant de stocker des informations de fort volume (maximum 2 Go) sous forme textuelles (CLOB, NCLOB) ou binaires (BLOB : binary, XML, geometry, geography…).

ATTENTION : lorsque vos tables contiennent des LOBs mieux vaudrait qu’ils soient stockées en dehors des autres données relationnelles, via un espace de stockage dédié (TEXTIMAGE_ON).

VERSION SQL Server acceptées : à partir de SQL Server 2005

10 – nombre d’informations propre

WITH T AS
(
SELECT C.TABLE_SCHEMA, C.TABLE_NAME, C.COLUMN_NAME,
       (SELECT COUNT(*) 
        FROM   INFORMATION_SCHEMA.COLUMNS CL 
        WHERE  CL.TABLE_SCHEMA = C.TABLE_SCHEMA 
          AND  CL.TABLE_NAME = C.TABLE_NAME) AS NB_COL
FROM   INFORMATION_SCHEMA.COLUMNS AS C
       JOIN INFORMATION_SCHEMA.TABLES AS T
            ON C.TABLE_SCHEMA = T.TABLE_SCHEMA
            AND C.TABLE_NAME = T.TABLE_NAME 
WHERE  T.TABLE_TYPE = 'BASE TABLE'
AND    C.COLUMN_NAME NOT IN 
          (SELECT KCU.COLUMN_NAME
           FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
                  JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
                     ON TC.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA 
                     AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
           WHERE  TC.CONSTRAINT_TYPE = 'FOREIGN KEY'
             AND  TC.TABLE_SCHEMA = C.TABLE_SCHEMA
             AND  TC.TABLE_NAME = C.TABLE_NAME)
)
SELECT TABLE_SCHEMA, TABLE_NAME, NB_COL, COUNT(*) AS NB_INFO,
       AVG(COUNT(*)) OVER() AS AVERAGE_NB_INFO
FROM   T
GROUP  BY TABLE_SCHEMA, TABLE_NAME, NB_COL
ORDER  BY NB_INFO DESC;

Cette requête Transact SQL donne, pour chaque tables le nombre d’information propre (excluant de fait les clefs étrangères) ainsi que le nombre de colonne et la moyenne du nombre d’information par table dans la base.

VERSION SQL Server acceptées : à partir de SQL Server 2005

Lien de téléchargement du code

Vos commentaires sont les bienvenus !


Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
* Le site sur les SGBD relationnels et le SQL : https://sqlpro.developpez.com *
* le blog SQL, SQL Server, SGBDR... sur : https://blog.developpez.com/sqlpro/ *
* Expert Microsoft SQL Server, MVP (Most valuable Professional) depuis 14 ans *
* Entreprise SQL SPOT : modélisation, conseil, audit, optimisation, formation *
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

Ce contenu a été publié dans Requêtes de métadonnées, avec comme mot(s)-clé(s) , , , . Vous pouvez le mettre en favoris avec ce permalien.