17,354 Total Views, 67 views today
There are different ways to check whether specific column is exists in a sql server table
Solution:1 IF COL_LENGTH('schemaName.tableName', 'columnName') IS NOT NULL Print 'This Column is exists' else Print 'This Column doesn't exists' Example:1 use BlogDB go IF COL_LENGTH('dbo.Customer', 'Customer_Name') IS NOT NULL Print 'Customer_Name Column is exists in customer table ' else Print 'Customer_Name Column doesn''t exists in customer table ' go
Solution:2 IF EXISTS(SELECT 1 FROM sys.columns WHERE Name = 'columnName' AND Object_ID = Object_ID('schemaName.tableName')) Print 'This Column is exists' else Print 'This Column doesn't exists' Example:2 use BlogDB go IF EXISTS(SELECT 1 FROM sys.columns WHERE Name = 'Customer_Name' AND Object_ID = Object_ID('dbo.Customer')) Print 'Customer_Name Column is exists in customer table ' else Print 'Customer_Name Column doesn''t exists in customer table '
Solution:3 IF EXISTS( SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tableName' AND COLUMN_NAME = 'columnName' ) Example:3 use BlogDB go IF EXISTS( SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Customer' AND COLUMN_NAME = 'Customer_Name' ) Print 'Customer_Name Column is exists in customer table ' else Print 'Customer_Name Column doesn''t exists in customer table '
very nice