34,591
社区成员
发帖
与我相关
我的任务
分享
with tb as
(select pk_table=d.name,pk_name=b.name,pk_column=a.name
from sys.columns a,sys.indexes b,sys.index_columns c,sys.tables d
where b.is_primary_key=1
and a.column_id=c.column_id
and a.object_id=b.object_id
and a.object_id=c.object_id
and a.object_id=d.object_id)
select pk_table,pk_name,
STUFF((SELECT ';' + pk_column FROM tb b WHERE a.pk_table = b.pk_table and a.pk_name = b.pk_name
FOR XML PATH('')),1,1,'') AS pk_column
from tb a
group by pk_table,pk_name
SELECT t1.name,t2.name
FROM
(
select id,name from sysobjects where xtype='U'
AND LEFT(name,4)<>'_sys'
AND LEFT(name,3)<>'log'
) t1
LEFT JOIN
(
SELECT parent_obj,name FROM sysobjects WHERE xtype='pk'
) t2 ON t1.id=t2.parent_obj
以上是我写的,缺少1个主键对应字段
SELECT indexname = a.name , tablename = c. name , indexcolumns = d .name , a .indid
FROM sysindexes a JOIN sysindexkeys b ON a .id = b . id AND a .indid = b .indid
JOIN sysobjects c ON b .id = c . id
JOIN syscolumns d ON b .id = d . id AND b .colid = d .colid
WHERE a .indid NOT IN ( 0 , 255 )
-- and c.xtype='U' and c.status>0 -- 查所有用户表
AND c .name IN( 'SalesOrderHeader_TEST' , 'SalesOrderDetail_TEST' ) --查指定表
ORDER BY c. name ,
a.name ,
d.name
select o.name as 表名,c.name as 字段名,k.colid as 字段序号,k.keyno as 索引顺序
from sysindexes i
join sysindexkeys k on i.id = k.id and i.indid = k.indid
join sysobjects o on i.id = o.id
join syscolumns c on i.id=c.id and k.colid = c.colid
where o.xtype = 'U'
and exists(select 1 from sysobjects where xtype = 'PK' and name =
i.name) and o.name='AuctionSell'-- (AuctionSell是表名称)
order by o.name,k.colid
表名 字段名 字段序号 索引顺序
----------- --------------------- ------ ------
AuctionSell ASID 1 1