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