SELECT
表名=case when a.colorder=1 then d.name else '' end,
字段序號=a.colorder,
字段名=a.name,
類型=b.name
FROM syscolumns a
inner join sysobjects d on a.id=d.id
left join systypes b on a.xtype=b.xusertype
left join syscomments e on a.cdefault=e.id
left join sysproperties g on a.id=g.id and a.colid=g.smallid
left join sysproperties f on d.id=f.id and f.smallid=0
where d.xtype='U' and d.name<>'dtproperties'
and d.name='stk_main'--'要查的表'
order by a.id,a.colorder
表名=case when a.colorder=1 then d.name else '' end,
字段序號=a.colorder,
字段名=a.name,
類型=b.name
FROM syscolumns a
inner join sysobjects d on a.id=d.id
left join systypes b on a.xtype=b.xusertype
left join syscomments e on a.cdefault=e.id
left join sysproperties g on a.id=g.id and a.colid=g.smallid
left join sysproperties f on d.id=f.id and f.smallid=0
where d.xtype='U' and d.name<>'dtproperties'
and d.name='stk_main'--'要查的表'
order by a.id,a.colorder
@tablename varchar(10)
asSELECT
表名=case when a.colorder=1 then d.name else '' end,
字段序號=a.colorder,
字段名=a.name,
類型=b.name
FROM syscolumns a
inner join sysobjects d on a.id=d.id
left join systypes b on a.xtype=b.xusertype
left join syscomments e on a.cdefault=e.id
left join sysproperties g on a.id=g.id and a.colid=g.smallid
left join sysproperties f on d.id=f.id and f.smallid=0
where d.xtype='U' and d.name<>'dtproperties'
and d.name=@tablename--'要查的表'
order by a.id,a.colorder
我新建的一个学习sql的群:3747961
@tablename varchar(10)
as
exec('sp_help ['+@tablename+']')
显示的“长度”要和企业管理器设计表时候显示的长度一致,比如:有个字段的类型为nvarchar,用企业管理器设计表时定的长度是40,那么在数据库里的length就是80,我想
这个存储过程显示的的长度也是40,就是我用存储过程得到的结果应该和用企业管理器设计表时候显示的字段信息一致,请问该怎么做?
@tablename varchar(10)
asSELECT
表名=case when a.colorder=1 then d.name else '' end,
字段序號=a.colorder,
字段名=a.name,
類型=b.name,
長度=COLUMNPROPERTY(a.id,a.name,'PRECISION')
FROM syscolumns a
inner join sysobjects d on a.id=d.id
left join systypes b on a.xtype=b.xusertype
left join syscomments e on a.cdefault=e.id
left join sysproperties g on a.id=g.id and a.colid=g.smallid
left join sysproperties f on d.id=f.id and f.smallid=0
where d.xtype='U' and d.name<>'dtproperties'
and d.name=@tablename--'要查的表'
order by a.id,a.colorder
--exec table_type 'stk_main'create proc table_type
@tablename varchar(10)
asSELECT
表名=case when a.colorder=1 then d.name else '' end,
字段序號=a.colorder,
字段名=a.name,
類型=b.name,
占用字節=a.length FROM syscolumns a
inner join sysobjects d on a.id=d.id
left join systypes b on a.xtype=b.xusertype
left join syscomments e on a.cdefault=e.id
left join sysproperties g on a.id=g.id and a.colid=g.smallid
left join sysproperties f on d.id=f.id and f.smallid=0
where d.xtype='U' and d.name<>'dtproperties'
and d.name=@tablename--'要查的表'
order by a.id,a.colorder