Add [Is Primary Key Column] to this query

I made this query, to list tables, its columns, data type and etc:

select t.name as 'Table',
       c.name 'Column',
       ty.name 'Data_Type',
       c.max_length as 'Max_Lenght',
       c.is_nullable as 'Null',
       c.is_identity as 'Identity'
from sys.tables t
    join sys.columns c on t.object_id=c.object_id
    join sys.types ty on c.system_type_id=ty.system_type_id
        order by 1

I have 2 questions:

Ques.1:

How can I add a column 'Is primary key?' to it? I could not find a table that help me ( I took a look at sys.indexes, sys.foreign_keys, INFORMATION_SCHEMA.table_constraints ).

Ques.2:

In sys.columns, int values have the max_lenght as 4, and some other fields too. What is this 4? 4kb per data? datetime and decimal too.

Thanks.

Query that adds "is_primary_key" field for each column

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 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'
          ) pkcol
       ON pkcol.[table_object_id] = tbl.[object_id]
      AND pkcol.[column_id] = col.[column_id]
ORDER BY tbl.[name], col.[name];

Info on 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 (Int32.MaxValue) 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.

StackExchange Database Administrators Q#128314, answer score: 11