SQL Server：比较两个表中的列 [英] SQL Server: compare columns in two tables

问题描述

我最近完成了从某个应用程序的旧版本迁移到当前版本，并且在迁移数据库时遇到了一些问题。

I've recently done a migration from a really old version of some application to the current version and i faced some problems while migrating databases.

我需要一个查询这可以帮助我比较两个表中的列。我的意思是不是行中的数据，我需要比较列本身来找出，我错过了表结构的什么变化。

I need a query that could help me to compare columns in two tables. I mean not the data in rows, I need to compare the columns itself to figure out, what changes in table structure I've missed.

推荐答案

查看红门SQL比较

否则这里是一个开始（对于sql server）

Otherwise here is a start (for sql server)

select 
 so.name as [table],
 sc.name as [column],
 sc.type, sc.length, sc.prec, sc.scale, sc.collation
from 
 sysobjects so
 inner join syscolumns sc ON so.id = sc.id

where so.type='u'

order by so.name, sc.colorder

您可以查看

 - INFORMATION_SCHEMA.TABLES
 - INFORMATION_SCHEMA.COLUMNS
 - INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
 - INFORMATION_SCHEMA.TABLE_CONSTRAINTS
 - INFORMATION_SCHEMA.KEY_COLUMN_USAGE

表格。

[update]

[update]

使用INFORMATION_SCHEMA表格

Using the INFORMATION_SCHEMA tables

SELECT
 [table].TABLE_NAME AS [Table_Name],
 [column].COLUMN_NAME AS [Column_Name],
 COLUMNPROPERTY(object_id([table].[TABLE_NAME]), [column].[COLUMN_NAME], 'IsIdentity') AS [identity],
 [column].DATA_TYPE AS [datatype],
 [column].CHARACTER_MAXIMUM_LENGTH AS [Character_Length],
 [column].NUMERIC_PRECISION AS Numeric_precision,
 [column].ORDINAL_POSITION AS [order],
 [column].COLUMN_DEFAULT AS [defaultvalue],
 [column].IS_NULLABLE AS [nullable]
FROM 
 INFORMATION_SCHEMA.TABLES [table] INNER JOIN 
 INFORMATION_SCHEMA.COLUMNS [column] ON [table].TABLE_NAME = [column].TABLE_NAME
WHERE
 [table].TABLE_TYPE = 'BASE TABLE'
 AND [table].TABLE_NAME <> 'sysdiagrams'
ORDER BY 
 [table].TABLE_NAME ASC, 
 [column].ORDINAL_POSITION ASC

这篇关于SQL Server：比较两个表中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！