May 3, 2009 at 3:27 pm
So after fiddling around and spending my Sunday afternoon immersing myself in SQL, I believe I have come up with a way for me to find tables without indexes. I've checked a bunch of the ones from my database, and everything works, but I wonder if someone here could test and validate it for me.
The code is:
-- Shows the Table Name, the Index, and the last date the index was updated
SELECT o.id as ID,o.name as ObjName, i.name as IxName, STATS_DATE(i.id, i.indid) as StatsDate
into #Temp
FROM sysobjects o, sysindexes i
WHERE o.id = i.id
and (i.name like 'ix%')
order by o.name
-- A list of all the tables in the Database
select o.id,o.name as ObjName
into #Temp2
from sysobjects o
where o.xtype = 'u'
order by o.name
-- The query below worked fine for the first time. Now, it's only pulling ones where there is
-- a match. Select * from #Temp and #Temp2 and put into Access or Excel...
select distinct t2.objname,t.objname,t.ixname,t.statsdate
from #Temp t, #Temp t2
where t2.id *= t.id
order by t2.objname
drop table #Temp
drop table #Temp2
If anyone has tips on why my final query is not working, I'd appreciate it. Other than that, I've found about 75 tables that don't have an index which I will now need to take back to our developers. Tomorrow will be a fun Monday! 😀
Later
Chris
May 3, 2009 at 4:11 pm
Try this
SELECT o.name FROM
sysobjects o LEFT OUTER JOIN
(SELECT id, indid FROM sysindexes WHERE INDEXPROPERTY(ID, name ,'IsStatistics') = 0) i ON o.id = i.id
WHERE i.indid IS NULL
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 3, 2009 at 5:27 pm
Hey Gail! Thanks for replying...and as always, I have more questions (I continuously hope that by me being annoying and asking all these subsequent questions, someone one day will have all the answers they need in one place! ;-))
I ran your query and got 68 tables back. When I join the two data sets in Access, I get 340 tables without indexes; so, I am now more confused. I've ran through some of the ones I'm getting and they in fact do NOT have indexes. I cannot for the life of me figure out the difference between your query and what I'm doing. That's going to be a side project once I get this other stuff done.
Thanks again for your time,
Chris
Edit: Ok, I just realised my mistake (I think) - the query I have looking at the Indexes only pulled IX_% ones; I wasn't looking at the PK_% indexes.....back to digging into this!
May 4, 2009 at 3:17 am
darth.pathos (5/3/2009)
Edit: Ok, I just realised my mistake (I think) - the query I have looking at the Indexes only pulled IX_% ones; I wasn't looking at the PK_% indexes.....back to digging into this!
Indeed. Note that there's no requirement that index names start IX_. In fact, had that query be run on any DB I designed, it would pick up 0 indexes, as I always name mine idx_....
The query I wrote will consider all indexes, whether they were created with CREATE INDEX or as part of a primary key or unique constraint. It also doesn't care what the names of the indexes are.
Edit: If you want to find tables that have no indexes other than the pk, or no other indexes than the clustered index, it's pretty easy to extend the query. Look up IndexProperty in Books Online.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply