Câu hỏi Làm thế nào để kiểm tra xem một cột tồn tại trong một bảng SQL Server?


Tôi cần phải thêm một cột cụ thể nếu nó không tồn tại. Tôi có một cái gì đó như sau, nhưng nó luôn luôn trả về false:

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

Làm thế nào tôi có thể kiểm tra xem một cột tồn tại trong một bảng của cơ sở dữ liệu SQL Server?


1575
2017-09-25 12:34


gốc


Tôi không thực sự nghĩ rằng có bất cứ điều gì sai trái với mã trong câu hỏi: Hoạt động tốt cho tôi trong năm 2008 R2. (Có thể bạn đang chạy nó trong cơ sở dữ liệu sai? Có lẽ cơ sở dữ liệu của bạn phân biệt chữ hoa chữ thường và bạn không có quyền trong chuỗi myTableName / myColumnName của mình? Loại truy vấn này có vẻ linh hoạt hơn giải pháp COL_LENGTH: Tôi có thể để chạy nó với một cơ sở dữ liệu khác và thậm chí trên một liên kết cơ sở dữ liệu bằng tiền tố thích hợp "INFORMATION_SCHEMA". Không thể thấy cách thực hiện điều đó với hàm siêu dữ liệu COL_LENGTH. - mwardm
@mwardm - COL_LENGTH('AdventureWorks2012.HumanResources.Department ','ModifiedDate') hoạt động tốt. - Martin Smith
Ít gợi ý liên quan: nếu bạn muốn cập nhật một cột ngay sau khi thêm cột (tôi tin rằng nhiều người dùng đang tìm kiếm bài viết này cho mục đích đó), bạn có thể sử dụng EXEC sp_executesql với hình thành UPDATE tuyên bố. - cassandrad
Câu trả lời thực sự là bạn nên thêm cơ sở dữ liệu bạn đang kiểm tra để nó FROM [YourDatabase].INFORMATION_SCHEMA.COLUMNS - Alex Kwitny


Các câu trả lời:


SQL Server 2005 trở đi:

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

Phiên bản của Martin Smith ngắn hơn:

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

1729
2017-09-25 12:39



Hoặc bạn có thể lưu một số thao tác nhập và sử dụng IF COL_LENGTH('tableName', 'columnName') IS NOT NULL - Martin Smith
@Mitch - Theo tài liệu, nó sẽ trở lại NULL nếu người dùng không có quyền xem siêu dữ liệu nhưng sys.columns cũng kiểm tra quyền truy cập của người dùng. - Martin Smith
@MitchWheat -> Tôi sẽ nói thay vì chọn * bên trong nếu tồn tại, tôi sẽ sử dụng select 1 từ sys.columns, để nó chỉ trả về một giá trị vô hướng, thay vì một tập hợp các hàng. suy nghĩ của bạn ? - vijaysylvester
Điều này có thể hiển nhiên, nhưng nếu bảng nằm trong một lược đồ khác với dbo, bạn phải viết lược đồ như một phần của tên: ... AND [object_id] = OBJECT_ID(N'schemaName.tableName' ...) không làm như vậy, sẽ khiến truy vấn trả về không có hàng nào ngay cả khi cột đó tồn tại. - user1778770
@ MitchWheat có nghĩa là, bạn đã cung cấp đề xuất lỗi cho 1658 người và hầu hết trong số họ đã tạo ra các lỗi tiềm ẩn trong mã của họ vì bạn. Bạn vẫn không trả lời, hoặc bạn đã kiểm tra trường hợp được cung cấp của tôi, nơi hiển thị rõ ràng, rằng phương pháp của bạn là sai: oi63.tinypic.com/2mopgyc.jpg. Nó không hoạt động trong bất kỳ thủ tục nào với hệ thống phân cấp quyền máy chủ được triển khai bình thường. Vì vậy, xin vui lòng ngừng demagogy, và chỉ cần thử trường hợp sử dụng thực tế được cung cấp trong pic của tôi của "giải pháp" của bạn. Sau đó nói xin lỗi và chỉnh sửa câu trả lời của bạn, để ngừng đề xuất một chương trình lỗi cho mọi người. Đó là bình luận cuối cùng của tôi. Có cuộc săn tìm giá tốt. - Juozas


Một phiên bản ngắn gọn hơn

 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

Điểm về quyền truy cập vào xem siêu dữ liệu áp dụng cho tất cả các câu trả lời không chỉ là câu trả lời này.

Lưu ý rằng tên bảng tham số đầu tiên COL_LENGTH có thể ở định dạng một, hai hoặc ba phần theo yêu cầu.

Một ví dụ tham chiếu một bảng trong cơ sở dữ liệu khác nhau là

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

Một khác biệt với câu trả lời này so với việc sử dụng chế độ xem siêu dữ liệu là các hàm siêu dữ liệu như COL_LENGTH luôn luôn trả về dữ liệu về các thay đổi đã cam kết bất kể mức cô lập có hiệu lực như thế nào.


854
2018-03-20 14:47



Điều này ít dễ đọc hơn một số câu trả lời khác, có lẽ lý do tại sao nó không được đánh giá cao. - Bill Yang
@Bill - Ít dễ đọc hơn theo cách nào? Có vẻ tốt trong Firefox. Câu trả lời này đã được đăng hơn 2 năm so với câu trả lời được chấp nhận, giải thích về đánh giá IMO. Nếu bạn có nghĩa là ít rõ ràng rằng nó là một sự tồn tại kiểm tra kiểu thành ngữ này là khá phổ biến trong SQL Server. ví dụ. sử dụng IF OBJECT_ID('TableName','U') IS NULL để kiểm tra sự tồn tại của đối tượng hoặc DB_ID('foo') để kiểm tra sự tồn tại của cơ sở dữ liệu. - Martin Smith
@MartinSmith Tôi chắc chắn anh ấy có nghĩa là ít có thể đọc được bởi vì nếu bạn không biết thành ngữ này, và bạn thừa hưởng mã này từ người khác, bạn sẽ không ngay lập tức hiểu được mã nào. Giống như viết x>>2 thay vì x/4 trong C ++. Mã chi tiết hơn (if exists (select column_name from information_schema ...)) chiếm nhiều không gian hơn, nhưng không ai có thể gãi đầu cố gắng tìm ra nó. - Kip
Bên cạnh đó ngắn gọn hơn đây là một cách giải pháp nhanh hơn. Truy cập INFORMATION_SCHEMA lượt xem hoặc sys.columns truy cập đĩa, trong khi COL_LENGTH sử dụng siêu dữ liệu cơ sở dữ liệu được lưu trữ. - wqw
Đây có lẽ không phải là câu trả lời được đánh giá cao nhất bởi vì nó được đưa ra 2,5 năm sau câu trả lời khác. Đó là lý do tại sao tôi luôn kiểm tra ngày khi so sánh xếp hạng trên hai câu trả lời. Phải mất nhiều thời gian hơn để vượt qua câu trả lời đã được đưa ra sớm hơn nhiều. ;) - Sean


Tinh chỉnh bên dưới để phù hợp với yêu cầu cụ thể của bạn:

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

Chỉnh sửa để xử lý chỉnh sửa cho câu hỏi: Điều đó sẽ hoạt động - hãy xem kỹ mã của bạn vì những sai lầm ngu ngốc; bạn đang truy vấn INFORMATION_SCHEMA trên cùng một cơ sở dữ liệu như chèn của bạn đang được áp dụng cho ví dụ? Bạn có lỗi đánh máy trong tên bảng / cột trong câu lệnh nào không?


125
2017-09-25 12:35



Tôi chỉ phát hiện ra rằng thêm TABLE_SCHEMA = 'mySchema' sau khi mệnh đề sửa chữa vấn đề. - Maciej
-1: không trả lời câu hỏi của OP, chỉ thêm các thông tin mới về cách thêm một collumn mới mặc dù OP không hỏi về điều đó cả, không giải quyết bình luận của OP. - ANeves


Thử cái này...

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



Phương pháp này cũng làm việc với SQL CE, trong khi một số phương pháp khác được đề cập thì không. - Sandra Walters
Bạn có thể dùng SELECT 1 thay vì SELECT TOP 1 1 ;). - shA.t
Trong một EXISTS câu lệnh SQL tự động tối ưu hóa các cột đi (giống như count(*)) vì thế SELECT * sẽ đủ. - Marc L.


Tôi thích INFORMATION_SCHEMA.COLUMNS trên một bảng hệ thống vì Microsoft không đảm bảo bảo toàn các bảng hệ thống giữa các phiên bản. Ví dụ, dbo.syscolumns vẫn hoạt động trong SQL 2008, nhưng nó không được chấp nhận và có thể bị xóa bất kỳ lúc nào trong tương lai.


43
2018-06-26 08:58



"Chế độ xem INFORMATION_SCHEMA có thể chưa hoàn chỉnh vì chúng không được cập nhật cho tất cả các tính năng mới" - onedaywhen
Vâng vâng, điều đó không cần phải nói từ INFORMATION_SCHEMA chế độ xem chỉ chứa siêu dữ liệu chuẩn ANSI. Tuy nhiên, đó là đủ cho một thử nghiệm tồn tại. - Christian Hayter
Microsoft nói "Trong các phiên bản tương lai của SQL Server, Microsoft có thể tăng thêm định nghĩa của bất kỳ khung nhìn danh mục hệ thống nào bằng cách thêm các cột vào cuối danh sách cột. Chúng tôi khuyên bạn nên sử dụng cú pháp SELECT * FROM sys.catalog_view_name trong mã sản xuất vì số lượng các cột được trả lại có thể thay đổi và phá vỡ ứng dụng của bạn. " Điều đó ngụ ý rằng họ sẽ không loại bỏ các cột hoặc thay đổi thứ tự của chúng. Đó là đủ khả năng tương thích ngược cho tất cả các trường hợp cạnh nhưng. - siride


Bạn có thể sử dụng các khung nhìn hệ thống lược đồ thông tin để tìm ra khá nhiều thứ về các bảng mà bạn quan tâm:

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

Bạn cũng có thể thẩm vấn các khung nhìn, các thủ tục lưu sẵn và khá nhiều thứ về cơ sở dữ liệu bằng cách sử dụng khung nhìn Information_schema.


38
2017-09-25 12:37





Trước tiên hãy kiểm tra xem table/column(id/name) kết hợp tồn tại trong dbo.syscolumns (một bảng SQL Server nội bộ có chứa các định nghĩa trường) và nếu không phát hành ALTER TABLE truy vấn để thêm nó. Ví dụ:

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





Hãy thử một cái gì đó như:

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

Sau đó sử dụng nó như thế này:

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

Nó sẽ làm việc trên cả SQL Server 2000 & SQL Server 2005. Không chắc chắn về SQL Server 2008, nhưng không thấy tại sao không.


26
2018-05-01 04:46





Đối với những người đang kiểm tra sự tồn tại của cột để thả nó.

Trong SQL Server 2016 bạn có thể sử dụng các câu lệnh DIE mới thay vì IF giấy gói

ALTER TABLE Table_name DROP COLUMN IF EXISTS Column_name

24
2018-03-03 15:49





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'
    +'    ['+@myColumn+'] bigint       null')
end

22
2018-03-20 22:27





Một người bạn tốt và đồng nghiệp của tôi đã chỉ cho tôi cách bạn cũng có thể sử dụng IF chặn với các hàm SQL OBJECT_ID và COLUMNPROPERTY trong SQL SERVER 2005+ để kiểm tra cột. Bạn có thể sử dụng một cái gì đó tương tự như sau:

Bạn có thể thấy cho chính mình ở đây

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

21
2017-08-02 19:54



Và tất nhiên, nếu bạn tự tin rằng bảng tồn tại, bạn có thể bỏ qua phần đầu tiên của điều kiện và kiểm tra COLUMNPROPERTY chỉ có. - Ruud Helderman