|M| 邹键老大的一条SQL查询语句有错误,大家看一下要怎么改
SELECT CASE WHEN a.colorder = 1 THEN d.name ELSE ' ' END AS 表名,
CASE WHEN a.colorder = 1 THEN isnull(CONVERT(nvarchar, f.value), ' ')
ELSE ' ' END AS 表说明, a.colorder AS 字段序号, a.name AS 字段名,
CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity ')
= 1 THEN '√ ' ELSE ' ' END AS 标识, CASE WHEN EXISTS
(SELECT 1
FROM sysobjects
WHERE xtype = 'PK ' 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 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(CONVERT(nvarchar, g.[value]), ' ') AS 字段说明
FROM syscolumns a LEFT OUTER 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 OUTER JOIN
syscomments e ON a.cdefault = e.id LEFT OUTER JOIN
sysproperties g ON a.id = g.id AND a.colid = g.smallid LEFT OUTER JOIN
sysproperties f ON d.id = f.id AND f.smallid = 0
WHERE (d.name = 'MyTab ') ORDER BY a.id, a.colorder
这是我在网上搜到的邹键老大写的一条查询表信息的查询语句
但这里面有一个地方查出来的数据是错误的:
SELECT name
FROM sysindexes
WHERE indid IN
(SELECT indid
FROM sysindexkeys
WHERE id = a.id AND colid = a.colid)))
也就是查主键的
我用这一条查询的时候比如我的表
ID int 标示 主键
TypeID int
Name nvarchar
---------------------------------
但查出来的时候ID和TypeID都变为主键了
我想是因为在别一个表中有
TypeID int 标示 主建
TypeName nvarchar
的原因
----------------------------------------
然后我查SQL的帮助的系统统表
中的 主键表 对像表 字段表 进行关联查询也没有办法正确查出一个表的主键
请大家看看上面的语句要怎么来改才能正确的显示表信息中的主建
谢谢
[解决办法]
測試了下，老大完整的代碼是沒有問題的
[解决办法]
的确有问题,增加一行代码就可以了:
SELECT CASE WHEN a.colorder = 1 THEN d.name ELSE ' ' END AS 表名,
CASE WHEN a.colorder = 1 THEN isnull(CONVERT(nvarchar, f.value), ' ')
ELSE ' ' END AS 表说明, a.colorder AS 字段序号, a.name AS 字段名,
CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity ')
= 1 THEN '√ ' ELSE ' ' END AS 标识, CASE WHEN EXISTS
(SELECT 1
FROM sysobjects
WHERE parent_obj = object_id( 'MyTab ') and /*！！！只增加此行！！！*/
xtype = 'PK ' 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 AS 主键, b.name AS 类型, a.length AS 占用字节数,
......
