September 2, 2008 at 10:50 am
How can I find which tables in my database have clustered indexes and which don't?
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
September 2, 2008 at 11:26 am
Hello,
You can find which objects have Clustered Indexes from sysindexes e.g. Select object_name(id), name From sysindexes Where indid = 1
(Please note that this query returns system objects as well as user tables)
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
September 2, 2008 at 11:34 am
You can get just the user tables that have clustered indexes with this:
Select object_name(id), name From sysobjects Where xtype = 'u' AND objectproperty(id,'TableHasClustIndex')=1
and the user tables that don't have a clustered index with this
Select object_name(id), name From sysobjects Where xtype = 'u' AND objectproperty(id,'TableHasClustIndex')=0
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
September 3, 2008 at 3:42 am
Hello,
I noticed that GilaMonster's scripts need a slight amendment to the Where clause along the lines below, when run on a case sensitive database - yes, some do still exist!
Upper(xtype) = 'U'
For what it is worth here is my script with filtering added to return only user tables. It does have the slight benefit that it returns the Clustered Index name:-
-- User Tables With a Clustered Index
Select
object_name(SI.id) As ObjectName,
SI.name As IndexName,
SI.id As ObjectId
From
sysindexes SI
Where
(SI.indid = 1) And
(SI.id In (Select id From sysobjects Where (Upper(xtype) = 'U')))
Go
And this is the reverse:-
-- User Tables Without a Clustered Index
Select
object_name(SO.id) As ObjectName,
SO.id As ObjectId
From
sysobjects SO
Where
(Upper(xtype) = 'U') And
(SO.Id Not In(Select id From sysindexes Where (indid = 1)))
Go
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
September 3, 2008 at 4:15 am
Hello again,
Below is a more general purpose, instance-wide script that I once downloaded. Unfortunately, I can’t remember where from, so I'm unable to give the author his or her credit.
Regards,
John Marsh
declare @dbname varchar(100)
declare @sqlstring varchar(3000)
select @dbname = max(name) from master.dbo.sysdatabases
where dbid > 4
Create table #id (DatabaseName varchar(100), TableName varchar(100), WhatIsMissing varchar(100))
while @dbname is not null
begin
set @sqlstring = 'use '+ @dbname + ' insert into #id select ' + char(39) + @dbname +char(39)+ ', o.Table_Name, ''No primary key''
FROM
(select name as Table_Name from sysobjects where xtype = ''U'') o
LEFT OUTER JOIN
(
SELECT Table_Name FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where CONSTRAINT_TYPE = ''PRIMARY KEY'') pk
on pk.Table_Name = o.Table_Name
WHERE pk.Table_Name is null and o.Table_Name not like ''zz%'''
exec(@sqlstring)
set @sqlstring = ' insert into #id select ' + char(39) + @dbname +char(39)+ ', o.name, ''No clustered index''
from sysobjects o
left outer join (select distinct object_name(id)
as TableName from sysindexes where indid = 1) t
on t.TableName = o.name
where o.xtype = ''u'' and t.TableName is null'
exec(@sqlstring)
set @sqlstring = ' insert into #id select ' + char(39) + @dbname +char(39)+ ', name, ''No index''
from sysobjects where xtype = ''u'' and
id not in (select id from sysindexes where name not like ''_WA%'' and indid > 0)'
exec(@sqlstring)
select @dbname = max(name) from master.dbo.sysdatabases
where dbid > 4 and name < @dbname
end
select * from #id
drop table #id
www.sql.lu
SQL Server Luxembourg User Group
September 3, 2008 at 6:56 am
Thanks for all the great info.
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
September 3, 2008 at 8:47 am
Now with all that said I found about 109 out of 298 tables that lack a Clustered Index. I thought about going through the 109 and adding the primary key as the clustered index which worked on a couple of them but some of the major tables came back with the following error:
Server: Msg 1925, Level 16, State 2
Cannot convert a clustered index to a nonclustered index using the DROP_EXISTING option.
Which I am guessing has to do with the fact that that particular index is used in a foreign key constraint.
What would you suggest I do to get these clustered indexes in place?
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
September 3, 2008 at 9:43 am
Hello,
Did you try to Drop the Non-Clustered Index and then Add the Clustered Index e.g.
ALTER TABLE dbo.Table1
DROP CONSTRAINT PK_Table1
Go
ALTER TABLE dbo. Table1 ADD CONSTRAINT
PK_ Table1 PRIMARY KEY CLUSTERED (Field1)
Regards,
John Marsh
PS: You can not drop a Primary Key Constraint if it is referenced in a Foreign Key Constraint. You first need to drop the Foreign Key Constraint.
www.sql.lu
SQL Server Luxembourg User Group
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply