日期:2014-05-16 浏览次数:20617 次
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
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
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,
ST