开发者

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜