Re: Имя unique key

From

Alexander Cherepukhin (2:5025/17)

To

All

Date

2005-10-19T12:29:44Z

Area

SU.DBMS.SQL

From: "Alexander Cherepukhin" <ralex@relex.ru>

        Спасибо за подсказку!
    Вот что получилось в результате:

SELECT idx.name 'idxname', OBJECT_NAME(idx.id) 'tabname',
  COL_NAME(idx.id, sik.colid) 'colname', sik.keyno 'keyno',
  CONVERT(bit, INDEXPROPERTY(idx.id, idx.name, N'IsClustered')) AS
isclustered,
  CONVERT(bit, INDEXPROPERTY(idx.id, idx.name, N'IsUnique')) AS isunique,
  CONVERT(bit, CASE WHEN (idx.status & 4096)=0 THEN 0 ELSE 1 END) AS
isuniquecon,
  CONVERT(bit, CASE WHEN (idx.status & 2048)=0 THEN 0 ELSE 1 END) AS
isprimary,
  CONVERT(bit, CASE WHEN (idx.status & 0x1)=0 THEN 0 ELSE 1 END) AS
ignoredupkey
FROM sysindexes idx
  INNER JOIN sysindexkeys sik
  ON sik.id = idx.id AND sik.indid = idx.indid
WHERE
  CONVERT(bit, INDEXPROPERTY(idx.id, idx.name, N'IsStatistics')) = 0

а sysconstraints и sysindexes увязываются между собой по имени (для PK и UK)

SELECT idx.name FROM sysconstraints con
   INNER JOIN sysindexes idx ON idx.name = OBJECT_NAME(con.constid)

        С уважением, Александр Черепухин.


--- Microsoft Outlook Express 6.00.2800.1106
 * Origin: RELEX Inc. (2:5025/17@fidonet)
SEEN-BY: 46/50 50/12 450/1024 5000/5000 5010/53 5011/13 5012/46 5015/10 28
SEEN-BY: 5019/31 5020/545 715 4441 5021/29 5025/2 3 17 27 63 69 77 151 646 900
SEEN-BY: 5025/2275 5026/10 5027/16 5030/115 5035/38 5036/34 5047/43 5053/16
SEEN-BY: 5054/1 8 9 28 35 37 45 63 67 5059/9 5069/7 5077/70 5080/68 1003
SEEN-BY: 5085/13 5095/20 6000/12 254

PATH: 5025/17 2 3 5020/545 5054/1 37