--試試以下: select table_name from information_schema.columns where column_name = '材料名称' INTERSECT select table_name from information_schema.columns where column_name = '材料编码'
with t as (select a.name 'TabName',b.name 'ColName' from sys.tables a inner join sys.columns b on a.object_id=b.object_id where b.name in('材料名称','材料编码') ) select * from t a where exists(select 1 from t b where b.TabName=a.TabName and a.ColName<>b.ColName) order by a.TabName
是啊 的确是SQL2000啊 请问SQL2000应该怎么写啊
SELECT * FROM ( SELECT a.name 'TabName' , b.name 'ColName' FROM sys.tables a INNER JOIN sys.columns b ON a.object_id = b.object_id WHERE b.name IN ( '材料名称', '材料编码' ) ) a WHERE EXISTS ( SELECT 1 FROM ( SELECT a.name 'TabName' , b.name 'ColName' FROM sys.tables a INNER JOIN sys.columns b ON a.object_id = b.object_id WHERE b.name IN ( '材料名称', '材料编码' ) ) b WHERE b.TabName = a.TabName AND a.ColName <> b.ColName ) ORDER BY a.TabName
我不知道2005的sys.columns对应2000的哪个表,手上没环境,SELECT * FROM ( SELECT a.name 'TabName' , b.name 'ColName' FROM sys.sysobjects a INNER JOIN sys.columns b ON a.object_id = b.object_id WHERE a.type='u' AND b.name IN ( '材料名称', '材料编码' ) ) a WHERE EXISTS ( SELECT 1 FROM ( SELECT a.name 'TabName' , b.name 'ColName' FROM sys.sysobjects a INNER JOIN sys.columns b ON a.object_id = b.object_id WHERE a.type='u' AND b.name IN ( '材料名称', '材料编码' ) ) b WHERE b.TabName = a.TabName AND a.ColName <> b.ColName ) ORDER BY a.TabName
--試試以下:
select table_name from information_schema.columns where column_name = '材料名称'
INTERSECT
select table_name from information_schema.columns where column_name = '材料编码'
with t as
(select a.name 'TabName',b.name 'ColName'
from sys.tables a
inner join sys.columns b on a.object_id=b.object_id
where b.name in('材料名称','材料编码')
)
select *
from t a
where exists(select 1 from t b
where b.TabName=a.TabName and a.ColName<>b.ColName)
order by a.TabName
FROM ( SELECT a.name 'TabName' ,
b.name 'ColName'
FROM sys.tables a
INNER JOIN sys.columns b ON a.object_id = b.object_id
WHERE b.name IN ( '材料名称', '材料编码' )
) a
WHERE EXISTS ( SELECT 1
FROM ( SELECT a.name 'TabName' ,
b.name 'ColName'
FROM sys.tables a
INNER JOIN sys.columns b ON a.object_id = b.object_id
WHERE b.name IN ( '材料名称', '材料编码' )
) b
WHERE b.TabName = a.TabName
AND a.ColName <> b.ColName )
ORDER BY a.TabName
对象名 'sys.tables' 无效。
服务器: 消息 208,级别 16,状态 1,行 1
对象名 'sys.columns' 无效。
服务器: 消息 208,级别 16,状态 1,行 1
对象名 'sys.tables' 无效。
服务器: 消息 208,级别 16,状态 1,行 1
对象名 'sys.columns' 无效。
FROM ( SELECT a.name 'TabName' ,
b.name 'ColName'
FROM sys.sysobjects a
INNER JOIN sys.columns b ON a.object_id = b.object_id
WHERE a.type='u' AND b.name IN ( '材料名称', '材料编码' )
) a
WHERE EXISTS ( SELECT 1
FROM ( SELECT a.name 'TabName' ,
b.name 'ColName'
FROM sys.sysobjects a
INNER JOIN sys.columns b ON a.object_id = b.object_id
WHERE a.type='u' AND b.name IN ( '材料名称', '材料编码' )
) b
WHERE b.TabName = a.TabName
AND a.ColName <> b.ColName )
ORDER BY a.TabName