I am doing a large query to generate a data dictionary, where I am indicating to each field of the table if it is fk, pk, if I have designated a default value (CONSTRAINT-- DEFAULT ()). And it is in the latter that I can not associate the field with its possible default value (if it had one).
A possible result would be as follows:
You can use the following query to system tables (metadata) that includes the fields that you mention in your question and others:
SELECT
o.name as [Tabla], o.crdate as [Creacion tabla],
c.colid as [ID campo], c.name as [Nombre campo],
t.name as [Tipo campo],
c.length as [Tamaño], c.xprec as [Precision], c.xscale as [Escala],
CAST(c.isnullable as bit) as [Acepta nulos],
ISNULL(i.is_primary_key, 0) as [Llave primaria],
CAST(ISNULL(fk.referenced_column_id, 0) as bit) AS [Llave foranea],
ISNULL(sm.text, '') as [Valor DEFAULT]
FROM sysobjects o
INNER JOIN syscolumns c ON o.id = c.id
INNER JOIN systypes t ON c.xusertype = t.xusertype
LEFT OUTER JOIN
sys.index_columns ic ON ic.object_id = o.id AND ic.column_id = c.colid
LEFT OUTER JOIN
sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
LEFT OUTER JOIN
sys.syscomments sm ON sm.id = c.cdefault
LEFT OUTER JOIN
sys.foreign_key_columns fk ON fk.parent_object_id = o.id AND fk.parent_column_id = c.colid
WHERE o.type = 'u'
The query filters objects of type 'u' (objects created by the user), but you could filter by a specific table using the field o.name .
I also leave an exercise so you can check the result online.
System tables (name with prefix sys ) have many useful fields; I recommend that you read about them (or give them direct queries) to review their entire structure.