Muchas veces los desarrolladores tenemos la necesidad de consultar datos de la metadata de SQL Server, que es esto de la metadata? pues bien, la metadata es la información que guarda SQL Server en tablas sobre datos como (Stores, tablas, columnas de una tabla, las vistas, el código de los Store, etc.)

Imaginemos que deseamos saber los parámetros que tiene un Store Procedure o bien en que tablas se encuentra una columna dada, o cuales son las relaciones que tiene una tabla, para poder hacer todo este tipo de querys necesitamos consultar esta metadata.

Bien, como lo hacemos, pues hay varias formas , una de ellas es consultas las tablas internas de SQL lo cual no es muy recomendado porque pueden cambiar entre versiones del producto (hasta con la instalación de un service pack) y dejarnos de funcionar nuestras querys, a partir de 2005 se pueden usar las vistas de sistema que son una mejora importante ya que nos dan una capa intermedia de acceso sin acceder directamente a las tablas de sistema, pero solo funciona a partir de 2005.

La otra opción es usar unas vistas ANSI (Standard para aquellas bases de datos que soporten ANSI) llamadas INFORMATION_SCHEMA, estas vistas se encuentran en casi todas las versiones de SQL Server (2000, 2005 y 2008) pero como son ANSI no se puede obviamente consultar todo lo que se puede hacer con las vistas o tablas internas.

Ahora bien, estas vistas ANSI si a los efectos de lo que un desarrollador puede consumir abarcan casi el 90% ya que en todos los motores existen las tablas, las relaciones, las columnas etc., esas cosas que consumimos con frecuencia las resuelven estas vistas ANSI sin mayor problema.

Bien, en este post les voy a mostrar distintas consultas habituales que hacemos como desarrolladores y las resolveré con las INFORMATION_SCHEMA.

Consultar información de las columnas de tablas

-- TABLAS QUE CONTIENEN UNA COLUMNA

SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME = 'CUSTOMERID'

Listado de Tablas

SELECT * FROM INFORMATION_SCHEMA.TABLES 

Campos que conforman una PK

SELECT 
   TC.TABLE_NAME, CU.COLUMN_NAME 
FROM 
   INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC 
   INNER JOIN 
   INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
   ON TC.CONSTRAINT_NAME = CU.CONSTRAINT_NAME 
   WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY' 
   AND TC.TABLE_NAME = 'CUSTOMER' -- TABLA 
ORDER BY TC.TABLE_NAME    

Relaciones entre tablas

SELECT 
    FK_Table  = FK.TABLE_NAME, 
    FK_Column = CU.COLUMN_NAME, 
    PK_Table  = PK.TABLE_NAME, 
    PK_Column = PT.COLUMN_NAME, 
    ConstraintName = C.CONSTRAINT_NAME 
FROM 
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C 
    INNER JOIN 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK 
        ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME 
    INNER JOIN 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK 
        ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME 
    INNER JOIN 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU 
        ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME 
    INNER JOIN 
    ( 
      SELECT 
            TC.TABLE_NAME, CU.COLUMN_NAME 
      FROM 
      INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC 
      INNER JOIN 
      INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
      ON TC.CONSTRAINT_NAME = CU.CONSTRAINT_NAME 
      WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY' 
    ) PT 
    ON PT.TABLE_NAME = PK.TABLE_NAME 
WHERE 
PK.TABLE_NAME = 'SALESORDERHEADER' -- TU TABLA

Checks de una tabla dada

SELECT TC.TABLE_SCHEMA,TC.TABLE_NAME,CU.COLUMN_NAME,
CK.CHECK_CLAUSE 
  FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CU
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS  TC
ON TC.TABLE_NAME = CU.TABLE_NAME 
AND
TC.CONSTRAINT_NAME = CU.CONSTRAINT_NAME 
INNER JOIN
INFORMATION_SCHEMA.CHECK_CONSTRAINTS CK
ON
CK.CONSTRAINT_NAME = TC.CONSTRAINT_NAME 
WHERE CONSTRAINT_TYPE = 'CHECK'
AND
TC.TABLE_NAME = 'CUSTOMER' -- TU TABLA

Parámetros de un Store Procedure

SELECT *
FROM INFORMATION_SCHEMA.PARAMETERS 
WHERE SPECIFIC_NAME ='ufnGetProductDealerPrice'

Resumen:

Aquí les mostré algunas de las querys que mas usamos los desarrolladores cuando necesitamos consultar la metadata, como podrán haber observado no es nada complejo hacerlo y como en ANSI lo podemos usar en todas las versiones de SQL Server, para cosas mas complejas y quizás mas para administradores DBA es aconsejable usar las vistas de sistema de 2005 / 2008 y en 2000 las tablas de sistema pero para los desarrolladores yo recomiendo las INFORMATION_SCHEMA ya que con ellas cubren casi todas las necesidades y ante cambios de versión de producto no deben actualizar su código.

Les dejo un adjunto con todo el código acá descripto y algunos ejemplos mas