SQL Server Performance

Number of indexes and Number of objects

Discussion in 'General DBA Questions' started by buenowight, Jul 13, 2007.

  1. buenowight New Member

    Hi ,

    Is there a way to find the number of indexes in a given sql database??
    Is there a way to find the total number of objects in a given SQL database?

    Regards,
    bueno
  2. FrankKalis Moderator

    For the first check out the sysindexes system table.
    For the second check out sysobjects.
    Might be that you have to filter out something, so take a look at the description of these tables in BOL.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
    Webmaster:http://www.insidesql.de
  3. buenowight New Member

    I have checked in the sysindexes and sysobjects table even before posting my question. But the filters whci show accurate resutls for one database would not show correct results for the other database.

    Thats the reason I was wondering if there is someother way to get these counts.

    Thanks.
  4. FrankKalis Moderator

  5. MohammedU New Member

    select count(*)
    -- name
    from sysobjects where objectproperty( id, 'ismsshipped') = 0
    go
    select count(*)
    --object_name(id) as TableName, name as IndexName
    from sysindexes
    where objectproperty( id, 'ismsshipped') = 0
    and INDEXPROPERTY(id , name, 'IsHypothetical') = 0
    and INDEXPROPERTY(id , name, 'IsAutoStatistics') = 0
    and INDEXPROPERTY(id , name, 'IsStatistics') = 0
    -- order by object_name(id)


    MohammedU.
    Microsoft SQL Server MVP
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  6. buenowight New Member

    This is the list of queries that i am using to get the list of foreign keys, primay keys, non clustered indexes , list of user tables and list of objects.

    Each of our database has a system table called dt_properties can any one tell me what is this used for ??

    Please let me know if any of the belwo queries return the wrong results.

    --To get the list of foreigh keys in the database
    SELECT * FROM SYSOBJECTS WHERE XTYPE = 'F'

    --to get the list of primary keys( clusetered indexs) in the database
    SELECT * FROM SYSOBJECTS WHERE XTYPE = 'PK' and name not like 'pk_dt%' and status = 16

    --to get the list of non clustered indexes in the database
    select name,* from sysindexes where indid >1 and origfillfactor > 0

    --to get the list of user tables in the database
    select * from sysobjects where xtype = 'U' and status > 0

    --to get the list of objects in the database
    select * from sysobjects where xtype not in ( 'D', 'S') and status > 0 and name not like 'pk_dt%'

    thx.
  7. MohammedU New Member

    dt_properties table is used for database diagrams and all dt_ objects too...

    Use objectproperty( id, 'ismsshipped') = 0 in your query to get the objects not created by MS...


    MohammedU.
    Microsoft SQL Server MVP
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  8. thugs New Member

    i am also looking for these queries

    thanks for the queries
  9. amu_27 New Member

    One more detailed information script for indexes<br /><br />/*Script lists detail INDEX Information for all Databases*/<br /><pre id="code"><font face="courier" size="2" id="code"><br />use master <br /><br />DECLARE @db_name varchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />DECLARE @DbID int<br />DECLARE @sql_string nvarchar(4000)<br /><br />set nocount on<br /><br />CREATE TABLE [#tblHistoryIndex] (<br />[DbName] [varchar] (12<img src='/community/emoticons/emotion-11.gif' alt='8)' /> NOT NULL ,<br />[TableName] [varchar] (12<img src='/community/emoticons/emotion-11.gif' alt='8)' /> NOT NULL ,<br />[IndexName] [varchar] (12<img src='/community/emoticons/emotion-11.gif' alt='8)' /> NOT NULL ,<br />[Indexid] [int] NOT NULL ,<br />[Primary] [int] NULL ,<br />[Clustered] [int] NULL ,<br />[Unique] [int] NULL ,<br />[IgnoreDupKey] [int] NULL ,<br />[IgnoreDupRow] [int] NULL ,<br />[NoRecompute] [int] NULL ,<br />[FillFactor] [int] NULL ,<br />[EstRowCount] [bigint] NULL ,<br />[ReservedKB] [bigint] NULL ,<br />[UsedKB] [bigint] NULL ,<br />[KeyNumber] [int] NULL ,<br />[ColumnName] [varchar] (12<img src='/community/emoticons/emotion-11.gif' alt='8)' /> NULL ,<br />[DataType] [varchar] (12<img src='/community/emoticons/emotion-11.gif' alt='8)' /> NULL ,<br />[Precision] [int] NULL ,<br />[Scale] [int] NULL ,<br />[IsComputed] [int] NULL ,<br />[IsNullable] [int] NULL ,<br />[Collation] [varchar] (12<img src='/community/emoticons/emotion-11.gif' alt='8)' /> NULL ) <br /><br />declare db_cursor cursor forward_only for<br /><br />SELECT name, DbID <br />FROM master..sysdatabases<br />WHERE name NOT IN ('northwind', 'pubs')<br />AND (status & 32) &lt;&gt; 32 --loading.<br />AND(status & 64) &lt;&gt; 64 --pre recovery.<br />AND(status & 12<img src='/community/emoticons/emotion-11.gif' alt='8)' /> &lt;&gt; 128 --recovering.<br />AND(status & 256) &lt;&gt; 256 --not recovered.<br />AND(status & 512) &lt;&gt; 512 --Offline<br />AND(status & 3276<img src='/community/emoticons/emotion-11.gif' alt='8)' /> &lt;&gt; 32768 --emergency mode.<br />AND DbID &gt; 4<br /><br />open db_cursor<br /><br />fetch next from db_cursor into @db_name, @DbID<br /><br /><br />while @@FETCH_STATUS = 0<br />begin<br /><br />set @sql_string = ''<br />+'Insert into #tblHistoryIndex '<br />+'select ''' + @db_name + ''' as ''DbName'', '<br />+' o.name as ''TableName'', '<br />+'i.name as ''IndexName'', '<br />+'i.indid as ''Indexid'', '<br />+'CASE WHEN (i.status & 0x800) = 0 THEN 0 ELSE 1 END AS ''Primary'', '<br />+'CASE WHEN (i.status & 0x10) = 0 THEN 0 ELSE 1 END AS ''Clustered'', '<br />+'CASE WHEN (i.status & 0x2) = 0 THEN 0 ELSE 1 END AS ''Unique'', '<br />+'CASE WHEN (i.status & 0x1) = 0 THEN 0 ELSE 1 END AS ''IgnoreDupKey'', '<br />+'CASE WHEN (i.status & 0x4) = 0 THEN 0 ELSE 1 END AS ''IgnoreDupRow'', '<br />+'CASE WHEN (i.status & 0x1000000) = 0 THEN 0 ELSE 1 END AS ''NoRecompute'', '<br />+'i.OrigFillFactor AS ''FillFactor'', '<br />+'i.rowcnt as ''EstRowCount'', '<br />+'i.reserved * cast(8 as bigint) as ''ReservedKB'', '<br />+'i.used * cast(8 as bigint) as ''UsedKB'', '<br />+'k.keyno as ''KeyNumber'', '<br />+'c.name as ''ColumnName'', '<br />+'t.name as ''DataType'', '<br />+'c.xprec as ''Precision'', '<br />+'c.xscale as ''Scale'', '<br />+'c.iscomputed as ''IsComputed'', '<br />+'c.isnullable as ''IsNullable'', '<br />+'c.collation as ''Collation'' '<br />+' '<br />+'from [' + @db_name + ']..sysobjects o with(nolock) '<br />+'inner join [' + @db_name + ']..sysindexes i with(nolock) on o.id = i.id '<br />+'inner join [' + @db_name + ']..sysindexkeys k with(nolock) on i.id = k.id and i.indid = k.indid '<br />+'inner join [' + @db_name + ']..syscolumns c with(nolock) on k.id = c.id and k.colid = c.colid '<br />+'inner join [' + @db_name + ']..systypes t with(nolock) on c.xtype = t.xtype '<br />+' '<br />+'where o.xtype &lt;&gt; ''S'' ' -- Ignore system objects<br />+'and i.name not like ''_wa_sys_%'' ' -- Ignore statistics<br />+' '<br />+'order by '<br />+'o.name, '<br />+'k.indid, '<br />+'k.keyno '<br /><br />execute sp_executesql @sql_string<br /><br />fetch next from db_cursor into @db_name, @DbID<br />end <br /><br />deallocate db_cursor<br /><br />select * from #tblHistoryIndex<br /><br />drop table #tblHistoryIndex</font id="code"></pre id="code">

Share This Page