November 25, 2002 at 9:18 am
Hi All,
A friend of mine passed this question on to me. He struggles with an application that uses SQL Server 7.0 as the back end, and the database has over 1500 tables (most of them temporaries). I wish I was kidding. Here's the question he posed, if anyone may know:
"Paul, do you know any way to determine from the sys* tables whether or not a table has indices? I have tried something similar to the following, but it doesn't seem to work. This is the SELECT statement used to create a CURSOR to determine what tables are reindexed. It seems to get *most* of them (the script runs longer before erroring out), but not all of them.
Select Distinct so.Name As TableName, su.Name As Owner
From sysobjects so inner join sysusers su on so.uid=su.uid inner join sysindexes si on si.id=so.id
where so.type='U'
and si.keys is not null
and not ( isnumeric(substring(so.name,2,len(so.name)-1))=1 and left(so.name,1) in ('F','T','S','M') )
and so.name not like 'S%G%'
and so.name not like 'NCOA_%'
order by so.name asc
November 25, 2002 at 9:36 am
In sysindexes, a row will be added for the table with indid > 0. A 1 is clustered, other numbers are nonclustered.
Steve Jones
November 25, 2002 at 9:55 am
Also check for the script in
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=473
It scan all indexes in the db and report a status of fragmentation.
November 25, 2002 at 10:01 am
Further more an indid of 0 or 255 represent the base table. 255 is when there is a text page being used.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 25, 2002 at 10:10 am
Very good information. Thanks to all of you, especially for answering so quickly!
November 25, 2002 at 11:02 am
Also, when looking in sysindexes the StatVersion is 0 for indexes and 1 for stats and impid should be 0 there are some that are -1 that are not indexes on the physical table that I am still not sure of.
December 2, 2002 at 1:12 am
Following script will re-build all existing indexes, can be usefull as well.
USE DATABASE_NAME --Enter the name of the database you want to reindex
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Reindexing ' + @TableName
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
December 2, 2002 at 2:24 am
Hi!
You might want to have a look at that to get the information desired:
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=246
Best regards,
C.
December 3, 2002 at 7:30 pm
quote:
"Paul, do you know any way to determine from the sys* tables whether or not a table has indices?
take apart the sp_help stored procedure where it displays the info for a table. You will see the select statement and other internal info you need to determine the indexes.
system table sysindexes contains the info for each data base.
December 3, 2002 at 7:38 pm
From an article I wrote:
Tables Without Indexing
Tables without indexing are a concern for obvious reasons. One of the things to remember is that primary keys are instituted with indexes, so if a table doesn't have an index, it doesn't have a primary key. What we're concerned with here, however, is more than just tables without primary keys. Tables without any indexes at all are generally warning signs that the developers don't understand the purpose of indexes and thus have not implemented them to aid performance. There are exceptions to this rule. For example, if a table exists as a heap table simply for the purpose of reporting or the like, we may choose not to place an index on the table (including a primary key, simply because an index is created) in order to allow writes to that table to be as quick as possible.
quote:
Now in the script to follow, there is a derived table from the sysindexes table. Basically, we want to create a derived table that doesn't include an indid of either 0 or 255. For indid = 0 refers to the table itself. An indid = 255 refers to a text, ntext, or image column for a particular table. We're looking for clustered and non-clustered indexes, so we need to look at indid between 1 and 254, 1 being a clustered index and > 1 being a non-clustered index. I've taken the liberty of adding an ORDER BY simply for aesthetic reasons:
/* Script to check for tables with no indexes. */
SELECT USER_NAME(so.uid) Owner, so.name [Table]
FROM sysobjects so
LEFT JOIN (SELECT id FROM sysindexes
WHERE indid BETWEEN 1 AND 254) si
ON so.id = si.id
WHERE so.type = 'U'
AND si.id IS NULL
ORDER BY Owner, [Table]
Here's the article: http://www.sqlservercentral.com/columnists/bkelley/designoversight-preliminaryreview.asp
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
December 4, 2002 at 10:24 am
WOW! What a response from everyone! I was hoping one or two would reply. I've got enough here to write a book! This is great. Thanks to all of you for jumping on this one. Huge help!
-PD
December 4, 2002 at 10:56 am
WOW! What a response from everyone! I was hoping one or two would reply. I've got enough here to write a book! This is great. Thanks to all of you for jumping on this one. Huge help!
-PD
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply