Query that adds "is_primary_key" field for each column
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 catalog 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 tbl.[name] AS [TableName],
col.[name] AS [ColumnName],
ty.[name] AS [DataType],
col.[max_length] AS [MaxLength],
col.[is_nullable] AS [Nullable],
col.[is_identity] AS [Identity],
CONVERT(BIT, IIF(pkcol.index_id IS NOT NULL, 1, 0)) AS [PrimaryKey]
FROM sys.tables tbl
INNER JOIN sys.columns col
ON col.[object_id] = tbl.[object_id]
INNER JOIN sys.types ty
ON ty.[user_type_id] = col.[user_type_id] -- do not use system_type_id
LEFT JOIN (
SELECT ind.[object_id] AS [table_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'
ON pkcol.[table_object_id] = tbl.[object_id]
AND pkcol.[column_id] = col.[column_id]
ORDER BY tbl.[name], col.[name];
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
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
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 (
Int32.MaxValue) which is used by the
The Unicode string types (
NVARCHAR) that are not declared as
MAX will display a
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
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
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.
IMAGE types that nobody is using anymore (that's sarcasm) show a
max_length of 16, presumably being the size of the pointer left on the data page that points to the LOB page holding the data.