Souvenirs de IT

waitfor delay '00:01:00'

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

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

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

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