Comment vérifier si une colonne existe dans une table SQL Server

Mots clés : sql-serversql-server-2008tsqlsql-server-2012sql-server-2016sql-server

meilleur 5 Réponses Comment vérifier si une colonne existe dans une table SQL Server

vote vote

96

IF EXISTS(SELECT 1 FROM sys.columns            WHERE Name = N'columnName'           AND Object_ID = Object_ID(N'schemaName.tableName')) BEGIN     -- Column Exists END 
IF COL_LENGTH('schemaName.tableName', 'columnName') IS NOT NULL BEGIN     -- Column Exists END 
vote vote

81

IF COL_LENGTH('table_name','column_name') IS NULL BEGIN /* Column does not exist or caller does not have permission to view the object */ END 
COL_LENGTH('AdventureWorks2012.HumanResources.Department','ModifiedDate') 
vote vote

70

if not exists (select                      column_name                from                      INFORMATION_SCHEMA.columns                where                      table_name = 'MyTable'                      and column_name = 'MyColumn')     alter table MyTable add MyColumn int 
vote vote

69

IF NOT EXISTS(   SELECT TOP 1 1   FROM INFORMATION_SCHEMA.COLUMNS   WHERE      [TABLE_NAME] = 'Employees'     AND [COLUMN_NAME] = 'EmployeeID') BEGIN   ALTER TABLE [Employees]     ADD [EmployeeID] INT NULL END 
vote vote

50

ALTER TABLE Table_name DROP COLUMN IF EXISTS Column_name 

Questions similaires