SQL比较两表字段和字段类型

一、问题

业务需要把TB_Delete_KYSubProject表数据恢复到TB_KYSubProject，但提示错误，错误原因是两表字段类型存在不一致

 

insert into [TB_KYSubProject] SELECT * from [TB_Delete_KYSubProject]   
WHERE   [TB_Delete_KYSubProject].id = 'A49CFC7B-8F9D-476F-B853-CA62C18E2D03'

 

二、方法

一个个字段比对很麻烦，所以用以下sql 查询出两表字段不一致

可以用查询表的字段信息

SELECT  OBJECT_NAME(c.object_id) AS TableName ,
        c.name AS ColumnsName ,
        t.name AS ColumnType ,
        c.max_length AS Length
FROM    sys.columns c
        INNER JOIN systypes t ON c.system_type_id = t.xtype
WHERE   c.object_id = OBJECT_ID('TB_Delete_KYSubProject')
        AND T.NAME <> 'sysname'

 

SELECT  c.name AS ColumnsName ,
        t.name AS ColumnType ,
        c.max_length AS Length
FROM    sys.columns c
        INNER JOIN systypes t ON c.system_type_id = t.xtype
WHERE   c.object_id = OBJECT_ID('TB_Delete_KYSubProject')
        AND T.NAME <> 'sysname'
EXCEPT
SELECT  c.name AS ColumnsName ,
        t.name AS ColumnType ,
        c.max_length AS Length
FROM    sys.columns c
        INNER JOIN systypes t ON c.system_type_id = t.xtype
WHERE   c.object_id = OBJECT_ID('TB_KYSubProject')
        AND T.NAME <> 'sysname'

 

 

 得到两个表具体哪里不一致

SELECT  OBJECT_NAME(c.object_id) AS TableName ,
        c.name AS ColumnsName ,
        t.name AS ColumnType ,
        c.max_length AS Length
FROM    sys.columns c
        INNER JOIN systypes t ON c.system_type_id = t.xtype
WHERE   c.object_id = OBJECT_ID('TB_Delete_KYSubProject')
        AND T.NAME <> 'sysname'
        AND c.name IN ( 'FrequencyCount', 'IsDelete', 'PortNumber' )

SELECT  OBJECT_NAME(c.object_id) AS TableName ,
        c.name AS ColumnsName ,
        t.name AS ColumnType ,
        c.max_length AS Length
FROM    sys.columns c
        INNER JOIN systypes t ON c.system_type_id = t.xtype
WHERE   c.object_id = OBJECT_ID('TB_KYSubProject')
        AND T.NAME <> 'sysname'
        AND c.name IN ( 'FrequencyCount', 'IsDelete', 'PortNumber' )

 

 

 

扩展：

获取表字段说明

SELECT  c.name AS ColumnsName ,
        t.name AS ColumnType ,
        c.max_length AS Length ,
        CAST(ep.[value] AS VARCHAR(100)) AS [字段说明]
FROM    sys.columns c
        INNER JOIN systypes t ON c.system_type_id = t.xtype
        LEFT JOIN sys.extended_properties AS ep ON ep.major_id = c.object_id
                                                   AND ep.minor_id = c.column_id
WHERE   c.object_id = OBJECT_ID('TB_Delete_SJSubProject')
        AND T.NAME <> 'sysname'
        AND ep.class = 1