sql-server - restricciones - relacionar tablas en sql server por codigo

Esquema de información y claves principales

Question

¿Cómo imprimo una ''clave principal'' para la columna con la clave principal?

Obtengo ''clave principal'' para todas las columnas si la tabla tiene una clave principal, en lugar de una columna con la clave principal y las otras columnas como en blanco en keyType.

   SELECT c.TABLE_NAME, 
          c.COLUMN_NAME, 
          c.DATA_TYPE, 
          c.Column_default, 
          c.character_maximum_length, 
          c.numeric_precision, 
          c.is_nullable,
          CASE 
            WHEN u.CONSTRAINT_TYPE = ''PRIMARY KEY'' THEN ''primary key''
            ELSE '''' 
          END AS KeyType
     FROM INFORMATION_SCHEMA.COLUMNS as c
LEFT JOIN information_schema.table_constraints as u ON c.table_name = u.table_name
 ORDER BY table_name

Answer 1

Como una adición, considere mirar:

sp_helptext N''sp_help''

A veces aprender del equipo de MS es algo grandioso. :-)

Answer 2

SELECT  c.TABLE_NAME, c.COLUMN_NAME,c.DATA_TYPE, c.Column_default, c.character_maximum_length, c.numeric_precision, c.is_nullable
             ,CASE WHEN pk.COLUMN_NAME IS NOT NULL THEN ''PRIMARY KEY'' ELSE '''' END AS KeyType
FROM INFORMATION_SCHEMA.COLUMNS c
LEFT JOIN (
            SELECT ku.TABLE_CATALOG,ku.TABLE_SCHEMA,ku.TABLE_NAME,ku.COLUMN_NAME
            FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
            INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS ku
                ON tc.CONSTRAINT_TYPE = ''PRIMARY KEY'' 
                AND tc.CONSTRAINT_NAME = ku.CONSTRAINT_NAME
         )   pk 
ON  c.TABLE_CATALOG = pk.TABLE_CATALOG
            AND c.TABLE_SCHEMA = pk.TABLE_SCHEMA
            AND c.TABLE_NAME = pk.TABLE_NAME
            AND c.COLUMN_NAME = pk.COLUMN_NAME
ORDER BY c.TABLE_SCHEMA,c.TABLE_NAME, c.ORDINAL_POSITION 

Answer 3

un muy pequeño complemento a la primera respuesta. Es posible ver al mismo tiempo la MS_Description (u otras propiedades extendidas)

SELECT  c.TABLE_NAME, c.COLUMN_NAME,c.DATA_TYPE, c.Column_default,  c.character_maximum_length, c.numeric_precision, c.is_nullable
             ,CASE WHEN pk.COLUMN_NAME IS NOT NULL THEN ''PRIMARY KEY'' ELSE '''' END AS KeyType, c.ORDINAL_POSITION 
             ,convert(varchar(8000), ex.value) as coln
   FROM INFORMATION_SCHEMA.COLUMNS c
   LEFT JOIN (
       SELECT ku.TABLE_CATALOG,ku.TABLE_SCHEMA,ku.TABLE_NAME,ku.COLUMN_NAME
            FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
            INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS ku
                ON tc.CONSTRAINT_TYPE = ''PRIMARY KEY'' 
                AND tc.CONSTRAINT_NAME = ku.CONSTRAINT_NAME
         )   pk 
    ON  c.TABLE_CATALOG = pk.TABLE_CATALOG
            AND c.TABLE_SCHEMA = pk.TABLE_SCHEMA
            AND c.TABLE_NAME = pk.TABLE_NAME
            AND c.COLUMN_NAME = pk.COLUMN_NAME
    outer apply ::fn_listextendedproperty(''MS_Description'', ''schema'', ''dbo'', ''table'', c.TABLE_NAME, ''column'', c.COLUMN_NAME) ex
   ORDER BY c.TABLE_SCHEMA,c.TABLE_NAME, c.ORDINAL_POSITION