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
           SELECT ind.[object_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 16, presumably being the size of the pointer left on the data page that points to the LOB page holding the data

Copyright © 2024 delaney. All rights reserved.