[Snippets] Générer le script de création d’une table existante

Publié le par

Voici une petite procédure stockée qui permet d’imprimer le code permettant de créer une table existante.
Il ne permet pas encore de récupérer les commandes de création des indexes attachées à la table …

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
---------------------------------
-- Nicolas SOUQUET - 28/01/2009 -
---------------------------------
CREATE PROCEDURE uspScripteTable
  @nomTable SYSNAME,
  @copie BIT = 0,
  @execute BIT = 0
AS
BEGIN
  SET NOCOUNT ON
 
  DECLARE @script VARCHAR(MAX)
 
  SELECT    @script =
      ISNULL(@script, '') + CHAR(9) +
      COLONNES.name +
      CASE
        WHEN COMPCOL.object_id IS NOT NULL THEN ''
        ELSE ' ' + TYPES.name
        END + '' +
      CASE TYPES.name
        WHEN 'float' THEN '(' + CAST(COLONNES.precision AS VARCHAR) + ')'
        WHEN 'decimal' THEN '(' + CAST(COLONNES.precision AS VARCHAR) + ')'
        WHEN 'numeric' THEN '(' + CAST(COLONNES.precision AS VARCHAR) + ', ' + CAST(COLONNES.scale AS VARCHAR) + ')'
        WHEN 'binary' THEN '(' + CAST(COLONNES.max_length AS VARCHAR) + ')'
        WHEN 'varbinary' THEN '(' + CASE COLONNES.max_length WHEN -1 THEN 'max' ELSE CAST(COLONNES.max_length AS VARCHAR) END + ')'
        WHEN 'char' THEN '(' + CAST(COLONNES.max_length AS VARCHAR) + ')'
        WHEN 'varchar' THEN '(' + CASE COLONNES.max_length WHEN -1 THEN 'max' ELSE CAST(COLONNES.max_length AS VARCHAR) END + ')'
        WHEN 'nchar' THEN '(' + CAST(COLONNES.max_length AS VARCHAR) + ')'
        WHEN 'nvarchar' THEN '(' +  CASE COLONNES.max_length WHEN -1 THEN 'max' ELSE CAST(COLONNES.max_length AS VARCHAR) END + ')'
        ELSE ''
      END +
      CASE COLONNES.is_identity
        WHEN 0 THEN ''
        WHEN 1 THEN ' IDENTITY(' + CAST(IDENT_SEED(TABLES.name) AS VARCHAR) + ', ' + CAST(IDENT_INCR(TABLES.name) AS VARCHAR) + ')'
      END +
      CASE COLONNES.is_nullable
        WHEN 0 THEN ' NOT NULL'
        WHEN 1 THEN ' NULL'
      END +
      CASE
        WHEN FK_COLONNES.constraint_object_id IS NULL THEN ''
        ELSE ' CONSTRAINT FK_' + TABLES.name +
        CASE @copie
          WHEN 1 THEN '_COPIE'
          ELSE ''
          END
        + '_' + COLONNES.name + ' FOREIGN KEY (' + COLONNES.name + ') REFERENCES ' + SCHEMA_FK.name + '.' + TAB_FK.name + '(' + COL_FK.name + ')'
      END +
      CASE
        WHEN DF.object_id IS NULL THEN ''
        ELSE ' CONSTRAINT DF_' + TABLES.name +
        CASE @copie
          WHEN 1 THEN '_COPIE'
          ELSE ''
          END + '_' + COLONNES.name + ' DEFAULT ' + REPLACE(REPLACE(DF.definition, '(', ''), ')', '')
      END +
      CASE
        WHEN CHK.object_id IS NULL THEN ''
        ELSE ' CONSTRAINT CHK_' + TABLES.name +
        CASE @copie
          WHEN 1 THEN '_COPIE'
          ELSE ''
        END + '_' + COLONNES.name + ' CHECK (' + REPLACE(REPLACE(REPLACE(REPLACE(CHK.definition, '(', ''), ')', ''), '[', ''), ']', '') + ')'
      END +
      CASE
        WHEN COMPCOL.object_id IS NULL THEN ''
        ELSE ' AS (' + REPLACE(REPLACE(REPLACE(REPLACE(COMPCOL.definition, '(', ''), ')', ''), '[', ''), ']', '') + ')'
        END +
      CASE
        WHEN ISNULL(INDEXES.is_primary_key, 0) = 0 THEN ''
        ELSE ' CONSTRAINT PK_' + TABLES.name +
        CASE @copie
          WHEN 1 THEN '_COPIE'
          ELSE ''
        END + ' PRIMARY KEY'
      END +
      CASE
        WHEN ISNULL(INDEXES.is_unique_constraint, 0) = 0 THEN ''
        ELSE ' CONSTRAINT UQ_' + TABLES.name + '_' + COLONNES.name + ' UNIQUE'
      END + ',' + CHAR(10)
  FROM    sys.tables AS TABLES -- Tables
  INNER JOIN  sys.columns AS COLONNES
        ON TABLES.object_id = COLONNES.object_id -- Colonnes
  INNER JOIN  sys.types AS TYPES
        ON COLONNES.user_type_id = TYPES.user_type_id -- types
  LEFT JOIN  sys.index_columns COL_INDEX -- indexes
        ON COL_INDEX.object_id = COLONNES.object_id
        AND COL_INDEX.column_id = COLONNES.column_id
  LEFT JOIN  sys.indexes AS INDEXES
        ON INDEXES.object_id = COL_INDEX.object_id
        AND INDEXES.index_id = COL_INDEX.index_id
  LEFT JOIN  sys.default_constraints AS DF -- contraintes de valeur par défaut
        ON DF.parent_object_id = TABLES.object_id
        AND DF.parent_column_id = COLONNES.column_id
  LEFT JOIN  sys.check_constraints AS CHK -- contraintes CHECK
        ON CHK.parent_object_id = TABLES.object_id
        AND CHK.parent_column_id = COLONNES.column_id
  LEFT JOIN  sys.computed_columns AS COMPCOL -- colonnes calculées
        ON COMPCOL.column_id = COLONNES.column_id
        AND COMPCOL.object_id = TABLES.object_id
  LEFT JOIN  sys.foreign_key_columns AS FK_COLONNES
        ON FK_COLONNES.parent_object_id = TABLES.object_id
        AND FK_COLONNES.parent_column_id = COLONNES.column_id
  LEFT JOIN  sys.columns AS COL_FK
        ON FK_COLONNES.referenced_object_id = COL_FK.object_id
        AND FK_COLONNES.referenced_column_id = COL_FK.column_id
  LEFT JOIN  sys.tables AS TAB_FK
        ON COL_FK.object_id = TAB_FK.object_id
  LEFT JOIN  sys.schemas AS SCHEMA_FK
        ON SCHEMA_FK.schema_id = TAB_FK.schema_id
  WHERE    TABLES.name = @nomTable
 
  SELECT  @script = 'CREATE TABLE ' + S.name + '.' +
    CASE @copie
      WHEN 1 THEN T.name + '_COPIE'
      ELSE T.name
    END + CHAR(10) +
    '(' + CHAR(10) + SUBSTRING(@script, 1, LEN(@script) - 2) + CHAR(10) + ')'
  FROM  sys.schemas AS S
  JOIN  sys.tables AS T ON S.schema_id = T.schema_id
  WHERE  T.name = @nomTable
 
  PRINT @script
 
  IF @execute = 1
  BEGIN
    EXEC (@script)
  END
END

ElSuket

6 réflexions au sujet de « [Snippets] Générer le script de création d’une table existante »

  2. Autre petite remarque par rapport aux valeurs par défaut.
    La commande utilisée pour enlever les parenthèses au début et à la fin des valeurs par défaut définies dans sys.default_constraints :
    REPLACE(REPLACE(DF.definition, ‘(‘,  »), ‘)’,  »)
    enlève aussi les parenthèses des fonctions utilisées en tant que valeur par défaut.

    Par exemple, sur un champ de type datetime on peut avoir défini la fonction système getdate() comme valeur par défaut pour un champ, enregistrée en tant que (getdate()) dans sys.default_constraints. Sauf que la commande traduit ça en getdate, sans ses parenthèses.

    Je pense donc qu’il faudrait plutôt faire comme ceci pour enlever les parenthèses de début et de fin :
    SUBSTRING(DF.definition,2,LEN(DF.definition)-2)

  3. Merci beaucoup ElSuket pour ce script qui va me rendre un grand service !
    Je me permets cependant d’envoyer une remarque par rapport à un problème que j’ai rencontré sur ma base de données. En effet, dans le cas où une table a plusieurs index sur certains de ses champs, alors les jointures sur sys.index_columns et sys.indexes telles qu’elles sont faites entraînent une répétition des champs concernés dans le script.
    Ex:
    CREATE TABLE SUIVICHANTIER
    (
    CLE numeric(38, 0) NOT NULL (…),
    CODESAL varchar(8) (…),
    CODESAL varchar(8) (…),
    CLEEQUIPE numeric(38, 0) NULL (…),
    CLEEQUIPE numeric(38, 0) NULL (…),
    NOMBRE numeric(38, 6) NOT NULL (…),
    DATETX datetime NOT NULL (…),
    DATETX datetime NOT NULL (…),
    DATETX datetime NOT NULL (…),
    DATETX datetime NOT NULL (…),
    DATETX datetime NOT NULL (…),
    MONTANT numeric(38, 6) NOT NULL (…),
    (…)
    )

    Je pense qu’au lieu d’utiliser la commande :
    LEFT JOIN sys.index_columns COL_INDEX — indexes
    ON COL_INDEX.object_id = COLONNES.object_id
    AND COL_INDEX.column_id = COLONNES.column_id
    LEFT JOIN sys.indexes AS INDEXES
    ON INDEXES.object_id = COL_INDEX.object_id
    AND INDEXES.index_id = COL_INDEX.index_id

    il serait préférable de faire comme ceci :
    LEFT JOIN (
    SELECT COL_INDEX.object_id, COL_INDEX.column_id, INDEXES.is_primary_key, INDEXES.is_unique_constraint
    FROM sys.index_columns COL_INDEX — indexes
    LEFT JOIN sys.indexes AS INDEXES
    ON INDEXES.object_id = COL_INDEX.object_id AND INDEXES.index_id = COL_INDEX.index_id
    WHERE (INDEXES.is_primary_key0 OR INDEXES.is_unique0)
    ) INDEXES
    ON INDEXES.object_id = COLONNES.object_id
    AND INDEXES.column_id = COLONNES.column_id

    avec notamment l’ajout d’un WHERE sur les clauses is_primary_key et is_unique qui sont finalement les seules informations qui nous intéressent dans ces tables.

    Jean

  5. Sympa; merci. Juste une précision : ce script ne retourne pas; p.e.; la longueur des champs.

    Le résultat diffère effectivement de ce qu’on obtient en faisant un clic droit sur le nom de la table puis qu’on choisis « Script table as –> Create to –> New Query Editor Window »

Laisser un commentaire