2016-07-09 22:23:00
Source: Add [Is Primary Key Column] to this query - Stack Exchange
The sys.key_constraints and sys.index_columns catalog views will identify the PK columns. You can JOIN them together and then use that set as a derived table to LEFT JOIN to your main query which will allow for not filtering out columns that are not part of a PK.
Also, you want to use [user_type_id] instead of [system_type_id] to avoid a Cartesian product. Most of the time there won't be a difference between these two fields. But if you have User Defined Data Types (UDDTs), or use the sysname datatype (which is an alias to NVARCHAR(128)), then the [system_type_id] value will be repeated in the sys.types catalogue view.
The following query lists all fields in all tables, adding a computed field to denote if the column is part of the PK or not. It handles composite PKs as well.
SELECT field.column_id AS FieldId,
entity.object_id AS EntityId,
field.[name] AS [Name],
ty.[name] AS [DataType],
field.[max_length] AS [Size],
field.[precision] AS [DecimalPrecision],
field.[is_nullable] AS [IdNullable],
CONVERT(BIT, IIF(pkcol.index_id IS NOT NULL, 1, 0)) AS [IsPrimaryKey],
field.[is_identity] AS [IsAutoNumbered]
FROM sys.tables entity
INNER JOIN sys.columns field
ON field.[object_id] = entity.[object_id]
INNER JOIN sys.types ty
ON ty.[user_type_id] = field.[user_type_id] -- do not use system_type_id
LEFT JOIN (
SELECT ind.[object_id],
ind.[index_id],
ind.[column_id]
FROM sys.index_columns ind
INNER JOIN sys.key_constraints pks
ON pks.[parent_object_id] = ind.[object_id]
AND pks.[unique_index_id] = ind.[index_id]
WHERE pks.[type] = 'PK'
) pkcol
ON pkcol.[object_id] = entity.[object_id]
AND pkcol.[column_id] = field.[column_id]
ORDER BY entity.[name], field.[name];
max_length column
The max_length column in sys.columns is the maximum number of bytes that the column can take up per row. In the case of fixed-length fields such as INT, DATETIME, etc, those fields always take up their maximum amount of space, unless you are using the SPARSE option (per column setting) or have enabled Data Compression (per index setting).
For variable-length fields such as VARCHAR, NVARCHAR, XML, etc, that value is the maximum number of bytes it can take up. A value of -1 indicates a value of approx 2 GB which is used by the MAX (VARCHAR, NVARCHAR and VARBINARY) and XML types.
The Unicode string types (NCHAR and NVARCHAR) that are not declared as MAX will display a max_length of 2 * declared_max since the (safe) assumption is that they use 2 bytes per "character". This is not always the case since Supplementary Characters are actually 4 bytes per "character". But, this is why sysname, being an alias for NVARCHAR(128), has a max_length of 256. Similarly, the less frequently used Double Byte Character Set (DBCS) collations that allow for mapping more than 256 characters in an 8-bit VARCHAR / CHAR field will store characters in either 1 or 2 bytes, depending on the character. Meaning, for VARCHAR data using a DBCS collation, and for NVARCHAR data, declaring a column or variable as VARCHAR(x) or NVARCHAR(x) does not guarantee x characters are storable: you can only fit x characters if all of those characters are of the standard length for that type (i.e. 1 byte for VARCHAR, 2 bytes for NVARCHAR). So, an NVARCHAR(3) field is given 6 bytes maximum to use. That can fit: 3 regular two-bytes Unicode characters, or 1 regular two-byte character and 1 Supplementary Character at 4 bytes (which is only 2 characters, not 3). It cannot fit 2 Supplementary Characters as that would require 8 bytes.
The deprecated TEXT, NTEXT, and IMAGE types that nobody is using anymore (that's sarcasm) show a max_length of
