看了您的http://blog.csdn.net/zjcxc/archive/2004/01/04/20088.aspx这篇文章,看后有些疑问,
您里面包含了如下语句
- SQL code
主键=case when exists(SELECT 1 FROM [email protected]+'..sysobjects where xtype=''PK'' and name in ( SELECT name FROM [email protected]+'..sysindexes WHERE indid in( SELECT indid FROM [email protected]+'..sysindexkeys WHERE id = a.id AND colid=a.colid ))) then 1 else 0 end,
我拿我的一个数据库做了测试,但是里面出现了一个很奇怪的问题,就是求某一个表的主键,我设计数据库的时候我只设置类一个列作为主键,但是通过这个语句我查出来了我有三个列是主键,我有些不理解,差了SQL Server的帮助文档里面有这样的一段话:
DBCC UPDATEUSAGE
报告和更正 sysindexes 表的不正确内容，该内容可能会导致通过 sp_spaceused 系统存储过程产生不正确的空间使用报表。
语法
DBCC UPDATEUSAGE
( { 'database_name' | 0 }
[ , { 'table_name' | 'view_name' }
[ , { index_id | 'index_name' } ] ]
)
[ WITH [ COUNT_ROWS ] [ , NO_INFOMSGS ]
]
我试着之行了DBCC UPDATEUSAGE(0)但是查出来还是包含了三个列是主键,我有些不理解,请邹建老师讲解一下,谢谢老师~
------解决方案--------------------
- SQL code
如何取主键字段名称及字段类型--得到主键字段名1:SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME<>'dtproperties'2:EXEC sp_pkeys @table_name='表名'3:select o.name as 表名,c.name as 字段名,k.colid as 字段序号,k.keyno as 索引顺序,t.name as 类型from sysindexes ijoin sysindexkeys k on i.id = k.id and i.indid = k.indidjoin sysobjects o on i.id = o.idjoin syscolumns c on i.id=c.id and k.colid = c.colidjoin systypes t on c.xusertype=t.xusertypewhere o.xtype = 'U' and o.name='要查询的表名'and exists(select 1 from sysobjects where xtype = 'PK' and parent_obj=i.id and name = i.name)order by o.name,k.colid
------解决方案--------------------
zjcxc的这个脚本的确有点问题，这个问题以前有朋友提过。那个帖子的地址是：
http://topic.csdn.net/u/20070625/10/490e3182-9c15-4a42-bf5e-6c99cafda258.html
其中有一处判断不太严谨，修改了一下，请zjcxc指正：
- SQL code
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 [color=#FF0000]parent_obj = object_id('MyTab') and /*！！！只增加此行！！！*/[/color] 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 占用字节数, ......
------解决方案--------------------
确实有楼上的问题
- SQL code
WHERE parent_obj = object_id(d .name) AND xtype = 'PK' AND name IN