Requête SQL pour récupérer la structure d'une table

Version imprimable

Bonjour,

Je cherche à exporter le résultat d'une requête pouvant m'afficher la structure d'une table.
J'ai trouvé sur internet une requête m'affichant presque tout ce que je souhaite :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT    S.name AS schema_name
    , T.name AS table_name
    , C.name AS column_name
--    , C.column_id
    , TY.name AS data_type_name
    , CASE WHEN TY.name IN ('nchar', 'nvarchar') THEN C.max_length / 2 ELSE C.max_length END AS max_length
FROM    sys.schemas AS S
INNER JOIN  sys.tables AS T
      ON S.schema_id = T.schema_id
INNER JOIN  sys.columns AS C  
      ON C.object_id = T.object_id
INNER JOIN  sys.types AS TY
      ON TY.system_type_id = C.system_type_id
      AND TY.system_type_id = TY.user_type_id
WHERE    1 = 1
--AND    S.name = 'dbo'
--AND    T.name = 'maTable'
--AND    C.name = 'maColonne'
ORDER BY  T.name, C.column_id

Je cherche à afficher des informations supplémentaires (qui apparaissent lorsqu'on lance le script "create to") telles que null ou not null, ex :

Code:

1
2
3
4
CREATE TABLE [schema].[matable](
[iTransactionsId] [bigint] NOT NULL,
[dMontant] [decimal](18, 2) NULL,
...

ainsi que le libellé (@value) qui apparait dans une suite de commande que je ne comprends absolument pas, ex :

Code:

1
2
3
4
5
6
7
8
9
10
 
...CONSTRAINT [PK_DTM_TRANSACTIONS] PRIMARY KEY CLUSTERED 
(
	[iTransactionsId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Clé primaire' , @level0type=N'SCHEMA',@level0name=N'schema', @level1type=N'TABLE',@level1name=N'matable', @level2type=N'COLUMN',@level2name=N'iTransactionsId'
GO

Quelqu'un saurait-il comment ajouter ces 2 dernières informations à ma 1ère requête afin que je puisse la compléter ?

Merci d'avance.

Tout d'abord votre requête est relativement incomplète. En voici une plus complète sans les contraintes :

Code:

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
DECLARE @TABLE_SCHEMA sysname = N'S_PRS',
        @TABLE_NAME   sysname = N'T_PERSONNE_PHYSIQUE_PSP';
 
SELECT N'USE [' + DB_NAME() + N'];' AS SQL_CMD
UNION  ALL
SELECT N'GO'
UNION  ALL
SELECT CASE uses_ansi_nulls WHEN 1 THEN N'SET ANSI_NULLS ON;' ELSE N'SET ANSI_NULLS OFF;' END 
FROM   sys.tables 
WHERE  name = @TABLE_NAME 
  AND  schema_id = SCHEMA_ID(@TABLE_SCHEMA)
UNION  ALL
SELECT N'GO'
UNION  ALL
SELECT CASE is_quoted_identifier_on WHEN 1 THEN N'SET QUOTED_IDENTIFIER ON;' ELSE N'SET QUOTED_IDENTIFIER OFF;' END
FROM   sys.databases 
WHERE  name = DB_NAME()
UNION  ALL
SELECT N'GO'
UNION  ALL
SELECT N'CREATE TABLE [' + COALESCE(@TABLE_SCHEMA, SCHEMA_NAME()) + N'].[' + @TABLE_NAME + N'] (' AS SQL_CMD_LINE 
UNION  ALL
SELECT N'[' + clmns.name + '] '
       + COALESCE(N'[' + s1clmns.name + '].[' + usrt.name + N'] ', N'[' + baset.name + N'] ', N'') +
       CASE WHEN s1clmns.name IS NULL 
               THEN 
                  CASE WHEN ISC.DATA_TYPE LIKE '%char' AND clmns.max_length > 0
                          THEN N'(' + CAST(CHARACTER_MAXIMUM_LENGTH AS NVARCHAR(32)) + N') '
                       WHEN ISC.DATA_TYPE LIKE '%varchar' OR ISC.DATA_TYPE = 'varbinary' AND CHARACTER_MAXIMUM_LENGTH = -1 
                          THEN N'(max) '
                       WHEN ISC.DATA_TYPE IN ('datetime2', 'time', 'datetimeoffset') 
                          THEN N'(' + CAST(DATETIME_PRECISION AS NVARCHAR(32)) + N') '
                       WHEN ISC.DATA_TYPE IN ('decimal', 'numeric') 
                          THEN N'(' + CAST(NUMERIC_SCALE AS VARCHAR(32)) + N',' + CAST(NUMERIC_PRECISION AS VARCHAR(32)) + N') '
                       WHEN ISC.DATA_TYPE = 'xml' AND xscclmns.name IS NOT NULL 
                          THEN N'([' + s2clmns.name + N'].[' + xscclmns.name + ']) '
                       ELSE N''
                  END
 
            ELSE N'' 
       END
       + CASE clmns.is_computed WHEN 1 THEN cc.definition + N' ' ELSE N'' END 
       + CASE cc.is_persisted WHEN 1 THEN N'PERSISTED ' ELSE N'' END
       + CASE WHEN clmns.collation_name <> CAST((DATABASEPROPERTYEX(DB_NAME(), 'Collation')) AS NVARCHAR(128)) THEN N'COLLATE ' + clmns.collation_name ELSE N'' END
       + CASE clmns.is_nullable WHEN 1 THEN N'NULL ' ELSE N' NOT NULL ' END
       + CASE clmns.is_identity WHEN 1 THEN N'IDENTITY(' + CAST(CAST(ic.seed_value AS bigint) AS NVARCHAR(32)) + N', ' 
                                                         + CAST(CAST(ic.increment_value AS bigint) AS NVARCHAR(32)) + N') ' ELSE N'' END
       + CASE WHEN clmns.is_rowguidcol = 1 THEN N'ROWGUIDCOL ' ELSE N'' END
       + CASE WHEN ic.is_not_for_replication = 1 THEN N'NOT FOR REPLICATION ' ELSE N'' END
FROM   sys.tables AS tbl
       INNER JOIN sys.schemas AS s 
          ON tbl.schema_id = s.schema_id
       INNER JOIN sys.all_columns AS clmns 
          ON clmns.object_id=tbl.object_id
       LEFT OUTER JOIN INFORMATION_SCHEMA.COLUMNS AS ISC 
          ON clmns.name = ISC.COLUMN_NAME AND TABLE_SCHEMA = @TABLE_SCHEMA AND TABLE_NAME = @TABLE_NAME
       LEFT OUTER JOIN sys.computed_columns AS cc 
          ON cc.object_id = clmns.object_id and cc.column_id = clmns.column_id
       LEFT OUTER JOIN sys.types AS usrt 
          ON usrt.user_type_id = clmns.user_type_id
       LEFT OUTER JOIN sys.schemas AS s1clmns 
          ON s1clmns.schema_id = usrt.schema_id
       LEFT OUTER JOIN sys.objects AS d 
          ON d.object_id = clmns.default_object_id
       LEFT OUTER JOIN sys.default_constraints as dc 
          ON clmns.default_object_id = dc.object_id
       LEFT OUTER JOIN sys.identity_columns AS ic 
          ON ic.object_id = clmns.object_id and ic.column_id = clmns.column_id
       LEFT OUTER JOIN sys.types AS baset 
          ON (baset.user_type_id = clmns.system_type_id and baset.user_type_id = baset.system_type_id) or 
             ((baset.system_type_id = clmns.system_type_id) and (baset.user_type_id = clmns.user_type_id) and (baset.is_user_defined = 0) and (baset.is_assembly_type = 1)) 
       LEFT OUTER JOIN sys.objects AS r 
          ON r.object_id = clmns.rule_object_id
       LEFT OUTER JOIN sys.xml_schema_collections AS xscclmns 
          ON xscclmns.xml_collection_id = clmns.xml_collection_id
       LEFT OUTER JOIN sys.schemas 
          AS s2clmns ON s2clmns.schema_id = xscclmns.schema_id
WHERE  tbl.name=@TABLE_NAME and s.name = @TABLE_SCHEMA
UNION ALL
SELECT N') ON ' + N'[' + ds.name + '] '
      + CASE WHEN  ds.type = 'PS' THEN N'(' + cp.name + N') ' ELSE '' END
     + CASE WHEN  lob_data_space_id > 0 THEN N'[' + dsl.name + N']' ELSE N'' END
FROM   sys.tables AS t 
       JOIN sys.indexes AS i ON i.object_id = t.object_id
       JOIN sys.schemas AS s ON t.schema_id = s.schema_id
       JOIN sys.data_spaces AS ds 
          ON i.data_space_id = ds.data_space_id 
       LEFT OUTER JOIN sys.data_spaces AS dsl 
          ON t.lob_data_space_id = dsl.data_space_id 
       LEFT OUTER JOIN sys.index_columns AS ic
          ON i.object_id = ic.object_id AND i.index_id = ic.index_id AND ic.partition_ordinal > 0
       LEFT OUTER JOIN sys.columns AS cp
          ON ic.object_id = cp.object_id AND ic.column_id = cp.column_id
WHERE  s.name = @TABLE_SCHEMA AND t.name = @TABLE_NAME
 AND i.index_id < 2;

Remplacez dans les 2 première variables, les noms de schéma et de table par vos valeurs.

Pour le reste :
1) contraintes avec les particularité d'index pour PK et UNIQUE
2) index relatif à la table
3) propriétés étendues

Il faut quelques requêtes de plus !

A +

Bonjour,

Merci pour votre prompte réponse.
Elle est cependant très compliquée vu mes capacités limitées en SQL.
En décortiquant votre code, j'ai finalement identifié la table INFORMATION_SCHEMA.COLUMNS qui répond à tous mes besoins :
Code:

1 2 3 4 5 6 7 8 9 10 select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME as nom_sql, DATA_TYPE as type, CHARACTER_MAXIMUM_LENGTH as longueur, case IS_NULLABLE when 'NO' then 'NOT NULL' else 'NULL' end as obligatoire from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'schema' order by 1, 2, 3 ;
à l'exception de la description des colonnes (auquel je peux accéder en cliquant sur les propriétés).

N'existerait-il donc pas une autre table contenant cette information que je pourrais simplement ajouter à ma requête ?

Merci.