COLUMNPROPERTY: примеры (SQL)

Короткие примеры использования

Проверка свойства AllowsNull для столбца.

USE AdventureWorks2019;
GO
SELECT COLUMNPROPERTY(OBJECT_ID('Person.Address'), 'City', 'AllowsNull') AS CityAllowsNull;

CityAllowsNull
0

Определение, является ли столбец вычисляемым.

SELECT COLUMNPROPERTY(OBJECT_ID('Sales.SalesOrderHeader'), 'TotalDue', 'IsComputed') AS IsTotalDueComputed;

IsTotalDueComputed
0

Проверка столбца на наличие свойства IDENTITY.

SELECT COLUMNPROPERTY(OBJECT_ID('Person.Address'), 'AddressID', 'IsIdentity') AS IsAddressIdIdentity;

IsAddressIdIdentity
1

Получение точности для столбца типа decimal.

CREATE TABLE #TempTable (Price DECIMAL(10,2));
SELECT COLUMNPROPERTY(OBJECT_ID('tempdb..#TempTable'), 'Price', 'Precision') AS PrecisionValue;
DROP TABLE #TempTable;

PrecisionValue
10

Похожие функции в MS SQL

В SQL Server существуют другие функции для получения метаданных объектов.

• OBJECTPROPERTY и OBJECTPROPERTYEX - возвращают сведения о свойствах объектов базы данных, таких как таблицы, представления, процедуры. Используются, когда требуется информация об объекте в целом, а не о его столбцах.
• COL_LENGTH - возвращает длину столбца в байтах. Применяется для получения физического размера столбца.
• COL_NAME - возвращает имя столбца по идентификатору таблицы и номеру столбца. Обратная операция к получению свойств по имени.
• TYPEPROPERTY - возвращает сведения о свойстве типа данных. Полезно для проверки характеристик типов, а не конкретных столбцов.

Функцию COLUMNPROPERTY предпочтительнее использовать для проверки логических свойств столбца (IsIdentity, AllowsNull), тогда как для получения физических характеристик (длина, имя) больше подходят COL_LENGTH и COL_NAME.

Типичные ошибки

Основные ошибки связаны с неверными аргументами функции.

Использование несуществующего имени столбца или объекта приводит к возврату NULL.

SELECT COLUMNPROPERTY(OBJECT_ID('Person.Address'), 'NonExistentColumn', 'AllowsNull') AS Result;

Result
NULL

Ошибка может возникнуть при передаче NULL в качестве аргумента id.

SELECT COLUMNPROPERTY(NULL, 'City', 'AllowsNull') AS Result;

Result
NULL

Указание недопустимого значения для свойства property также возвращает NULL.

SELECT COLUMNPROPERTY(OBJECT_ID('Person.Address'), 'City', 'InvalidProperty') AS Result;

Result
NULL

Важно учитывать контекст базы данных. Функция OBJECT_ID() выполняется в контексте текущей базы, поэтому может не найти объект из другой базы.

Изменения в последних версиях

В SQL Server 2012 и более поздних версиях были добавлены новые значения для аргумента property.

• Свойство IsSparse стало доступно для проверки столбцов, объявленных как SPARSE.
• Для поддержки хранимых процедур, скомпилированных в собственном коде, и оптимизированных для памяти таблиц, некоторые свойства могут возвращать уточненные значения, но сама функция не претерпела значительных синтаксических изменений.

В актуальных версиях SQL Server функция сохраняет обратную совместимость, и её поведение с ранее существовавшими свойствами остаётся неизменным.

Расширенные примеры использования

Динамическое построение запроса в зависимости от допустимости NULL в столбце.

DECLARE @TableName NVARCHAR(128) = N'Person.Address';
DECLARE @ColumnName NVARCHAR(128) = N'City';
DECLARE @Sql NVARCHAR(MAX);

IF COLUMNPROPERTY(OBJECT_ID(@TableName), @ColumnName, 'AllowsNull') = 1
    SET @Sql = N'SELECT ' + QUOTENAME(@ColumnName) + N' FROM ' + QUOTENAME(@TableName) + N' WHERE ' + QUOTENAME(@ColumnName) + N' IS NULL';
ELSE
    SET @Sql = N'SELECT ' + QUOTENAME(@ColumnName) + N' FROM ' + QUOTENAME(@TableName) + N' WHERE ' + QUOTENAME(@ColumnName) + N' = N''' + N'Sample' + N'''';

PRINT @Sql;
-- EXEC sp_executesql @Sql;

SELECT [City] FROM [Person.Address] WHERE [City] = N'Sample'

Получение списка всех столбцов таблицы с их основными свойствами.

SELECT
    c.name AS ColumnName,
    COLUMNPROPERTY(c.object_id, c.name, 'IsIdentity') AS IsIdentity,
    COLUMNPROPERTY(c.object_id, c.name, 'AllowsNull') AS AllowsNull,
    COLUMNPROPERTY(c.object_id, c.name, 'IsComputed') AS IsComputed
FROM sys.columns c
WHERE c.object_id = OBJECT_ID('Person.Address');

ColumnName       | IsIdentity | AllowsNull | IsComputed
AddressID        | 1          | 0          | 0
AddressLine1     | 0          | 0          | 0
AddressLine2     | 0          | 1          | 0
City             | 0          | 0          | 0
...

Использование в проверочном ограничении для условной логики.

IF COLUMNPROPERTY(OBJECT_ID('Sales.SalesOrderDetail'), 'ProductID', 'IsIdentity') = 0
BEGIN
    PRINT 'Столбец ProductID не является идентификатором.';
    -- Дополнительные действия
END
ELSE
    PRINT 'Столбец ProductID - IDENTITY.';

Столбец ProductID не является идентификатором.

Аналоги в других СУБД

Прямого аналога функции COLUMNPROPERTY в других СУБД часто нет, но аналогичную информацию можно получить через системные представления или функции.

MySQL: Информация о свойствах столбцов находится в представлении INFORMATION_SCHEMA.COLUMNS.

SELECT IS_NULLABLE, COLUMN_TYPE, EXTRA
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'database_name'
  AND TABLE_NAME = 'table_name'
  AND COLUMN_NAME = 'column_name';

IS_NULLABLE | COLUMN_TYPE | EXTRA
YES         | int(11)     | auto_increment

PostgreSQL: Данные о столбцах доступны в information_schema.columns и pg_catalog.pg_attribute.

SELECT is_nullable, column_default, is_identity
FROM information_schema.columns
WHERE table_name = 'table_name'
  AND column_name = 'column_name';

is_nullable | column_default             | is_identity
YES         | nextval('seq_name'::regclass) | YES

Oracle: Свойства столбцов можно узнать из представлений ALL_TAB_COLS или USER_TAB_COLS.

SELECT NULLABLE, DATA_TYPE, DATA_PRECISION, DATA_SCALE
FROM ALL_TAB_COLS
WHERE TABLE_NAME = 'TABLE_NAME'
  AND COLUMN_NAME = 'COLUMN_NAME';

NULLABLE | DATA_TYPE | DATA_PRECISION | DATA_SCALE
N        | NUMBER    | 10             | 0

В отличие от MS SQL, где используется отдельная функция, в других СУБД чаще применяются запросы к системным каталогам, что требует знания структуры этих представлений.