Pytanie Jak sprawdzić, czy kolumna istnieje w tabeli programu SQL Server?


Muszę dodać konkretną kolumnę, jeśli nie istnieje. Mam coś podobnego do następującego, ale zawsze zwraca false:

IF EXISTS(SELECT *
          FROM   INFORMATION_SCHEMA.COLUMNS
          WHERE  TABLE_NAME = 'myTableName'
                 AND COLUMN_NAME = 'myColumnName') 

Jak mogę sprawdzić, czy kolumna istnieje w tabeli bazy danych SQL Server?


1575
2017-09-25 12:34


pochodzenie


Tak naprawdę nie uważam, że jest coś nie tak z kodem w pytaniu: Działa to bardzo dobrze dla mnie w 2008 R2. (Być może uruchomiłeś go w niewłaściwej bazie danych? Może w twojej bazie danych rozróżniana była wielkość liter i nie miałeś tego poprawnie w swoich ciągach myTableName / myColumnName? Ten typ zapytania wydaje się bardziej elastyczny niż rozwiązanie COL_LENGTH: jestem w stanie aby uruchomić go na innej bazie danych, a nawet na łączu do bazy danych przez odpowiednie prefiksowanie "INFORMATION_SCHEMA". Nie można tego uzyskać za pomocą funkcji metadanych COL_LENGTH. - mwardm
@mwardm - COL_LENGTH('AdventureWorks2012.HumanResources.Department ','ModifiedDate') działa w porządku. - Martin Smith
Mała podpowiedź: jeśli chcesz zaktualizować kolumnę zaraz po dodaniu kolumny (wierzę, że wielu użytkowników szukało tego artykułu w tym celu), możesz użyć EXEC sp_executesqlz utworzonym UPDATE komunikat. - cassandrad
Prawdziwa odpowiedź brzmi: powinieneś dodać bazę danych, którą sprawdzasz, aby była FROM [YourDatabase].INFORMATION_SCHEMA.COLUMNS - Alex Kwitny


Odpowiedzi:


SQL Server 2005 i dalsze:

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

Wersja Martina Smitha jest krótsza:

IF COL_LENGTH('schemaName.tableName', 'columnName') IS NOT NULL
BEGIN
    -- Column Exists
END

1729
2017-09-25 12:39



Możesz też zaoszczędzić trochę pisania i używania IF COL_LENGTH('tableName', 'columnName') IS NOT NULL - Martin Smith
@Mitch - Według dokumentów będzie wracać NULL jeśli użytkownik nie ma uprawnień do wyświetlania metadanych, ale sys.columns sprawdza także dostęp użytkownika. - Martin Smith
@MitchWheat -> Powiedziałbym zamiast wybierz * wewnątrz, jeśli istnieje, użyłbym opcji select 1 z sys.columns, dzięki czemu po prostu zwraca wartość skalarną, zamiast zestawu wierszy. Twoje myśli ? - vijaysylvester
Może to być oczywiste, ale jeśli tabela jest w schemacie innym niż dbo, musisz zapisać schemat jako część nazwy: ... AND [object_id] = OBJECT_ID(N'schemaName.tableName' ...) niezrobienie tego spowoduje, że zapytanie nie zwróci żadnych wierszy, nawet jeśli kolumna istnieje. - user1778770
@MitchWheat, który zasugerował, że dostarczyłeś buggy recomendation 1658 osobom, a większość z nich spowodowała potencjalne błędy w swoim kodzie z twojego powodu. Nadal nie odpowiadasz, lub sprawdziłeś mój dostarczony przypadek, gdzie wyraźnie pokazano, że twoja metoda jest zła: oi63.tinypic.com/2mopgyc.jpg. Nie działa w żadnej procedurze z normalnie wdrożoną hierarchią uprawnień serwera. Więc proszę, zatrzymajcie demagogię i po prostu spróbujcie prawdziwego przypadku, podanego na moim zdjęciu z "rozwiązania". Następnie przepraszam i edytuj swoją odpowiedź, aby przestać zalecać ludziom błędne podejście. To moja ostatnia uwaga. Miłej polowanie na ocenę. - Juozas


Bardziej zwięzła wersja

 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

Punkt dotyczący uprawnień do wyświetlania metadanych dotyczy wszystkich odpowiedzi, nie tylko tej.

Zwróć uwagę, że pierwsza nazwa tabeli parametrów to COL_LENGTH może być w jednym, dwóch lub trzech nazwach w razie potrzeby.

Przykładem odwołującym się do tabeli w innej bazie danych jest

COL_LENGTH('AdventureWorks2012.HumanResources.Department','ModifiedDate')

Jedną z różnic w porównaniu z używaniem widoków metadanych są funkcje metadanych, takie jak COL_LENGTH zawsze zwraca tylko dane o zatwierdzonych zmianach, niezależnie od obowiązującego poziomu izolacji.


854
2018-03-20 14:47



Jest to mniej czytelne niż niektóre inne odpowiedzi, prawdopodobnie dlatego, że nie jest tak wysoko oceniany. - Bill Yang
@Bill - Mniej czytelny w jaki sposób? Wygląda dobrze w Firefoksie. Ta odpowiedź została opublikowana ponad 2 lata później niż przyjęta, co wyjaśnia ocenę IMO. Jeśli mniej znaczyło się, że jest to test na istnienie, ten typ języka jest dość powszechny w SQL Server. na przykład za pomocą IF OBJECT_ID('TableName','U') IS NULL aby sprawdzić istnienie obiektu lub DB_ID('foo') aby sprawdzić istnienie bazy danych. - Martin Smith
@MartinSmith Jestem pewien, że miał na myśli mniej czytelny, ponieważ gdybyś nie znał tego idiomu i odziedziczyłeś ten kod od kogoś innego, nie zrozumiałbyś od razu, co robi kod. Coś jak pisanie x>>2 zamiast x/4 w C ++. Bardziej szczegółowy kod (if exists (select column_name from information_schema ...)) zajmuje dużo więcej miejsca, ale nikt nigdy nie drapie głowy, próbując dowiedzieć się, co robi. - Kip
Poza tym, że jest to bardziej zwięzłe, jest to szybsze rozwiązanie. Dostęp INFORMATION_SCHEMA widoki lub sys.columns uderza w dysk, podczas gdy COL_LENGTH korzysta z zapisanych w pamięci podręcznej metadanych bazy danych. - wqw
Prawdopodobnie nie jest to najlepiej oceniona odpowiedź, ponieważ została podana 2,5 roku po drugiej. Dlatego zawsze sprawdzam daty porównując oceny dwóch odpowiedzi. Trzeba dużo więcej czasu, aby pokonać odpowiedź udzieloną dużo wcześniej. ;) - Sean


Zmień poniżej, aby spełnić określone wymagania:

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

Edytuj, aby zaadresować edycję do pytania: To powinno zadziałać - uważnie przyjrzyj się kodowi na głupie błędy; czy wysyłasz zapytanie do INFORMATION_SCHEMA w tej samej bazie danych, na przykład do której wstawia się twój insert? Czy masz literówkę w nazwie tabeli / kolumny w którejś z instrukcji?


125
2017-09-25 12:35



Właśnie dowiedziałem się, że dodanie TABLE_SCHEMA = "mySchema" po klauzuli where rozwiązuje problem. - Maciej
-1: nie odpowiada na pytanie OP, dodaje tylko nowe informacje o tym, jak dodać nową kolumnę mimo, że OP nie pyta o to w ogóle, nie odpowiada na komentarz OP. - ANeves


Spróbuj tego...

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

63
2017-09-25 12:35



Ta metoda działa również z SQL CE, podczas gdy niektóre inne wymienione metody nie. - Sandra Walters
Możesz użyć SELECT 1 zamiast SELECT TOP 1 1 ;). - shA.t
Wewnątrz EXISTS instrukcja SQL automatycznie optymalizuje kolumny z dala (podobnie jak count(*)) więc SELECT * wystarczy. - Marc L.


wolałbym INFORMATION_SCHEMA.COLUMNS nad tabelą systemową, ponieważ Microsoft nie gwarantuje zachowania tabel systemowych między wersjami. Na przykład, dbo.syscolumns nadal działa w SQL 2008, ale jest przestarzały i mógł zostać usunięty w dowolnym momencie w przyszłości.


43
2018-06-26 08:58



Widoki "INFORMATION_SCHEMA" mogą być niekompletne, ponieważ nie są aktualizowane dla wszystkich nowych funkcji " - onedaywhen
No tak, to oczywiste, od kiedy INFORMATION_SCHEMA widoki zawierają tylko standardowe metadane zgodne ze standardem ANSI. Jest to jednak wystarczające do testu na istnienie. - Christian Hayter
Microsoft mówi: "W przyszłych wersjach SQL Server, Microsoft może rozszerzyć definicję dowolnego widoku katalogu systemowego poprzez dodanie kolumn na końcu listy kolumn. Zalecamy, aby nie używać składni SELECT * FROM sys.catalog_view_name w kodzie produkcyjnym, ponieważ liczba kolumny zwrócone mogą się zmienić i złamać aplikację. " Oznacza to, że nie usuwają kolumn ani nie zmieniają ich kolejności. Kompatybilność wsteczna jest wystarczająco dobra we wszystkich przypadkach oprócz krawędzi. - siride


Możesz użyć widoków schematu informacji informacyjnych, aby dowiedzieć się prawie wszystkiego o tabelach, które Cię interesują:

SELECT *
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = 'yourTableName'
 ORDER BY ORDINAL_POSITION

Możesz także przeglądać widoki, zapisane procedury i prawie wszystko o bazie danych za pomocą widoków Information_schema.


38
2017-09-25 12:37





Najpierw sprawdź, czy table/column(id/name) istnieje kombinacja w dbo.syscolumns (wewnętrzna tabela SQL Server, która zawiera definicje pól), a jeśli nie, wydaj odpowiednią ALTER TABLE zapytanie, aby je dodać. Na przykład:

IF NOT EXISTS ( SELECT  *
            FROM    syscolumns
            WHERE   id = OBJECT_ID('Client')
                    AND name = 'Name' ) 
ALTER TABLE Client
ADD Name VARCHAR(64) NULL

28
2017-09-25 12:38





Spróbuj czegoś takiego:

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

Następnie użyj go tak:

IF ColumnExists('xxx', 'yyyy') = 'F'
BEGIN
  ALTER TABLE xxx
  ADD yyyyy varChar(10) NOT NULL
END
GO

Powinien działać zarówno na SQL Server 2000 i SQL Server 2005. Nie jestem pewien co do SQL Server 2008, ale nie rozumiem, dlaczego nie.


26
2018-05-01 04:46





Dla ludzi, którzy sprawdzają istnienie kolumn, aby je upuścić.

W SQL Server 2016 możesz używać nowych instrukcji DIE zamiast dużych IF opakowania

ALTER TABLE Table_name DROP COLUMN IF EXISTS Column_name

24
2018-03-03 15:49