問題描述
如何顯示所有用戶表中的所有列/字段？ (How to display all columns/fields in all user tables?)
Running sybase. Trying to see all columns for all my tables with type, length, column name and table name. This is what im trying to run:
select tbl.name as tablename, col.name as columnname, typ.name as columntype, col.length as size
from syscolumns col, sysobjects tbl, systypes typ
where col.id = tbl.id
and col.type = typ.type
and tbl.type='U'
I've noticed in the systypes table that theres more than one type for each type ID (e.g. type id #39 has over 50 entries but varchar is the one I want). Not only are the types returned wrong, i'm getting way more rows when executed than columns that exist in my tables.
參考解法
方法 1:
minor change in your query
select tbl.name as tablename, col.name as columnname, typ.name as columntype, col.length as size from syscolumns col, sysobjects tbl, systypes typ where col.id = tbl.id and col.usertype = typ.usertype and tbl.type='U'
(by Kambo_Rambo、Yogamurthy)