CFSDN nhấn mạnh vào giá trị tạo ra nguồn mở và chúng tôi cam kết xây dựng nền tảng chia sẻ tài nguyên để mọi nhân viên CNTT có thể tìm thấy thế giới tuyệt vời của bạn tại đây.

Bài viết trên blog CFSDN này thảo luận về cách SQL sử dụng chế độ xem hệ thống INFORMATION_SCHema để lấy thông tin khóa chính và khóa ngoài của bảng. Nó được tác giả sưu tầm và biên soạn. Nếu bạn quan tâm đến bài viết này, hãy nhớ thích nó.

Sau đó, bài viết trước "Phân tích truy vấn thông tin cấu trúc bảng SQL bao gồm khóa chính và khóa ngoài, tăng tự động" đã đề cập đến chế độ xem INFORMATION_SCHEMA Trên thực tế, trong SQL 2005, Microsoft chủ yếu khuyến nghị mọi người sử dụng chế độ xem hệ thống INFORMATION_SCHEMA thay vì sử dụng nội dung sys. Tất nhiên, vẫn còn rất nhiều thông tin chỉ có thể được truy vấn thông qua chế độ xem hệ thống. Ở đây chúng tôi vẫn lấy thông tin kết quả của bảng truy vấn làm ví dụ để minh họa việc sử dụng một số khung nhìn INFORMATION_SCHEMA chính. Trước tiên, chúng ta cần truy vấn thông tin cột, yêu cầu sử dụng chế độ xem hệ thống [INFORMATION_SCHEMA].[COLUMNS] để truy vấn thông tin cột dữ liệu. SQL như sau:

Sao chép mã mã như sau

CHỌN c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE, c.CHARACTER_MAXIMUM_LENGTH, c.COLUMN_DEFAULT, c.IS_NULLABLE, c.NUMERIC_PRECISION, c.NUMERIC_SCALE TỪ [INFORMATION_SCHEMA].[COLUMNS] c NƠI TABLE_NAME = 'Địa chỉ' 。

Kết quả chạy như sau: Tất cả chúng ta đều biết rằng khi xác định các cột, chúng ta thường sử dụng thông tin như varchar(50). Ở đây, chúng ta cần tích hợp thông tin DATA_TYPE và CHARACTER_MAXIMUM_LENGTH Khi CHARACTER_MAXIMUM_LENGTH là -1, ngay cả khi độ dài tối đa cụ thể. không được chỉ định, Thông tin độ dài được chỉ định của dữ liệu là tối đa và số cần tích hợp thông tin NUMERIC_PRECISION và NUMERIC_SCALE. SQL được sửa đổi như sau:

Sao chép mã mã như sau

CHỌN c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, TRƯỜNG HỢP KHI ( ( CHARINDEX('char', c.DATA_TYPE) > 0 HOẶC CHARINDEX('binary', c.DATA_TYPE) > 0 ) VÀ c.CHARACTER_MAXIMUM_LENGTH <> -1 ) SAU ĐÓ c.DATA_TYPE + '(' + CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(4)) + ')' KHI ( ( CHARINDEX('CHAR', c.DATA_TYPE) > 0 HOẶC CHARINDEX('binary', c.DATA_TYPE) > 0 ) VÀ c.CHARACTER_MAXIMUM_LENGTH = -1 ) SAU ĐÓ c.DATA_TYPE + '(max)' KHI ( CHARINDEX('numeric', c.DATA_TYPE) > 0 ) SAU ĐÓ c.DATA_TYPE + '(' + CAST(c.NUMERIC_PRECISION AS VARCHAR(4)) + ',' + CAST(c.NUMERIC_SCALE AS VARCHAR(4)) + ')' KHÁC c.DATA_TYPE END AS DATA_TYPE , c.COLUMN_DEFAULT , c.IS_NULLABLE , c.COLUMN_DEFAULT FROM [INFORMATION_SCHEMA].[COLUMNS] c WHERE TABLE_NAME = 'Địa chỉ' SẮP XẾP THEO c.ORDINAL_POSITION 。

Kết quả chạy như hình: Bây giờ chúng ta cần đánh dấu cột nào của bảng này là khóa chính và cột nào là khóa ngoại. Để truy vấn thông tin khóa chính và khóa ngoại của bảng, chúng ta cần sử dụng [INFORMATION_SCHema]. [TABLE_CONSTRAINTS] và [INFORMATION_SCHema].[KEY_COLUMN_USAGE] ] Kết quả chạy chế độ xem hệ thống như trong hình: Sửa đổi câu lệnh SQL trước đó của chúng tôi:

Sao chép mã mã như sau

CHỌN c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, TRƯỜNG HỢP KHI ( ( CHARINDEX('char', c.DATA_TYPE) > 0 HOẶC CHARINDEX('binary', c.DATA_TYPE) > 0 ) VÀ c.CHARACTER_MAXIMUM_LENGTH <> -1 ) SAU ĐÓ c.DATA_TYPE + '(' + CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(4)) + ')' KHI ( ( CHARINDEX('CHAR', c.DATA_TYPE) > 0 HOẶC CHARINDEX('binary', c.DATA_TYPE) > 0 ) VÀ c.CHARACTER_MAXIMUM_LENGTH = -1 ) SAU ĐÓ c.DATA_TYPE + '(max)' KHI ( CHARINDEX('numeric', c.DATA_TYPE) > 0 ) SAU ĐÓ c.DATA_TYPE + '(' + CAST(c.NUMERIC_PRECISION AS VARCHAR(4)) + ',' + CAST(c.NUMERIC_SCALE AS VARCHAR(4)) + ')' KHÁC c.DATA_TYPE KẾT THÚC DƯỚI DẠNG DỮ LIỆU, c.COLUMN_DEFAULT, c.IS_NULLABLE, c.COLUMN_DEFAULT, TRƯỜNG HỢP KHI tc.CONSTRAINT_TYPE = 'KHÓA CHÍNH' THÌ 'CÓ' KHÁC 'KHÔNG' KẾT THÚC DƯỚI DẠNG IS_PRIMARY_KEY, TRƯỜNG HỢP KHI tc.CONSTRAINT_TYPE = 'KHÓA NGOẠI' THÌ 'CÓ' KHÁC 'KHÔNG' KẾT THÚC DƯỚI DẠNG IS_FOREIGN_KEY TỪ [SCHEMA_THÔNG TIN].[CỘT] c NỐI TRÁI [SCHEMA_THÔNG TIN].[SỬ DỤNG CỘT CHÌA KHÓA] kcu TRÊN kcu.TABLE_SCHEMA = c.TABLE_SCHEMA VÀ kcu.TABLE_NAME = c.TABLE_NAME VÀ kcu.COLUMN_NAME = c.COLUMN_NAME NỐI TRÁI [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] tc TRÊN tc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA VÀ tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME NƠI c.TABLE_NAME = 'Địa chỉ' SẮP XẾP THEO c.ORDINAL_POSITION 。

Kết quả chạy như trong hình: Bây giờ chúng ta đã biết cột nào là khóa chính và cột nào là khóa ngoại. Bước tiếp theo là thông tin bảng kiểm tra bên ngoài được liên kết với các cột khóa ngoại. .[REFERENTIAL_CONSTRAINTS] chế độ xem hệ thống để chạy chế độ xem này như sau: Vì vậy, hãy sửa đổi SQL của chúng tôi như sau:

Sao chép mã mã như sau

CHỌN c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, TRƯỜNG HỢP KHI ( ( CHARINDEX('char', c.DATA_TYPE) > 0 HOẶC CHARINDEX('binary', c.DATA_TYPE) > 0 ) VÀ c.CHARACTER_MAXIMUM_LENGTH <> -1 ) SAU ĐÓ c.DATA_TYPE + '(' + CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(4)) + ')' KHI ( ( CHARINDEX('CHAR', c.DATA_TYPE) > 0 HOẶC CHARINDEX('binary', c.DATA_TYPE) > 0 ) VÀ c.CHARACTER_MAXIMUM_LENGTH = -1 ) SAU ĐÓ c.DATA_TYPE + '(max)' KHI ( CHARINDEX('numeric', c.DATA_TYPE) > 0 ) SAU ĐÓ c.DATA_TYPE + '(' + CAST(c.NUMERIC_PRECISION AS VARCHAR(4)) + ',' + CAST(c.NUMERIC_SCALE AS VARCHAR(4)) + ')' KHÁC c.DATA_TYPE KẾT THÚC DƯỚI DẠNG DỮ LIỆU, c.COLUMN_DEFAULT, c.IS_NULLABLE, c.COLUMN_DEFAULT, TRƯỜNG HỢP KHI tc.CONSTRAINT_TYPE = 'PRIMARY KEY' THÌ 'CÓ' KHÁC 'KHÔNG' KẾT THÚC DƯỚI DẠNG IS_PRIMARY_KEY, TRƯỜNG HỢP KHI tc.CONSTRAINT_TYPE = 'FOREIGN KEY' THÌ 'CÓ' KHÁC 'KHÔNG' KẾT THÚC DƯỚI DẠNG IS_FOREIGN KEY, fkcu.COLUMN_NAME LÀ FOREIGN KEY, fkcu.TABLE_NAME LÀ FOREIGN TABLE TỪ [INFORMATION_SCHEMA].[COLUMNS] c LEFT JOIN [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] kcu TRÊN kcu.TABLE_SCHEMA = c.TABLE_SCHEMA VÀ kcu.TABLE_NAME = c.TABLE_NAME VÀ kcu.COLUMN_NAME = c.COLUMN_NAME NỐI TRÁI [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] tc TRÊN tc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA VÀ tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME NỐI TRÁI [INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS] fc TRÊN kcu.CONSTRAINT_SCHEMA = fc.CONSTRAINT_SCHEMA VÀ kcu.CONSTRAINT_NAME = fc.CONSTRAINT_NAME NỐI TRÁI [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] fkcu TRÊN fkcu.CONSTRAINT_SCHEMA = fc.UNIQUE_CONSTRAINT_SCHEMA VÀ fkcu.CONSTRAINT_NAME = fc.UNIQUE_CONSTRAINT_NAME Ở ĐÂU c.TABLE_NAME = 'Địa chỉ' SẮP XẾP THEO c.ORDINAL_POSITION 。

Các kết quả đang chạy như trong hình: Quá nhiều NULL được hiển thị trong kết quả truy vấn của chúng tôi, điều này không thoải mái lắm khi xem xét. Ngoài ra, tên bảng của chúng tôi sẽ được hiển thị một lần. Sửa đổi SQL như sau:

Sao chép mã mã như sau

CHỌN TRƯỜNG HỢP KHI c.ORDINAL_POSITION = 1 THÌ c.TABLE_SCHEMA + '.' + c.TABLE_NAME ELSE '' KẾT THÚC NHƯ TABLE_NAME, c.COLUMN_NAME, CASE KHI ( ( CHARINDEX('char', c.DATA_TYPE) > 0 HOẶC CHARINDEX('binary', c.DATA_TYPE) > 0 ) VÀ c.CHARACTER_MAXIMUM_LENGTH <> -1 ) THÌ c.DATA_TYPE + '(' + CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(4)) + ')' KHI ( ( CHARINDEX('CHAR', c.DATA_TYPE) > 0 HOẶC CHARINDEX('binary', c.DATA_TYPE) > 0 ) VÀ c.CHARACTER_MAXIMUM_LENGTH = -1 ) THÌ c.DATA_TYPE + '(max)' KHI ( CHARINDEX('numeric', c.DATA_TYPE) > 0 ) THÌ c.DATA_TYPE + '(' + CAST(c.NUMERIC_PRECISION AS VARCHAR(4)) + ',' + CAST(c.NUMERIC_SCALE AS VARCHAR(4)) + ')' KHÁC c.DATA_TYPE KẾT THÚC DƯỚI DẠNG DỮ LIỆU, ISNULL(c.COLUMN_DEFAULT, '') DƯỚI DẠNG CỘT, TRƯỜNG HỢP KHI c.IS_NULLABLE = 'CÓ' THÌ '√' KHÁC '' KẾT THÚC DƯỚI DẠNG IS_NULLABLE, TRƯỜNG HỢP KHI tc.CONSTRAINT_TYPE = 'KHÓA CHÍNH' THÌ '√' KHÁC '' KẾT THÚC DƯỚI DẠNG IS_PRIMARY_KEY, TRƯỜNG HỢP KHI tc.CONSTRAINT_TYPE = 'KHÓA NGOẠI' THÌ '√' KHÁC '' KẾT THÚC DƯỚI DẠNG IS_FOREIGN_KEY, ISNULL(fkcu.COLUMN_NAME, '') DƯỚI DẠNG FOREIGN_KEY, ISNULL(fkcu.TABLE_NAME, '') LÀ BẢNG NGOÀI TỪ [SCHEMA_THÔNG TIN].[CỘT] c LIÊN KẾT TRÁI [SCHEMA_THÔNG TIN].[KHÓA_CỘT_SỬ DỤNG] kcu TRÊN kcu.TABLE_SCHEMA = c.SCHEMA VÀ kcu.TABLE_NAME = c.SCHEMA VÀ kcu.COLUMN_NAME = c.SỔ LIÊN KẾT TRÁI [SCHEMA_THÔNG TIN].[TABLE_CONSTRAINTS] tc TRÊN tc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA VÀ tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME LIÊN KẾT TRÁI [SCHEMA_THÔNG TIN].[CÁC_CONSTRAINT_Tham_SỬ] fc TRÊN kcu.CONSTRAINT_SCHEMA = fc.CONSTRAINT_SCHEMA VÀ kcu.CONSTRAINT_NAME = fc.CONSTRAINT_NAME LEFT JOIN [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] fkcu TRÊN fkcu.CONSTRAINT_SCHEMA = fc.UNIQUE_CONSTRAINT_SCHEMA VÀ fkcu.CONSTRAINT_NAME = fc.UNIQUE_CONSTRAINT_NAME NƠI c.TABLE_NAME = 'Địa chỉ' SẮP XẾP THEO c.ORDINAL_POSITION 。

Kết quả chạy như hình: Nếu có gì sai sót các bạn góp ý nhé! .

Cuối cùng, bài viết này thảo luận về cách SQL sử dụng chế độ xem hệ thống INFORMATION_SCHEMA để lấy thông tin khóa chính và khóa ngoài của bảng. vui lòng Tìm kiếm các bài viết của CFSDN hoặc tiếp tục duyệt các bài viết liên quan. Tôi hy vọng bạn sẽ ủng hộ blog của tôi trong tương lai! .