主要是用到sysobjects、syscolumns及systypes。
sysobjects主要是記錄有哪些被建立的物件，也包含Talbe，type如下（取自From a Sybase Database, how I can get table description ( field names and types)?）：
- C – computed column
- D – default
- F – SQLJ function
- L – log
- N – partition condition
- P – Transact-SQL or SQLJ procedure
- PR – prepare objects (created by Dynamic SQL)
- R – rule
- RI – referential constraint
- S – system table
- TR – trigger
- U – user table
- V – view
- XP – extended stored procedure
所以使用下列的sql，就可以查到帳號可以存取的table
SELECT * FROM sysobjects WHERE type = 'U'
而我想查詢的是table中的column的type，所以要合用sysobjects、syscolumns及systypes，如下我要查的是Db_subject
select st.name, sc.name, sc.length from syscolumns sc inner join sysobjects so on sc.id = so.id inner join systypes st on st.type = sc.type where so.name = 'Db_subject'
這樣就可以看到Table的欄位資料了。
＝＝＝＝＝＝＝＝＝＝＝＝＝2015/04/07 更新＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
底下SQL可以更準確的取得
select o.name tableName,'' tableChnName,c.name columnName, '' columnChnName, t.name columnType,
(
case when (t.name='numeric' or t.name='decimal')
then '('+CONVERT(VARCHAR(10),c.prec)+','+CONVERT(VARCHAR(10),c.scale)+')'
else CONVERT(VARCHAR,c.length)
end
) columnLength --如果是數字,則顯示 (precision,scale)
from sysobjects o
inner join syscolumns c on c.id = o.id
inner join systypes t on t.usertype = c.usertype
where o.type = 'U' and o.name in ('表格1名稱', '表格2名稱' );
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
參考網址：
From a Sybase Database, how I can get table description ( field names and types)?
