求查找语句:

查询出,一个库中所有表的列,并把此列对应的表列出

如:
user表中有id,mark
info表中有id,name,sex

查询结果:
column table
id user
mark user
id info
name info
sex info

求助!!!

用user表和info表连接可行吗?

不知道SQL2000支持sys.columns吗

我用SQL2005可以用

有高手,利用这个实现上面的效果吗?

use databaseName
select [name] from syscolumns where id in (object_id('user'),object_id('info'))

[CODE]use Northwind
select colname=a.name,tablename=b.name from syscolumns a,sysobjects b where a.id=b.id and b.type='U'[/CODE]

SQL Server2000测试通过.

还是不行好象,一个库中理论上是有N张表的,而不止两个

实现的效果不一样???

其实我想要的是,把一个库中的所有列抽去出来,和列所在的表一一对应,方便核对数据.

但是我实现了半天无法实现!
请求帮忙!!!谢谢

五楼代码效果如下.
[CODE]CustomerID Orders
EmployeeID Orders
Freight Orders
OrderDate Orders
OrderID Orders
RequiredDate Orders
ShipAddress Orders
ShipCity Orders
ShipCountry Orders
ShipName Orders
ShippedDate Orders
ShipPostalCode Orders
ShipRegion Orders
ShipVia Orders
CategoryID Products
Discontinued Products
ProductID Products
ProductName Products
QuantityPerUnit Products
ReorderLevel Products
SupplierID Products
UnitPrice Products
UnitsInStock Products
UnitsOnOrder Products
Discount Order Details
OrderID Order Details
ProductID Order Details
Quantity Order Details
UnitPrice Order Details
CustomerID CustomerCustomerDemo
CustomerTypeID CustomerCustomerDemo
CustomerDesc CustomerDemographics
CustomerTypeID CustomerDemographics
RegionDescription Region
RegionID Region
RegionID Territories
TerritoryDescription Territories
TerritoryID Territories
EmployeeID EmployeeTerritories
TerritoryID EmployeeTerritories
CategoryID t1
Discontinued t1
ProductID t1
ProductName t1
QuantityPerUnit t1
ReorderLevel t1
SupplierID t1
UnitPrice t1
UnitsInStock t1
UnitsOnOrder t1
CategoryID t2
Discontinued t2
ProductID t2
ProductName t2
QuantityPerUnit t2
ReorderLevel t2
SupplierID t2
UnitPrice t2
UnitsInStock t2
UnitsOnOrder t2
employeeid employee_Results
firstname employee_Results
lastname employee_Results
employeeid Employees_Result
firstname Employees_Result
lastname Employees_Result
Address Employees
BirthDate Employees
City Employees
Country Employees
EmployeeID Employees
Extension Employees
FirstName Employees
HireDate Employees
HomePhone Employees
LastName Employees
Notes Employees
Photo Employees
PhotoPath Employees
PostalCode Employees
Region Employees
ReportsTo Employees
Title Employees
TitleOfCourtesy Employees
CategoryID Categories
CategoryName Categories
Description Categories
Picture Categories
Address Customers
City Customers
CompanyName Customers
ContactName Customers
ContactTitle Customers
Country Customers
CustomerID Customers
Fax Customers
Phone Customers
PostalCode Customers
Region Customers
CompanyName Shippers
Phone Shippers
ShipperID Shippers
Address Suppliers
City Suppliers
CompanyName Suppliers
ContactName Suppliers
ContactTitle Suppliers
Country Suppliers
Fax Suppliers
HomePage Suppliers
Phone Suppliers
PostalCode Suppliers
Region Suppliers
SupplierID Suppliers[/CODE]

是你一楼所说的效果..

select a.name as fields,b.name as tables from syscolumns a left join sysobjects b on a.id=b.id
這個是查詢所有表及字段，
如果是要查用戶創建的表就要用下面的
select a.name as fields,b.name as tables from syscolumns a left join sysobjects b on a.id=b.id where b.xtype='u'

谢谢帮助,问题已经解决了,楼上的语法都是2000吧!谢谢各位