SQL Server Performance

Elminating Cluster Index created by Primary Key?

Discussion in 'General DBA Questions' started by sonnysingh, Oct 27, 2006.

  1. sonnysingh Member

    Hi Folks

    I am creating script to drop and create indexes of give tables or all the tables. I have situation where I need to eliminate cluster indexes out of scripted those made through primary key cos I want to do that part through constraints(which i think more arropriate to do). Now, I have gone through sysindexes, sysobjects and ObjectProperty but no luck so far..
    here is simple code for all...

    SELECT SYSOBJECTS.NAME, SYSINDEXES.NAME, CASE when SYSINDEXES.indid = 1 then upper('Clustered index') else upper('Non clustered Index') END
    WHERE SYSINDEXES.indId <> 0 AND SYSINDEXES.name not like '_WA_SYS%'
    AND SYSINDEXES.indid<>255 AND objectProperty(SYSOBJECTS.id,'isUsertable')=1
    Order by SYSOBJECTS.name

    is any one help please??????

    Thanks in Advance
  2. FrankKalis Moderator

    When you include sysconstraint in your query, you can use the OBJECTPROPERTY(sysconstraint.constid, 'IsPrimaryKey') to get the information.

    Frank Kalis
    Microsoft SQL Server MVP
  3. sonnysingh Member

    Thanks Frank

    I know about sysconstraints view (or INFORMATION_SCHEMA.TABLE_CONSTRAINTS).. my question as how how elminate those cluster indexes from result set of query which made through making primary key by default?? like said I have checked out the different columns in sysindexes, sysobjects and objectproperty etc... I think that cos lack of information about columns in these tables I couldn't solve it...

    please help...

  4. sonnysingh Member


    look at this:

    SELECT dbo.sysobjects.name AS TableName, dbo.sysindexes.name AS IndexName, dbo.sysindexes.indid
    FROM dbo.sysobjects INNER JOIN dbo.sysconstraints ON dbo.sysobjects.id = dbo.sysconstraints.id INNER JOIN dbo.sysindexes ON dbo.sysobjects.id = dbo.sysindexes.id
    WHERE (dbo.sysindexes.indid <> 255) AND (NOT (dbo.sysindexes.name LIKE N'_WA_SYS%')) AND (dbo.sysindexes.indid <> 0) AND OBJECTPROPERTY(dbo.sysconstraints.id, 'IsPrimaryKey') <> 1)
    ORDER BY dbo.sysobjects.name

    This still include Cluster Indexes create with Primary Key...

  5. Adriaan New Member

    These are the clustered indexes (indid=1) that are also the primary key on a user table -

    select i.[name] [IndexName], object_name(i.id) [TableName]
    from dbo.sysindexes i
    where i.indid = 1
    and objectproperty(i.[id], 'issystemtable') <> 1
    and objectproperty(object_id(i.[name]), 'isprimarykey') = 1

    To find the clustered indexes that are not the primary key, change the last 1 to a 0.
  6. sonnysingh Member

    Thanks Adriaan

    It's works fine with Cluster Index with primary condition but not for only 'Cluster' and 'Noncluster' indexes. When I changed to '0' for '1' as you have mentioned, It's appear list of tables that do not have indexes at all.

    select object_name(sysindexes.id) [TableName], sysindexes.[name] [IndexName]
    from dbo.sysindexes
    where sysindexes.indid = 0
    and objectproperty(sysindexes.[id], 'issystemtable') <> 1
    and objectproperty(object_id(sysindexes.[name]), 'isprimarykey') = 0

    what else need to add or change????
  7. sonnysingh Member

    Hi Folks

    any one knows what would be the Indid (from sysindexes) for unique index and statistics. I have some column with statistics of name given by users so need to be eliminate from cluster and noncluster indexes.

    Please help or refer to any articles etc...it is urgent.

    Thanks in advance
  8. joechang New Member

    a primary key is a constraint.
    an index should only have a sysindexes entry
    constraints (pk, and unique) should have sysobjects entries
    not sure on unique indexes
  9. Adriaan New Member

    Check the INDEXPROPERTY function!
  10. Adriaan New Member

    SELECT OBJECT_NAME(i.[id]) TableName,
    i.[name] IndexName,
    INDEXPROPERTY(i.[id], i.[name], 'IsUnique') IsUnique,
    INDEXPROPERTY(i.[id], i.[name], 'IsClustered') IsClustered
    FROM dbo.sysindexes i
  11. jezemine New Member

    can't resist here: SqlSpec will tell you all about which tables have which constraints, indexes, etc.

    link to it is in my sig below.

    also Dinesh gave me a very nice review:

    SqlSpec - a fast, cheap, and comprehensive data dictionary generator
    for SQL Server 2000 and 2005 and Analysis Server 2005 - www.elsasoft.org
  12. TRACEYSQL New Member

    Is there a way to get the index name followed by the column name and whether this column allows nulls
    Index_name column_name varchar not null.
  13. satya Moderator

    You could get using INFORMATION_SCHEMA views, refer to Books online for more informatin

Share This Page