Obtener documentación de base de datos SQL Server

Por cuestiones de tiempo, esta entrada es solo un "dump" de código fuente. Asumiendo que hiciste tu tarea de colocar los comentarios de los objetos de la base de datos utilizando la propiedad extendida MS_Description, a continuación incluyo unas consultas para obtener la documentación respectiva de los objetos más relevantes de la base de datos.

Esquemas

SELECT
  s.name AS esquema,
  ep.value AS descripcion
FROM sys.schemas s
  left join sys.extended_properties ep
    ON s.schema_id = ep.major_id
      AND ep.name = 'MS_Description'
WHERE s.name NOT IN (
  -- Lista de esquemas a ignorar
  'db_accessadmin',
  'db_backupoperator',
  'db_datareader',
  'db_datawriter',
  'db_ddladmin',
  'db_denydatareader',
  'db_denydatawriter',
  'db_owner',
  'db_securityadmin',
  'guest',
  'INFORMATION_SCHEMA',
  'sys'
)
ORDER BY 1

Tablas

SELECT
  SCHEMA_NAME(schema_id) esquema,
  t.name AS tabla,
  ep.value AS descripcion
FROM sys.tables AS t
  left join sys.extended_properties ep
    ON t.object_id = ep.major_id
      AND 0 = ep.minor_id
      AND ep.name = 'MS_Description'
ORDER BY 1, t.name

Columnas

Nota: No he descubierto aún por qué, pero hay ciertos casos en los que la columna que es llave primaria me aparece duplicada. Cualquier ayuda es bienvenida :)

SELECT
  SCHEMA_NAME(schema_id) + '.' + t.name AS tabla,
  c.name AS columna,
  TYPE_NAME(c.system_type_id) AS tipo_de_datos,
  CASE c.max_length
    WHEN -1 THEN 'MAX'
    ELSE CAST(c.max_length AS VARCHAR(100))
  END tamano,
  CASE c.is_nullable
    WHEN 1 THEN 'Sí'
    ELSE 'No'
  END AS permite_nulos,
  CASE i.is_primary_key
    WHEN 1 THEN 'Sí'
    ELSE 'No'
  END AS llave_primaria,
  CASE
    WHEN fk.referenced_column_id IS NOT NULL THEN 'Sí'
    ELSE 'No'
  END AS llave_foranea,
  ep.value AS descripcion
FROM sys.tables AS t
  INNER JOIN sys.columns c
    ON t.object_id = c.object_id
  LEFT JOIN sys.index_columns AS ic
    ON ic.OBJECT_ID = c.OBJECT_ID AND ic.column_id = c.column_id
  LEFT JOIN sys.indexes AS i
    ON i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id
  LEFT JOIN sys.foreign_key_columns AS fk
    ON fk.parent_object_id = c.object_id
      AND fk.parent_column_id = c.column_id
  LEFT JOIN sys.extended_properties ep
    ON t.object_id = ep.major_id
      AND c.column_id = ep.minor_id
      AND ep.name = 'MS_Description'
ORDER BY 1, c.column_id

Llaves foráneas

Nota: Esta consulta devuelve una fila por columna, por lo que aparecerán tantas filas como columnas incluya la llave.

SELECT
  SCHEMA_NAME(t.schema_id) + '.' + t.name as tabla_hija,
  c.name as tabla_hija_columna,
  SCHEMA_NAME(tr.schema_id) + '.' + tr.name tabla_padre,
  cr.name tabla_padre_columna,
  fk.name fk_nombre,
  CASE fk.update_referential_action
    WHEN 0 THEN 'NO ACTION'
    WHEN 1 THEN 'CASCADE'
    WHEN 2 THEN 'SET NULL'
    WHEN 3 THEN 'SET DEFAULT'
  END regla_actualizacion,
  CASE fk.delete_referential_action
    WHEN 0 THEN 'NO ACTION'
    WHEN 1 THEN 'CASCADE'
    WHEN 2 THEN 'SET NULL'
    WHEN 3 THEN 'SET DEFAULT'
  END regla_eliminacion
FROM sys.foreign_key_columns as fkc
  INNER JOIN sys.foreign_keys fk
    ON fkc.constraint_object_id = fk.object_id
  INNER JOIN sys.tables as t
    ON fkc.parent_object_id = t.object_id
  INNER JOIN sys.columns as c
    ON fkc.parent_object_id = c.object_id
      and fkc.parent_column_id = c.column_id
  INNER JOIN sys.tables as tr
    ON fkc.referenced_object_id = tr.object_id
  INNER JOIN sys.columns as cr
    ON fkc.referenced_object_id = cr.object_id
      and fkc.referenced_column_id = cr.column_id
ORDER BY 1, fkc.constraint_column_id

Índices

Nota: Esta consulta devuelve una fila por índice, y muestra las columnas separadas por comas.

SELECT DISTINCT
  da.tabla AS tabla,
  i.name AS indice,
  da.tipo AS tipo,
  CASE i.is_primary_key
    WHEN 1 THEN 'Sí'
    ELSE 'No'
  END AS llave_primaria,
  CASE i.is_unique
    WHEN 1 THEN 'Sí'
    ELSE 'No'
  END AS llave_unica,
  CASE i.is_unique_constraint
    WHEN 1 THEN 'Sí'
    ELSE 'No'
  END AS restriccion_unicidad,
  LEFT(cols.names, LEN(cols.names) - 1) columnas
FROM sys.tables AS t
  INNER JOIN sys.columns c
    ON t.object_id = c.object_id
  INNER JOIN sys.index_columns AS ic
    ON ic.OBJECT_ID = c.OBJECT_ID AND ic.column_id = c.column_id
  INNER JOIN sys.indexes AS i
    ON i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id
  CROSS APPLY (
    SELECT
      SCHEMA_NAME(schema_id) + '.' + t.name tabla,
      CASE i.index_id
        WHEN 1 THEN 'CLUSTERED'
        ELSE 'NONCLUSTERED'
      END AS tipo
  ) da
  CROSS APPLY (
    SELECT c_i.name + ','
    FROM sys.columns c_i
      INNER JOIN sys.index_columns AS ic_i
        ON ic_i.OBJECT_ID = c_i.OBJECT_ID AND ic_i.column_id = c_i.column_id
    WHERE i.OBJECT_ID = ic_i.OBJECT_ID AND i.index_id = ic_i.index_id
    FOR XML PATH ('')
  ) cols(names)
ORDER BY 1, 2, 3