sqlserver查詢一個庫所有表的欄位名及欄位型別

select T_C.table_name,T_C.table_id,T_C.Column_name,T_C.Column_type_id,

systypes.name Column_type_name

from

(select sysobjects.name table_name,sysobjects.id table_id,SysColumns.name Column_name,

SysColumns.xtype Column_type_id from sysobjects

inner join SysColumns on sysobjects.id=SysColumns.id

where sysobjects.type='U') T_C

inner join systypes on T_C.Column_type_id=systypes.xtype

where systypes.xtype=systypes.xusertype

order by T_C.table_name

 

 

 

select sysobjects.name table_name,sysobjects.id table_id,SysColumns.name Column_name,

SysColumns.xtype Column_type_id,systypes.name Column_type_name from sysobjects

inner join SysColumns on sysobjects.id=SysColumns.id

inner join systypes on SysColumns.xtype=systypes.xtype

where sysobjects.type='U' and systypes.xtype=systypes.xusertype

order by table_name

 

 

 

 

select sysobjects.name table_name,sysobjects.id table_id,SysColumns.name Column_name,

SysColumns.xtype Column_type_id,systypes.name Column_type_name from sysobjects

inner join SysColumns on sysobjects.id=SysColumns.id

inner join systypes on SysColumns.xtype=systypes.xtype

where sysobjects.type='U' and systypes.xtype=systypes.xusertype

and systypes.name in ('datetime')  --查詢指定的欄位型別

order by table_name