SQL SERVER - GENERATION AUTOMATIQUE D'UNE RETRO-DOCUMENTATION

-- Base de données traitée
USE [AdventureWorks];

-- Ne pas afficher le nbre de lignes affectées
SET NOCOUNT ON

-- Variable locale
DECLARE @nom_table AS NVARCHAR(128)

-- Nom de la base de données et date de création 
PRINT 'BASE ' + db_name()
PRINT 'Document créé le ' + Convert(char(10), getdate(), 103)
PRINT ''

-- Curseur de génération de la rétro-documentation
DECLARE tables_cursor CURSOR FOR SELECT [name] FROM sys.all_objects WHERE type_desc = 'USER_TABLE'
OPEN tables_cursor
-- Première table analysée
FETCH NEXT FROM tables_cursor INTO @nom_table

WHILE @@FETCH_STATUS = 0 BEGIN
   -- Nom de la table
   PRINT 'TABLE : ' + @nom_table
   -- Structure de la table (tableau de méta-données)
   SELECT DISTINCT c.name AS [Colonne],ISC.data_type AS [Type],c.max_length AS [Long.], CASE ISC.is_nullable WHEN 'NO' THEN 'OUI' ELSE 'NON' END AS [Oblig.],
   isnull(ISC.column_default,'') AS [Val. par défaut], CASE c.is_identity WHEN '0' THEN '' ELSE 'OUI' END AS [Incr. auto], ISNULL(CASE WHEN i.is_primary_key = 1 THEN 'Clé primaire '
   WHEN i.is_unique = 1 THEN 'Clé unique ' ELSE 'Clé avec doublon ' END + 'de type '+ i.type_desc,'') AS [Clé],
   CASE WHEN isnull(fc.name,'0') = '0' THEN '' ELSE 'Clé étrangère : ' + isnull(o2.name,'') + '.' + isnull(c2.name,'') END AS [Contrainte] 
   FROM sys.all_objects o 
   INNER JOIN  sys.all_columns c ON o.object_id = c.object_id 
   LEFT OUTER JOIN INFORMATION_SCHEMA.COLUMNS ISC ON ISC.table_name = o.name AND  c.name = ISC.column_name 
   LEFT OUTER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON ccu.table_name = o.name AND  c.name = ccu.column_name 
   LEFT OUTER JOIN sys.foreign_keys fc on fc.name = ccu.constraint_name 
   LEFT OUTER JOIN sys.all_columns c2 ON c2.object_id = fc.referenced_object_id AND fc.key_index_id = c2.column_id
   LEFT OUTER JOIN sys.all_objects o2 ON o2.object_id = c2.object_id 
   LEFT OUTER JOIN sys.index_columns ic ON o.object_id = ic.object_id AND ic.column_id = c.column_id
   LEFT OUTER JOIN sys.indexes i ON i.object_id = ic.object_id AND ic.index_id = i .index_id 
   WHERE o.name =  @nom_table
   -- Table suivante
   FETCH NEXT FROM tables_cursor INTO @nom_table
END

-- Libération de la mémoire
CLOSE tables_cursor
DEALLOCATE tables_cursor