SQL Server : Extraire les méta-données des tables (description, champs et leurs types de données)

Question

J'essaie de trouver un moyen d'extraire des informations sur mes tables dans SQL Server (2008).
Les données dont j'ai besoin doivent comprendre les éléments suivants description du tableau (renseigné à partir de la propriété Description dans la fenêtre Propriétés), une liste des champs de cette table et leurs types de données .

Existe-t-il un moyen d'extraire ces métadonnées ? Je suppose que je dois utiliser des sys sp mais je ne sais pas lequel.

Answer 1

Pour obtenir les données de description, vous devez malheureusement utiliser sysobjects/syscolumns pour obtenir les identifiants :

SELECT      u.name + '.' + t.name AS [table],
            td.value AS [table_desc],
            c.name AS [column],
            cd.value AS [column_desc]
FROM        sysobjects t
INNER JOIN  sysusers u
    ON      u.uid = t.uid
LEFT OUTER JOIN sys.extended_properties td
    ON      td.major_id = t.id
    AND     td.minor_id = 0
    AND     td.name = 'MS_Description'
INNER JOIN  syscolumns c
    ON      c.id = t.id
LEFT OUTER JOIN sys.extended_properties cd
    ON      cd.major_id = c.id
    AND     cd.minor_id = c.colid
    AND     cd.name = 'MS_Description'
WHERE t.type = 'u'
ORDER BY    t.name, c.colorder

Vous pouvez le faire avec info-schema, mais vous devrez concaténer etc. pour appeler OBJECT_ID() - alors quel serait l'intérêt ?

Answer 2

Les informations génériques sur les tableaux et les colonnes se trouvent dans ces tableaux :

select * from INFORMATION_SCHEMA.TABLES
select * from INFORMATION_SCHEMA.COLUMNS

La description de la table est une propriété étendue, vous pouvez les interroger à partir de sys.extended_properties :

select 
    TableName = tbl.table_schema + '.' + tbl.table_name, 
    TableDescription = prop.value,
    ColumnName = col.column_name, 
    ColumnDataType = col.data_type
FROM information_schema.tables tbl
INNER JOIN information_schema.columns col 
    ON col.table_name = tbl.table_name
    AND col.table_schema = tbl.table_schema
LEFT JOIN sys.extended_properties prop 
    ON prop.major_id = object_id(tbl.table_schema + '.' + tbl.table_name) 
    AND prop.minor_id = 0
    AND prop.name = 'MS_Description' 
WHERE tbl.table_type = 'base table'

Answer 3

Vous pouvez essayer sp_help <Name of object>

Answer 4

Utilisation des vues du catalogue d'objets :

SELECT  T.NAME AS [TABLE NAME], C.NAME AS [COLUMN NAME], P.NAME AS [DATA TYPE], P.MAX_LENGTH AS[SIZE],   CAST(P.PRECISION AS VARCHAR) +‘/’+ CAST(P.SCALE AS VARCHAR) AS [PRECISION/SCALE]
FROM ADVENTUREWORKS.SYS.OBJECTS AS T
JOIN ADVENTUREWORKS.SYS.COLUMNS AS C
ON T.OBJECT_ID=C.OBJECT_ID
JOIN ADVENTUREWORKS.SYS.TYPES AS P
ON C.SYSTEM_TYPE_ID=P.SYSTEM_TYPE_ID
WHERE T.TYPE_DESC=‘USER_TABLE’;

Utilisation des vues du schéma d'information

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION,
       COLUMN_DEFAULT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
       NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,
       DATETIME_PRECISION
FROM ADVENTUREWORKS.INFORMATION_SCHEMA.COLUMNS

Answer 5

Si vous effectuez vos requêtes à l'aide de code Java, vous pouvez utiliser une classe très utile, ResultSetMetaData, qui permet de récupérer les noms des colonnes et leurs propriétés (type et longueur).

Exemple

ResultSet rs = null;

        rs = sql.executeQuery();

        if (rs != null) {
            if (rs.next()) {
                ResultSetMetaData rsmd = rs.getMetaData();
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    System.out.println("column name: "
                            + rsmd.getColumnName(i));
                    System.out.println("column size: "
                            + rsmd.getColumnDisplaySize(i));
                }
            }