Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jul 2003
    Posts
    30

    Unanswered: How find out which tables have Unique indexes?

    Hi,
    I am using Sql SERVER 2000 and I am doing some kind of schema comparisions and I would like to find out how would I be able to find which tables have which columns on which there are unique indexes?
    I tried checking out the SYSINDEXES but theres no field which gives me that info.
    I have also checked out sysobjects and checked out the XTYPE='UQ'
    but that would give me the unique constraints but not the unique indexes.

    Can you help?

    any help in this direction is very well appreciated.
    thanks
    -soumil

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    A Unique constraint automatically creates a unique index ... so are you looking for something else or more specific ?

  3. #3
    Join Date
    Jul 2003
    Posts
    30
    YES. Infact when you create a table using the Enterprise Manager you can go in the table design view and then click on creating new index and now under the unique option theres a check box for either a contraint or for a index.
    If you chek the index it will create an index.
    How can you find out all the unique indexes that were created by the system.
    -soumil

    Originally posted by rnealejr
    A Unique constraint automatically creates a unique index ... so are you looking for something else or more specific ?

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    So are you looking for a unique index that was not created as a by-product of a unique contraint or vice-versa ?

  5. #5
    Join Date
    Jul 2003
    Posts
    30
    That would be RIGHT.

    Originally posted by rnealejr
    So are you looking for a unique index that was not created as a by-product of a unique contraint or vice-versa ?

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    Check out the information schema views - I have to go but I will check back.

  7. #7
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Yes, I think INFORMATION_SCHEMA.TABLE_CONSTRAINTS is the one nearest and its best policy to query IS views rather than direclty to the system tables.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  8. #8
    Join Date
    Jul 2003
    Posts
    30
    Thats interesting.
    Where do I find more info on the information_Schema views and their descriptions?
    Secondly, Whats wierd is that it did not show me any rows with constraint type=Unique at all even though I know that there are tables that I have created lately which had the unique indexes?

    I never knew about the information_Schema views. Thanx for sharig that piece of info with me.

    -Soumil
    Originally posted by Satya
    Yes, I think INFORMATION_SCHEMA.TABLE_CONSTRAINTS is the one nearest and its best policy to query IS views rather than direclty to the system tables.

  9. #9
    Join Date
    Feb 2002
    Posts
    2,232
    Reference your bol (SQL Server Books Online).

  10. #10
    Join Date
    Jul 2003
    Posts
    30
    I found the info in BOL but still no luck finding the info about the unique indexes.

    Originally posted by rnealejr
    Reference your bol (SQL Server Books Online).

  11. #11
    Join Date
    Feb 2002
    Posts
    2,232
    What you are looking for is too specific ... Let me look at it and I will try to find a solution for you.

  12. #12
    Join Date
    Feb 2002
    Posts
    2,232
    OK - Here are the results - use at your own risk. One of the problems with this is that ms does not guarantee the usuage of these columns - so it could change from service pack to service pack or between versions...

    In the sysindexes table - look at the status field. The status field is a field that set bit values - each value has its own meaning.

    4096 = unique constraint
    2 = unique index (also set for a unique constraint)
    1 = ignore duplicate key

  13. #13
    Join Date
    Nov 2003
    Posts
    94
    Code:
    select 
    	t.[CONSTRAINT_TYPE],
    	u.[ORDINAL_POSITION],
    	c.[TABLE_NAME],
    	u.[CONSTRAINT_NAME] 
    	from [INFORMATION_SCHEMA].[COLUMNS] c
    		inner join [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] u
    			on c.[TABLE_NAME] = u.[TABLE_NAME]
    			and c.[COLUMN_NAME] = u.[COLUMN_NAME]
    		inner join [INFORMATION_SCHEMA].[table_constraints] t
    			on u.[CONSTRAINT_NAME] = t.[CONSTRAINT_NAME]
    	where t.[CONSTRAINT_TYPE] = 'UNIQUE'

  14. #14
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    MS-SQL specific:

    Code:
    SELECT object_name(id), name 
    FROM sysindexes 
    WHERE status&2=2

  15. #15
    Join Date
    Sep 2003
    Posts
    212
    This script finds all the indexes etc for a particular table. I found this using profiler when EM was creating the SQL for the table:

    Here:

    Use DBNAME
    declare @tablename nvarchar(517) , @indexname nvarchar(258)
    set @tablename = '[dbo].[Tablename]'
    set @indexname = null

    select i.name,i.status,
    IndCol1 = index_col(@tablename, i.indid, 1),
    IndCol2 = index_col(@tablename, i.indid, 2),
    IndCol3 = index_col(@tablename, i.indid, 3),
    IndCol4 = index_col(@tablename, i.indid, 4),
    IndCol5 = index_col(@tablename, i.indid, 5),
    IndCol6 = index_col(@tablename, i.indid, 6),
    IndCol7 = index_col(@tablename, i.indid, 7),
    IndCol8 = index_col(@tablename, i.indid, 8),
    IndCol9 = index_col(@tablename, i.indid, 9),
    IndCol10 = index_col(@tablename, i.indid, 10),
    IndCol11 = index_col(@tablename, i.indid, 11),
    IndCol12 = index_col(@tablename, i.indid, 12),
    IndCol13 = index_col(@tablename, i.indid, 13),
    IndCol14 = index_col(@tablename, i.indid, 14),
    IndCol15 = index_col(@tablename, i.indid, 15),
    IndCol16 = index_col(@tablename, i.indid, 16)
    , SegName = s.groupname
    , FullTextKey = IndexProperty(object_id(@tablename), i.name, N'IsFulltextKey')
    from (sysindexes i inner join
    sysfilegroups s on
    i.groupid = s.groupid )
    where id = object_id(@tablename) and i.indid > 0 and i.indid < 255
    and (@indexname is null or i.name = @indexname)
    and i.status < 10000000
    order by i.indid
    Last edited by vmlal; 12-17-03 at 14:43.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •