Получение деталей столбцов SQL Server: типы данных, PK

SELECT
    col.COLUMN_NAME, -- наименование столбца
    col.DATA_TYPE, -- тип данных столбца
    CASE
        WHEN col.IS_NULLABLE = 'NO' THEN 'NOT NULL' -- условие NOT NULL
    END as IS_NULLABLE,
    CASE
        WHEN pk.COLUMN_NAME IS NOT NULL THEN 'PRIMARY KEY' -- обозначение PRIMARY KEY
    END as COLUMN_KEY
FROM 
    INFORMATION_SCHEMA.COLUMNS col
LEFT JOIN 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE pk ON
    col.TABLE_SCHEMA = pk.TABLE_SCHEMA AND
    col.TABLE_NAME = pk.TABLE_NAME AND
    col.COLUMN_NAME = pk.COLUMN_NAME AND
    pk.CONSTRAINT_NAME = (
        SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
        WHERE 
            (TABLE_SCHEMA = col.TABLE_SCHEMA OR TABLE_SCHEMA IS NULL) AND
            TABLE_NAME = col.TABLE_NAME AND
            CONSTRAINT_TYPE = 'PRIMARY KEY'
    )
WHERE 
    col.TABLE_NAME = 'YourTableName' AND
    (col.TABLE_SCHEMA = 'YourSchemaName' OR col.TABLE_SCHEMA IS NULL)
ORDER BY 
    col.ORDINAL_POSITION;

SELECT
    c.name AS 'Название столбца',
    t.name AS 'Тип данных',
    c.max_length AS 'Максимальная длина',
    c.precision AS 'Точность',
    c.scale AS 'Масштаб',
    c.is_nullable AS 'NULL допустим',
    CASE WHEN ic.is_primary_key = 1 THEN 'Да' ELSE 'Нет' END AS 'Является ли первичным ключом'
FROM 
    sys.columns c
INNER JOIN 
    sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN 
    sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN 
    sys.indexes i ON i.object_id = ic.object_id AND i.index_id = ic.index_id AND i.is_primary_key = 1
WHERE 
    c.object_id = OBJECT_ID('YourSchemaName.YourTableName')
ORDER BY 
    c.column_id;

🧱 = Столбец
🏷️ = Тип данных
🔒 = NOT NULL
🔑 = PRIMARY KEY

Игровое поле (Таблица):
--------------------------------------
| 🧱 | 🏷️ | 🔒 | 🔑 |
|----|----|----|----|
|  1 |  A | 🔒 |    | <- Столбец NOT NULL без PRIMARY KEY
|  2 |  B |    | 🔑 | <- Столбец с PRIMARY KEY
|  3 |  C |    |    | <- Столбец, допускающий NULL
|  4 |  D | 🔒 | 🔑 | <- Столбец NOT NULL и PRIMARY KEY
--------------------------------------