SQL query to get column name and datatype in which the tables are located in two server's databases
I have two tables tableA
in databaseA
on ServerA
and tableB
in databaseB
on ServerB
.
I just want to get the column names and datatypes that are same in two tables A & B
. I need a SQL query using sysobjects
and syscolumns
(it should not use information schema). I am so confused to write the query for it. Now I am using information schema on this query.
I am using following query to get column name and datatype. Please give another query for the same purpose using sysobjects
and syscolumns
(it should not use information schema)
I am using SQL Server 2005.
SELECT
t1.column_name, t2.column_name, t开发者_JAVA技巧1.data_type, t2.data_type
FROM
(select * from serverA.databaseA.information_schema.COLUMNS
WHERE table_name ='tableA') as t1
full outer join
(select * from severB.databaseB.information_schema.COLUMNS
WHERE table_name ='tableA') as t2 ON t1.column_name=t2.column_name;
You can replace :
select * from serverA.databaseA.information_schema.COLUMNS WHERE table_name ='tableA'
By :
select serverA.syscolumns.name AS column_name, serverA.syscolumns.length AS column_length, serverA.systypes.name AS data_type
from serverA.syscolumns, serverA.systypes, serverA.sysobjects
where sysobjects.id = syscolumns.id
and 'TableA' = sysobjects.name
and syscolumns.type = systypes.type
精彩评论