要比较数据类型相同，请指定两个不同表的字段名称 [英] To compare data type the same specify field name of two different table

问题描述

表1的结构:-
--------------------
表_1_ID bigint，
AreaID bigint，
姓名Varchar(50)
等.....

表2的结构:-
--------------------
表_2_ID bigint，
AreaID bigint，
姓名Varchar(50)
等.....

我想这样做，如何通过SQL SERVER 2000中的T-Sql查询指定相同的字段名称(即AreaID)来比较两个表的数据类型相同?

请帮助我解决上述问题....

解决方案

看看是否有帮助...

 声明  @ datatype1   varchar ( 50 )
声明  @ datatype2   varchar ( 50 )

- -获取Firstfield的数据类型
选择  @ datatype1  = DATA_TYPE
 FROM  information_schema.columns
位置 table_schema = '  dbo'
 AND  table_name = '  Table_1'
 AND  column_name = '  AreaID'；

- -获取Secondfield的数据类型
选择  @ datatype2  = DATA_TYPE
 FROM  information_schema.columns
位置 table_schema = '  dbo'
 AND  table_name = '  Table_2'
 AND  column_name = '  AreaID'；

选择 '  true' 其中  @ datatype1  =  @ datatype2  

为了简化操作，请考虑在表中添加一个额外的列，这将有助于对数据进行分类.这样，您就不需要有两个单独的表.

但是同样，如果您发布的场景是(近)真实的，则适用:)

Structure of Table-1:-
--------------------
Table_1_ID bigint,
AreaID bigint,
Name Varchar(50)
etc.....

Structure of Table-2:-
--------------------
Table_2_ID bigint,
AreaID bigint,
Name Varchar(50)
etc.....

I want to do that, how will i compare the data type is same of two tables by specifying same field name(i.e. AreaID) through T- Sql Query in SQL SERVER 2000??

Please help me the above problem....

解决方案

See if this helps...

declare @datatype1 varchar(50)
declare @datatype2 varchar(50)

--- Get the datatype of Firstfield
SELECT @datatype1 = DATA_TYPE
FROM information_schema.columns
WHERE table_schema = 'dbo'
AND table_name = 'Table_1'
AND column_name = 'AreaID' ;

--- Get the datatype of Secondfield
SELECT @datatype2 = DATA_TYPE
FROM information_schema.columns
WHERE table_schema = 'dbo'
AND table_name = 'Table_2'
AND column_name = 'AreaID' ;

select 'true' where  @datatype1 = @datatype2

While the answer from JimJos is very good, I think that if this is a real scenario, you should rethink the design of the database: Why do you want to store the same data in separate table.

In order to keep things simple, consider adding an extra column to the table which would help to categorize the data. This way you wouldn''t need to have two separate tables.

But again, this applies if the scenario you posted is (near) real :)

这篇关于要比较数据类型相同，请指定两个不同表的字段名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！