解决了,用下面语句,把表名改一下,复制到excel里面就可以了Declare @tableName varchar(50) set @tableName='BMG602'
SELECT (case when a.colorder=1 then d.name else '' end) as 表名, a.name as 字段名, isnull(g.[value],'') as 字段说明, b.name as 类型, COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度, isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0)as 小数位数, (case when(SELECT count(*) FROM sysobjects WHERE(name in (SELECT name FROM sysindexes ss WHERE (ss.id=a.id) AND(indid in (SELECT indid FROM sysindexkeys WHERE(id=a.id) AND(colid in (SELECT colid FROM syscolumns WHERE(id=a.id) AND(name=a.name))))))) AND (xtype='PK'))>0 then'是' else '' end) as 是否主键, (case when a.isnullable=1 then '' else '是' end) as 是否必填, isnull(e.text,'') as 默认值
FROM syscolumns a left join systypes b on a.xtype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' left join syscomments e on a.cdefault=e.id left join sys.extended_properties g on a.id=g.major_id AND a.colid=g.minor_id --where d.name like '%'+@tableName+'%' where d.name = @tableName order by d.name,a.colorder
set @tableName='BMG602'
SELECT
(case when a.colorder=1 then d.name else '' end) as 表名,
a.name as 字段名,
isnull(g.[value],'') as 字段说明,
b.name as 类型,
COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度,
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0)as 小数位数,
(case when(SELECT count(*) FROM sysobjects
WHERE(name in (SELECT name FROM sysindexes ss
WHERE (ss.id=a.id) AND(indid in (SELECT indid FROM sysindexkeys WHERE(id=a.id)
AND(colid in (SELECT colid FROM syscolumns WHERE(id=a.id)
AND(name=a.name)))))))
AND (xtype='PK'))>0
then'是' else '' end) as 是否主键,
(case when a.isnullable=1 then '' else '是' end) as 是否必填,
isnull(e.text,'') as 默认值
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
left join sys.extended_properties g on a.id=g.major_id AND a.colid=g.minor_id
--where d.name like '%'+@tableName+'%'
where d.name = @tableName
order by d.name,a.colorder