我用网上的
SELECT
表名 = case when a.colorder=1 then d.name else '' end,
列名 = a.name,
类型 = b.name,
默认值 = isnull(e.text,''),
字段说明 = isnull(g.[value],'')
FROM
syscolumns a
left join
systypes b
on
a.xusertype=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
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
order by
d.name,a.colorder
怎么生成的记录只有第一个有表名啊? 不能补上表名吗? 表名,列名,类型,默认值,字段说明
User ID int 自增长的ID
NAME char 帐号
PWD char (888888)密码
DESC char 描述
补上后,假如下面的数据在一个专门的表里, 怎么反过来生成表呢?
User ID int 自增长的ID
User NAME char 帐号
User PWD char (888888)密码
User DESC char 描述
SELECT
表名 = case when a.colorder=1 then d.name else '' end,
列名 = a.name,
类型 = b.name,
默认值 = isnull(e.text,''),
字段说明 = isnull(g.[value],'')
FROM
syscolumns a
left join
systypes b
on
a.xusertype=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
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
order by
d.name,a.colorder
怎么生成的记录只有第一个有表名啊? 不能补上表名吗? 表名,列名,类型,默认值,字段说明
User ID int 自增长的ID
NAME char 帐号
PWD char (888888)密码
DESC char 描述
补上后,假如下面的数据在一个专门的表里, 怎么反过来生成表呢?
User ID int 自增长的ID
User NAME char 帐号
User PWD char (888888)密码
User DESC char 描述
表名 = case when a.colorder=1 then d.name else '' end,
表说明 = case when a.colorder=1 then isnull(f.value,'') else '' end,
字段序号 = a.colorder,
字段名 = a.name,
标识 = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
主键 = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end,
类型 = b.name,
占用字节数 = a.length,
长度 = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小数位数 = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允许空 = case when a.isnullable=1 then '√'else '' end,
默认值 = isnull(e.text,''),
字段说明 = isnull(g.[value],'')
FROM
syscolumns a
left join
systypes b
on
a.xusertype=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
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.name='要查询的表' --如果只查询指定表,加上此条件
order by
a.id,a.colorder
改为
d.name
User ID int 自增长的ID
User NAME char 帐号
User PWD char (888888)密码
User DESC char 描述
同一贴