変奏現実

sys.sysobjects　と　sys.columns　を使う場合。

use データベース名
select O.xtype, O.name, C.name
from sys.columns C inner join sys.sysobjects O
on C.object_id = O.id and O.xtype in('U', 'V')
order by O.xtype, O.name, C.name

sys.objects　と　sys.columns　を使う場合。

use データベース名
select O.type, O.name, C.name
from sys.columns C inner join sys.objects O
on C.object_id = O.object_id and O. in('U', 'V')
inner join sys.types T
on C.user_type_id=T.user_type_id
order by O.type, O.name, C.name

※sysobjects にはxtype,type,id　があるがobject_idが無い。
sys.objects　と　sys.columns　と　sys.types　を使ってフィールド定義も見る場合。

use データベース名
select O.type, O.name, C.name, /*C.user_type_id,*/
T.name + CASE
 WHEN T.name in ('BINARY','char') THEN T.name + '(' + RTRIM(CAST(C.max_length AS char)) + ')'
 WHEN T.name in ('datetime2','datetimeoffset','time') THEN T.name+'('+RTRIM(CAST(C.scale AS char)) + ')'
 WHEN T.name in ('decimal','numeric') THEN T.name + '(' + RTRIM(CAST(C.precision AS char)) + ',' + RTRIM(CAST(C.scale AS char)) + ')'
 WHEN T.name in ('nchar','nvarchar') THEN T.name + '(' + CASE WHEN C.max_length = -1 THEN 'MAX' ELSE RTRIM(CONVERT(nchar,C.max_length/2)) END + ')'
 WHEN T.name in ('varbinary','varchar') THEN T.name + '(' + CASE WHEN C.max_length = -1 THEN 'MAX' ELSE RTRIM(CONVERT(nchar,C.max_length)) END + ')'
 ELSE ''
END AS フィールド型
from sys.columns C inner join sys.objects O
on C.object_id = O.object_id and O.type in('U', 'V')
inner join sys.types T
on C.user_type_id=T.user_type_id
order by O.type, O.name, C.name