首页 新闻 搜索 专区 学院

在设计表的时候会写每个字段的描述,如何用sql语句把表结构和描述一起查询出来呢

0
[待解决问题]

在设计表的时候会写每个字段的描述,如何用sql语句把表结构和描述一起查询出来呢

Angel90的主页 Angel90 | 初学一级 | 园豆:200
提问于:2011-04-20 16:35
< >
分享
所有回答(2)
0

Create Procedure [dbo].[Test_DB]
@tablename varchar(30)=''
as

Begin
declare @str varchar(3000)

select @str='
--select ''表名'' AS 表名,''0字段序号'' AS 字段序号,''字段名'' AS 字段名,''标识'' AS 标识,''主键'' AS 主键,''类型'' AS 类型,''占用字节数'' AS 占用字节数,''长度'' AS 长度,''小数位数'' AS 小数位数,''允许空'' AS 允许空,''默认值'' AS 默认值,''字段说明'' AS 字段说明

SELECT     TOP (100) PERCENT (CASE WHEN a.colorder = 1 THEN d .name ELSE '''' END) AS 表名, a.colorder AS 字段序号, a.name AS 字段名,
                      (CASE WHEN COLUMNPROPERTY(a.id, a.name, ''IsIdentity'') = 1 THEN ''√'' ELSE '''' END) AS 标识, (CASE WHEN
                          (SELECT     COUNT(*)
                            FROM          sysobjects
                            WHERE      (name IN
                                                       (SELECT     name
                                                         FROM          sysindexes
                                                         WHERE      (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 主键,
                      b.name AS 类型, a.length AS 占用字节数, COLUMNPROPERTY(a.id, a.name, ''PRECISION'') AS 长度, ISNULL(COLUMNPROPERTY(a.id, a.name, ''Scale''),
                      0) AS 小数位数, (CASE WHEN a.isnullable = 1 THEN ''√'' ELSE '''' END) AS 允许空, ISNULL(e.text, '''') AS 默认值, ISNULL(g.value, '''') AS 字段说明
FROM         sys.syscolumns AS a LEFT OUTER JOIN
                      sys.systypes AS b ON a.xtype = b.xusertype INNER JOIN
                      sys.sysobjects AS d ON a.id = d.id AND d.xtype = ''U'' AND d.name <> ''dtproperties'' LEFT OUTER JOIN
                      sys.syscomments AS e ON a.cdefault = e.id LEFT OUTER JOIN
                      sys.extended_properties AS g ON a.id = g.major_id AND a.colid = g.minor_id
'
if(@tablename!='')
 Begin
  --select @tablename=Replace(@tablename,'dbo.','')
  select @str=@str+'where d.name='''+@tablename+ ''''
 End

select @str=@str+'ORDER BY a.id, 字段序号'
exec(@str)
End

上面直接 复制

调用: Test_DB '表名'

like%'远远'% | 园豆:635 (小虾三级) | 2011-04-20 16:48
看不懂
支持(0) 反对(0) Angel90 | 园豆:200 (初学一级) | 2011-04-20 17:05
会复制吗?
支持(0) 反对(0) like%'远远'% | 园豆:635 (小虾三级) | 2011-04-20 17:05
有些东西不一定要看懂(看懂要费很长时间),知道有什么用及怎么用就行。比如数据库 Test 其中有表A,B,C 在数据中创建上面的脚本,然后调用就是 Test_DB 'A' 这就是看A的结构了,再不懂的话,我撞墙了
支持(0) 反对(0) like%'远远'% | 园豆:635 (小虾三级) | 2011-04-20 17:09
那你就撞墙吧!嘿嘿!
支持(0) 反对(0) Angel90 | 园豆:200 (初学一级) | 2011-04-25 16:37
0
邀月 | 园豆:25375 (高人七级) | 2011-04-21 23:49
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册