|M| 邹键老大的一条SQL查询语句有错误,大家看一下要怎么改

Question

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的帮助的系统统表
中的 主键表 对像表  字段表 进行关联查询也没有办法正确查出一个表的主键请大家看看上面的语句要怎么来改才能正确的显示表信息中的主建
谢谢

Answer 1

Create Table MyTab
(ID           int Primary Key,
TypeID       int,  
Name         nvarchar(20))
Create Table TEST
(TypeID      int Primary Key,
TypeName    nvarchar(20))
GO
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
GO
Drop Table MyTab, TEST
/*
表名 表说明 字段序号 字段名 标识 主键 类型 占用字节数 长度 小数位数 允许空 默认值 字段说明
MyTab 1 ID √ int 4 10 0
2 TypeID int 4 10 0 √
3 Name nvarchar 40 20 0 √
*/

Answer 2

如果你只是要其實一部分代碼SELECT 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 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 类型
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'
WHERE (d.name = 'MyTab') ORDER BY a.id, a.colorder
--Result
/*
表名 字段序号 字段名 标识 主键 类型
MyTab 1 ID √ int
2 TypeID int
3 Name nvarchar
*/

Answer 3

我上面的数据库是进行缩略的
但是我的Agent表里面只有主键AgentID 但他里面的 SponsorID 查出来也是主键
但我表里面是没有主键的

Answer 4

SELECT *
FROM syscolumns
WHERE (id = OBJECT_ID(N'Agent')) AND (COLUMNPROPERTY(id, name, 'IsIdentity') 
      = 1)
我用这一条单主键的可以正确查出来
但是表有多个字段这个查询语句就查不出来了

Answer 5

--你可以看到有一個TEST表中TypeID為主鍵，在MyTab中也有一個字段名為TypeID，但是其不為主鍵--在查詢的結果中，只有ID這個字段顯示為主鍵Create Table MyTab
(ID           int Primary Key,
TypeID       int,  
Name         nvarchar(20))
Create Table TEST
(TypeID      int Primary Key,
TypeName    nvarchar(20))
GO
SELECT 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 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 类型
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'
WHERE (d.name = 'MyTab') ORDER BY a.id, a.colorder
GO
Drop Table MyTab, TEST
--Result
/*
表名 字段序号 字段名 标识 主键 类型
MyTab 1 ID √ int
2 TypeID int
3 Name nvarchar
*/

Answer 6

2 楼paoluo（一天到晚游泳的鱼）回复于 2005-05-17 17:15:52 得分 45
sp_pkeys   'Tablename'
用鱼兄别的地方写的这一条就可以查出来
但怎么和上面的进行合并呢

Answer 7

的确有问题,增加一行代码就可以了:
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 占用字节数, 
      ......

Answer 8

?
hellowork(一两清风)什麼情況有問題，怎麼我測試的沒有出錯？

Answer 9

就是 我有100+的表
就出现了这个问题
hellowork(一两清风)的代码我先试一下

Answer 10

我上面的測試代碼中也有定義兩個表的，而且TypeID在一個表中是主鍵，在另外一個表中不是主鍵，怎麼沒有出問題？

Answer 11

hellowork(一两清风)的代码我先试一下行不会出现多个主键了
但这样的话你的
WHERE parent_obj = object_id('MyTab') and    /*！！！只增加此行！！！*/
这里指字了表但我如果是要查出表中所有的信息就不能object_id('MyTab')  要怎么改

Answer 12

增加了这行限制条件后对查询表的所有信息没有任何影响,因为这行代码是用在exists()判断中的.

Answer 13

to : paoluo
下面这个判断的确不太严谨,表多的时候表露的几率就会大些:
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)))

Answer 14

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(d .name) 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 占用字节数, 
      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
ORDER BY d .name, a.colorder好了这才是真正正确的

Answer 15

看了这个才发现，呵呵！
得谢谢hellowork(一两清风)。