building index script showing wrong index name

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

building index script showing wrong index name

Author

Topic

Sarat
Constraint Violating Yak Guru

265 Posts

Posted - 2003-05-13 : 12:50:24

Hi,
I am in the process of writing a stored procedure for building indexes so i need to come up with an sqL which returns all indexes on all tables.
I came up with something like this:
SELECT so.name, si.name, si.indid, si.id
FROM sysindexes si, sysobjects so
WHERE si.id = so.id
and so.type = 'U'
and si.indid > 0
-- and so.name = 'PSACTARCHIVE'
it gave me all the rows from sysindexes table but i am also getting key column names (for just two tables) along with the clustered index.
For ex: the table PSACTARCHIVE has only one clustered index called 'PS_PSACTARCHIVE' per sp_helpindex but the sql returns me the following rows:
id.........indid....name
1140719662 1 PS_PSACTARCHIVE
1140719662 2 OPRID
1140719662 3 LASTUPDDTTM
1140719662 4 STEPNO
1140719662 5 PATHNO

should i be building all these indexes or just one which was returned by sp_helpindex. If just one then how do i get a population of clustered and non-clustered indexes?
Thanks,
Sarat.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-05-13 : 13:10:34

Only thing I can think of is that these are statistics. These are normally created as _WA_sys_<colname>....

Unless someone has created statistics manually with just the col names.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Sarat
Constraint Violating Yak Guru

265 Posts

Posted - 2003-05-13 : 16:34:48

I checked but no stats were created manually. Because this is only for very few tables, I am thinking to drop those stats.
Also, is there anyway i can tell when these stats were created?
Thanks,
Sarat.

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-13 : 16:48:30

Look up STATS_DATE in BOL

Brett

8-)

Sarat
Constraint Violating Yak Guru

265 Posts

Posted - 2003-05-13 : 17:09:37

Well, surprisingly the date is NULL. But this table name always shows up in the log file (stating that index was rebuilt on this table) for DBCC DBREINDEX task which i run every week.
But this table doesn't have any data in it so is that the reason the stats date is NULL?
thanks,
sarat.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-13 : 18:47:35

I have a script that does this already, excludes all of the things that you aren't interested in as well. I am not at my desk today, so I will try to post it tomorrow. The script was written for a 7.0 database but it can probably be easily rewritten for 2k or might even already work for 2k.

Tara

Sarat
Constraint Violating Yak Guru

265 Posts

Posted - 2003-05-13 : 19:12:55

Thanks Tara! I really appreciate your help.
Sarat.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-14 : 12:50:19

The below script is used to print out the individual DBCC SHOW_STATISTICS statements, but it can easily be converted for DBCC DBREINDEX. It also is written in such a way that if there are multiple owners of tables (dbo and others) that it will account for that too. This should get you started:


SET NOCOUNT ON

DECLARE @TableName sysname
DECLARE @IndexName sysname

DECLARE show_statistics CURSOR
   FOR 	SELECT T.name, I.name
	FROM SYSOBJECTS T
	INNER JOIN SYSINDEXES I ON T.id = I.id AND T.name <> I.name 
	WHERE T.type = 'U' AND T.status > -1
	AND I.name NOT IN (	SELECT SI.name FROM SYSOBJECTS O
				INNER JOIN SYSINDEXES SI ON O.name = SI.name
				WHERE O.xtype IN ('PK', 'F')	)
	ORDER BY I.name
OPEN show_statistics
FETCH NEXT FROM show_statistics INTO @TableName, @IndexName
WHILE @@FETCH_STATUS = 0
   BEGIN
	IF (SELECT INDEXPROPERTY(OBJECT_ID(@TableName), @IndexName, 'IsStatistics')) = 0
		PRINT 'DBCC SHOW_STATISTICS (' + @TableName + ', ' + @IndexName + ')'
	ELSE
		IF (SELECT INDEXPROPERTY(OBJECT_ID('USER1.' + @TableName), @IndexName, 'IsStatistics')) = 0
		BEGIN
			PRINT 'DBCC SHOW_STATISTICS ([USER1.' + @TableName + '], ' + @IndexName + ')'
		END
		ELSE
			IF (SELECT INDEXPROPERTY(OBJECT_ID('USER2.' + @TableName), @IndexName, 'IsStatistics')) = 0
			BEGIN
				PRINT 'DBCC SHOW_STATISTICS ([USER2.' + @TableName + '], ' + @IndexName + ')'
			END
	FETCH NEXT FROM show_statistics INTO @TableName, @IndexName
   END

CLOSE show_statistics
DEALLOCATE show_statistics

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources