sql server 列信息查詢

VeryHappyAG 2019-02-13

展開全文

在sqlserver中,常用的系統(tǒng)表有sysobjects,syscolumns, 這兩個可以判斷表是否存在,字段是否存在,可以滿足一些需求的查詢.但項目有需求,需要對比兩張表的字段類型和長度是否相等,主要針對varchar,nvarchar類型的字段.這個時候就需要使用到另外一個視圖 information_schema.columns . 該視圖記錄著表的列的詳細信息.

以下為查詢PROPOSAL表的列名相關信息

select  t2.TABLE_NAME,t2.COLUMN_NAME,t2.data_type,t2.character_maximum_length,
* FROM information_schema.columns t2
where table_name='proposal'

截圖如下圖
這里寫圖片描述

兩表對比查詢(proposal_history,proposal)

SELECT a.TABLE_NAME AS 表1,b.TABLE_NAME AS 表2,a.COLUMN_NAME AS 列名,
        a.DATA_TYPE AS 表1字段類型,b.DATA_TYPE AS 表2字段類型,
        a.CHARACTER_MAXIMUM_LENGTH AS 表1字段長度,
        b.CHARACTER_MAXIMUM_LENGTH AS 表2字段長度 
FROM 
    (select  t1.TABLE_NAME,t1.COLUMN_NAME,t1.data_type,ISNULL(t1.character_maximum_length,-1) AS CHARACTER_MAXIMUM_LENGTH
    from information_schema.columns t1
    where table_name='proposal_history')A
LEFT JOIN 
    (select  t2.TABLE_NAME,t2.COLUMN_NAME,t2.data_type,ISNULL(t2.character_maximum_length,-1) AS CHARACTER_MAXIMUM_LENGTH
    from information_schema.columns t2
    where table_name='proposal')B

ON a.COLUMN_NAME = b.COLUMN_NAME
WHERE 
(b.COLUMN_NAME IS NULL OR a.DATA_TYPE != b.DATA_TYPE OR a.CHARACTER_MAXIMUM_LENGTH ! = b.CHARACTER_MAXIMUM_LENGTH);

查詢結果
這里寫圖片描述