filmov
tv
3 Simple Ways to check if Column Exists in the Table - SQL SERVER

Показать описание
How to check if a column exists in a SQL Server table?
IF NOT EXISTS(SELECT 1 FROM SYS.COLUMNS
WHERE [Name] = N'DateOfBirth' AND Object_ID = Object_ID(N'[YarkulTestDb1].[dbo].[Person]'))
BEGIN
SELECT 'COLUMN DOES NOT EXISTS'
END
--2. INFORMATION_SCHEMA.COLUMNS
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Person' AND COLUMN_NAME = 'DateOfBirth')
BEGIN
SELECT 'COLUMN DOES NOT EXISTS'
END
--3. COL_LENGTH
IF COL_LENGTH('dbo.[Person]', 'DateOfBirth') IS NULL
BEGIN
SELECT 'COLUMN DOES NOT EXISTS'
END
IF NOT EXISTS(SELECT 1 FROM SYS.COLUMNS
WHERE [Name] = N'DateOfBirth' AND Object_ID = Object_ID(N'[YarkulTestDb1].[dbo].[Person]'))
BEGIN
SELECT 'COLUMN DOES NOT EXISTS'
END
--2. INFORMATION_SCHEMA.COLUMNS
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Person' AND COLUMN_NAME = 'DateOfBirth')
BEGIN
SELECT 'COLUMN DOES NOT EXISTS'
END
--3. COL_LENGTH
IF COL_LENGTH('dbo.[Person]', 'DateOfBirth') IS NULL
BEGIN
SELECT 'COLUMN DOES NOT EXISTS'
END