risposta-alla-domanda-sullo-sviluppo-web-bd.com

Come verificare se esiste una colonna in una tabella di SQL Server?

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?

1725
Maciej

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
1904
Mitch Wheat

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.

924
Martin Smith

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?

131
Luke Bennett

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
68
Leon Tayson

Preferirei 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.

44
Christian Hayter

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.

40
anonymous

Per prima cosa controlla se la combinazione tablename __/columnname __ (idname __/namename__) esiste in dbo.syscolumns (una tabella interna di SQL Server che contiene definizioni di campo), 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
31
mdb

Per le persone che stanno controllando l'esistenza della colonna per farlo cadere.

In SQL Server 2016 puoi usare le nuove istruzioni DIE invece dei big IF wrappers

ALTER TABLE Table_name DROP COLUMN IF EXISTS Column_name
30

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.

29
Matt Lacey

Un mio buon amico e collega mi ha mostrato come è possibile utilizzare anche un blocco IFcon funzioni SQL OBJECT_ID e COLUMNPROPERTYin SQL SERVER 2005+ per verificare la presenza di una colonna. È possibile 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
25
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
24

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
21
Joe M

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') 
20
Douglas Tondo

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')
18
FrostbiteXIII
if exists (
  select * 
  from INFORMATION_SCHEMA.COLUMNS 
  where TABLE_NAME = '<table_name>' 
  and COLUMN_NAME = '<column_name>'
) begin
  print 'Column you have specified exists'
end else begin
  print 'Column does not exist'
end
15
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;
11
Na30m

Una versione della 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
10
crokusek
select distinct object_name(sc.id)
from syscolumns sc,sysobjects so  
where sc.name like '%col_name%' and so.type='U'
10
Nishad

Esistono diversi modi per verificare l'esistenza di una colonna. Consiglio vivamente di utilizzare INFORMATION_SCHEMA.COLUMNS così come è stato creato per comunicare con l'utente. Considera le seguenti tabelle:

 sys.objects
 sys.columns

e anche alcuni altri metodi di accesso disponibili per controllare system catalog.

Inoltre, non c'è bisogno di usare SELECT *, basta testarlo con NULL value

IF EXISTS(
           SELECT NULL 
           FROM INFORMATION_SCHEMA.COLUMNS
           WHERE
             TABLE_NAME = 'myTableName'
             AND COLUMN_NAME = 'myColumnName'
         ) 
7
Ali Elmi

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'
7
Daniel Barbalace

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, Name è il ColumnName da aggiungere e Object_Id è il TableName

6
UJS

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
6
Arsman Ahmad

La query sottostante può essere utilizzata per verificare se la colonna cercata esiste o meno nella tabella. Possiamo prendere una decisione in base al risultato cercato anche come mostrato di seguito.

IF EXISTS (SELECT 'Y' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = <YourTableName> AND COLUMN_NAME = <YourColumnName>)
  BEGIN
    SELECT 'Column Already Exists.'
  END
  ELSE
  BEGIN
    ALTER TABLE <YourTableName> ADD <YourColumnName> <DataType>[Size]
  END
4
Suraj Kumar

Ancora un'altra variazione ...

SELECT 
  Count(*) AS existFlag 
FROM 
  sys.columns 
WHERE 
  [name] = N 'ColumnName' 
  AND [object_id] = OBJECT_ID(N 'TableName')
3
Manuel Alves

Esegui la query seguente per verificare se la colonna esiste nella tabella specificata:

IF(SELECT COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'TableName' AND COLUMN_NAME = 'ColumnName') IS NOT NULL
PRINT 'Column Exists in the given table';
1
S Krishna

tabella -> tabella di script come -> nuova finestra - hai uno script di progettazione. controlla e trova il nome della colonna nelle nuove finestre

1
arnav
IF EXISTS(SELECT 1 FROM sys.columns 
      WHERE Name = N'columnName'
      AND Object_ID = Object_ID(N'schemaName.tableName'))

Questo dovrebbe essere il modo più semplice e una soluzione immediata a questo problema. Ho usato questo più volte per scenari simili. Funziona come un fascino, non ci sono dubbi.

0
Ilangeeran