查询结果如下:
表名, 字段名称 字段类型 字段长度 小数点后几位table1 id nvarchar 20 0
table1 name nvarchar 40 0
table1 qty number 18 6
table2 code nvarchar 20 0
table2 name nvarchar 40 0
表名, 字段名称 字段类型 字段长度 小数点后几位table1 id nvarchar 20 0
table1 name nvarchar 40 0
table1 qty number 18 6
table2 code nvarchar 20 0
table2 name nvarchar 40 0
SELECT * FROM syscolumns
SELECT * FROM systypes
SELECT * FROM sysobjects三个表组合查询。吃饭去了
use databasename;
sp_columns '%';
c.name as object_name,
a.column_id,
a.name as column_name,
b.name +
case
when a.max_length = -1 and b.name in ('varbinary', 'varchar', 'nvarchar') then '(max)'
when b.name in ('decimal', 'numeric') then '(' + ltrim(a.precision) + ',' + ltrim(a.scale) + ')'
when b.name in ('varbinary', 'varchar', 'binary', 'char') then '(' + ltrim(a.max_length) + ')'
when b.name in ('nvarchar', 'nchar') then '(' + ltrim(a.max_length / 2) + ')'
else ''
end as data_type_definition
from
sys.columns as a
inner join
sys.types as b
on a.user_type_id = b.user_type_id
inner join
sys.objects as c
on a.object_id = c.object_id and c.type = 'U'