Devo aggiungere una colonna specifica se non esiste. Ho qualcosa di simile al seguente, ma restituisce sempre falso:
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTableName' AND COLUMN_NAME = 'myColumnName')
Come posso verificare se esiste una colonna in una tabella del database di SQL Server?
SQL Server 2005 in poi:
IF EXISTS(SELECT 1 FROM sys.columns WHERE Name = N'columnName' AND Object_ID = Object_ID(N'schemaName.tableName')) BEGIN -- Column Exists END
La versione di Martin Smith è più corta:
IF COL_LENGTH('schemaName.tableName', 'columnName') IS NOT NULL BEGIN -- Column Exists END
Una versione più concisa
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
Il punto sulle autorizzazioni alla visualizzazione dei metadati si applica a tutte le risposte, non solo a questa.
Si noti che il primo nome della tabella dei parametri su COL_LENGTH
può essere nel formato del nome di una, due o tre parti come richiesto.
Un esempio che fa riferimento a una tabella in un altro database è
COL_LENGTH('AdventureWorks2012.HumanResources.Department','ModifiedDate')
Una differenza rispetto a questa risposta rispetto all’uso delle viste dei metadati è che le funzioni di metadati come COL_LENGTH
restituiscono sempre solo i dati relativi alle modifiche apportate, indipendentemente dal livello di isolamento in vigore.
Modifica il seguito per soddisfare le tue esigenze specifiche:
if not exists (select column_name from INFORMATION_SCHEMA.columns where table_name = 'MyTable' and column_name = 'MyColumn') alter table MyTable add MyColumn int
Modifica per gestire la modifica alla domanda : Dovrebbe funzionare: dare un’occhiata attenta al codice per gli errori stupidi; stai interrogando INFORMATION_SCHEMA sullo stesso database in cui viene applicato il tuo inserto, ad esempio? Hai un refuso nel nome della tua tabella / colonna in entrambe le dichiarazioni?
Prova questo…
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
Preferire INFORMATION_SCHEMA.COLUMNS
su una tabella di sistema perché Microsoft non garantisce la conservazione delle tabelle di sistema tra le versioni. Ad esempio, dbo.syscolumns
funziona ancora in SQL 2008, ma è deprecato e potrebbe essere rimosso in qualsiasi momento in futuro.
Puoi utilizzare le viste di sistema dello schema di informazioni per scoprire praticamente qualsiasi cosa sulle tabelle che ti interessano:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'yourTableName' ORDER BY ORDINAL_POSITION
Puoi anche interrogare viste, stored procedure e praticamente qualsiasi cosa sul database usando le viste Information_schema.
Innanzitutto controlla se la combinazione table
/ column
( id
/ name
) esiste in dbo.syscolumns
(una tabella interna di SQL Server che contiene le definizioni dei campi), e se non emette la query ALTER TABLE
appropriata per aggiungerla. Per esempio:
IF NOT EXISTS ( SELECT * FROM syscolumns WHERE id = OBJECT_ID('Client') AND name = 'Name' ) ALTER TABLE Client ADD Name VARCHAR(64) NULL
Prova qualcosa come:
CREATE FUNCTION ColumnExists(@TableName varchar(100), @ColumnName varchar(100)) RETURNS varchar(1) AS BEGIN DECLARE @Result varchar(1); IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = @TableName AND COLUMN_NAME = @ColumnName) BEGIN SET @Result = 'T' END ELSE BEGIN SET @Result = 'F' END RETURN @Result; END GO GRANT EXECUTE ON [ColumnExists] TO [whoever] GO
Quindi usalo in questo modo:
IF ColumnExists('xxx', 'yyyy') = 'F' BEGIN ALTER TABLE xxx ADD yyyyy varChar(10) NOT NULL END GO
Dovrebbe funzionare sia su SQL Server 2000 che su SQL Server 2005. Non sono sicuro di SQL Server 2008, ma non vedo perché no.
Per le persone che stanno controllando l’esistenza della colonna per farlo cadere.
In SQL Server 2016 è ansible utilizzare le nuove istruzioni DIE anziché i grandi wrapper IF
ALTER TABLE Table_name DROP COLUMN IF EXISTS Column_name
declare @myColumn as nvarchar(128) set @myColumn = 'myColumn' if not exists ( select 1 from information_schema.columns columns where columns.table_catalog = 'myDatabase' and columns.table_schema = 'mySchema' and columns.table_name = 'myTable' and columns.column_name = @myColumn ) begin exec('alter table myDatabase.mySchema.myTable add' +' ['[email protected]+'] bigint null') end
Un mio buon amico e collega mi ha mostrato come è ansible utilizzare anche un blocco IF
con funzioni SQL OBJECT_ID
e COLUMNPROPERTY
in SQL SERVER 2005+ per verificare la presenza di una colonna. È ansible utilizzare qualcosa di simile al seguente:
Puoi vedere di persona qui
IF (OBJECT_ID(N'[dbo].[myTable]') IS NOT NULL AND COLUMNPROPERTY( OBJECT_ID(N'[dbo].[myTable]'), 'ThisColumnDoesNotExist', 'ColumnId') IS NULL) BEGIN SELECT 'Column does not exist -- You can add TSQL to add the column here' END
Questo ha funzionato per me in SQL 2000:
IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'table_name' AND column_name = 'column_name' ) BEGIN ... END
Prova questo
SELECT COLUMNS.* FROM INFORMATION_SCHEMA.COLUMNS COLUMNS, INFORMATION_SCHEMA.TABLES TABLES WHERE COLUMNS.TABLE_NAME = TABLES.TABLE_NAME AND Upper(COLUMNS.COLUMN_NAME) = Upper('column_name')
Avevo bisogno di qualcosa di simile per SQL SERVER 2000 e, come sottolinea @ Mitch, funziona solo inm 2005+.
Se dovesse aiutare qualcun altro, questo è ciò che ha funzionato per me alla fine:
if exists ( select * from sysobjects, syscolumns where sysobjects.id = syscolumns.id and sysobjects.name = 'table' and syscolumns.name = 'column')
if exists (select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='' and COLUMN_NAME='') begin print 'Column you have specified exists' end else begin print 'Column does not exists' end
IF NOT EXISTS( SELECT NULL FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'tablename' AND table_schema = 'db_name' AND column_name = 'columnname') THEN ALTER TABLE `TableName` ADD `ColumnName` int(1) NOT NULL default '0'; END IF;
select distinct object_name(sc.id) from syscolumns sc,sysobjects so where sc.name like '%col_name%' and so.type='U'
Una versione di tabella temporanea della risposta accettata :
if (exists(select 1 from tempdb.sys.columns where Name = 'columnName' and Object_ID = object_id('tempdb..#tableName'))) begin ... end
La risposta del grano è buona, ma si presume che non si abbiano coppie nome / nome tabella identiche in nessuno schema o database. Per renderlo sicuro per quella condizione usa questo …
select * from Information_Schema.Columns where Table_Catalog = 'DatabaseName' and Table_Schema = 'SchemaName' and Table_Name = 'TableName' and Column_Name = 'ColumnName'
Una delle soluzioni più semplici e comprensibili è:
IF COL_LENGTH('Table_Name','Column_Name') IS NULL BEGIN -- Column Not Exists, implement your logic END ELSE BEGIN -- Column Exists, implement your logic END
Ecco un semplice script che uso per gestire l’aggiunta di colonne nel database:
IF NOT EXISTS ( SELECT * FROM sys.Columns WHERE Name = N'QbId' AND Object_Id = Object_Id(N'Driver') ) BEGIN ALTER TABLE Driver ADD QbId NVARCHAR(20) NULL END ELSE BEGIN PRINT 'QbId is already added on Driver' END
In questo esempio, il Name
è il ColumnName
da aggiungere e Object_Id
è il TableName
Esistono diversi modi per verificare l’esistenza di una colonna. Consiglio vivamente di utilizzare INFORMATION_SCHEMA.COLUMNS
come è stato creato per comunicare con l’utente. Considera le seguenti tabelle:
sys.objects sys.columns
e persino alcuni altri metodi di accesso disponibili per controllare il system catalog.
Inoltre, non è necessario usare SELECT *
, basta testarlo con il NULL value
IF EXISTS( SELECT NULL FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTableName' AND COLUMN_NAME = 'myColumnName' )
Ancora un’altra variazione …
SELECT Count(*) AS existFlag FROM sys.columns WHERE [name] = N'ColumnName' AND [object_id] = OBJECT_ID(N'TableName')