script to find tables in the database without PK or indexes
-
I am trying to find out all the tables in the user databases without any index or primary keys. Does anyone have any suggestion for scripting or does anyone have any script that provides the info?
Thanks
-
I thought something like this would work:
select * from sysobjects a
where (not exists
(select * from sysindexes b where a.id = b.id))
and xtype = 'U'
order by name
But I find that my tables that don't have any indexes still have entries in sysindexes and sisindexkeys !?!? Any ideas as to why ?
Edit: I found that the table with no indexes, but records in sysindexes, does have indexes in the original prod database on another server. It's probable that my developement db was created from a restore of prod, so at the time, there were indexes. Since there are no longer indexes, I need to find a way to update sysindexes & sysindexkeys to reflect reality. I tired UPDATE STATISTICS and DBCC UPDATEUSAGE, but it had no effect
Thoughts ?
-
SQL Server uses SysIndexes for statistics, so it doesn't just contain user-created indexes.
The query needs to exclude the indexes where the name is prefixed with "_WA_Sys_".
select * from sysobjects a
where (not exists
(select * from sysindexes b where a.id = b.id and name not like "_WA_Sys_%' ))
and xtype = 'U'
order by name
-
I was wondering what those "_WA_Sys_%" ones were all about. I omitted them, but didn't know if that was a reliable thing to do for accurate, long term results.
I still have a non "_WA_Sys_%" record in sysindexes that's preventing me from getting expected results. In that case, indid = 0, so when I run this, my results look a lot better:
select * from sysobjects a
where (not exists
(select * from sysindexes b where a.id = b.id and name not like '_WA_Sys_%' and indid <> '0' ))
and xtype = 'U'
order by name
-
Warning : this query does direct access to system-objects ! They may change without warning with hotfix, sp, ...
This is what I use
print 'Server : '+@@servername + ' Database : '+db_name()
print '--------------------------------------------------'
select t.name as Tbname, t.crdate
from
(
select *
from sysobjects
where xtype = 'u'
) t
left join
(
select *
from sysobjects
where xtype = 'pk'
) k
on t.id = k.parent_obj
where k.name is null
order by tbname
-- To find tb without clustering indexes I use :
print 'Server : '+@@servername + ' Database : '+db_name()
print '-------------------------------------------------------------------------'
Print '-- tables with indexes , but without clustering key and/or primary key --'
print '-------------------------------------------------------------------------'
declare @des1 varchar(35), -- 35 matches spt_values
@des2 varchar(35),
@des4 varchar(35),
@des32 varchar(35),
@des64 varchar(35),
@des2048 varchar(35),
@des4096 varchar(35),
@des8388608 varchar(35),
@des16777216 varchar(35)
select @des1 = name from master.dbo.spt_values where type = 'I' and number = 1
select @des2 = name from master.dbo.spt_values where type = 'I' and number = 2
select @des4 = name from master.dbo.spt_values where type = 'I' and number = 4
select @des32 = name from master.dbo.spt_values where type = 'I' and number = 32
select @des64 = name from master.dbo.spt_values where type = 'I' and number = 64
select @des2048 = name from master.dbo.spt_values where type = 'I' and number = 2048
select @des4096 = name from master.dbo.spt_values where type = 'I' and number = 4096
select @des8388608 = name from master.dbo.spt_values where type = 'I' and number = 8388608
select @des16777216 = name from master.dbo.spt_values where type = 'I' and number = 16777216
Select CAST( u.name+'.'+o.name AS varchar) as TbName
, cast( case x.name when clx.name then '#' when pko.name then '*' else ' ' end as char(1)) as 'PK'
, x.indid as Ix_Id
, cast(x.name as varchar) as ixname
-- , cast(clx.name as varchar) as ClX_name
-- , cast(pko.name as varchar(50)) as PK_name
, cast(count(*) as smallint) as xCols
, convert(varchar(25), --bits 16 off, 1, 2, 16777216 on, located on group
case when (x.status & 16)<>0 then 'cl' else 'nc' end -- 'clustered' of 'nonclustered'
+ case when (x.status & 1)<>0 then ', '+@des1 else '' end
+ case when (x.status & 2)<>0 then ', '+@des2 else '' end
+ case when (x.status & 4)<>0 then ', '+@des4 else '' end
-- + case when (x.status & 64)<>0 then ', '+@des64 else case when (x.status & 32)<>0 then ', '+@des32 else '' end end
-- + case when (x.status & 2048)<>0 then ', '+@des2048 else '' end
-- + case when (x.status & 4096)<>0 then ', '+@des4096 else '' end
-- + case when (x.status & 8388608)<>0 then ', '+@des8388608 else '' end
-- + case when (x.status & 16777216)<>0 then ', '+@des16777216 else '' end
) as index_descr
--, o.id as id
--, x.indid as ix_id
--, x.rows as rijen
--, L.rowlength as rijlengte
from sysobjects o
inner join sysusers u
on o.uid = u.uid
inner join
(select id, sum(length) as rowlength
from syscolumns
group by id ) L
on o.id = L.id
left join
sysindexes x
on o.id = x.id
left join
sysindexkeys xk
on x.id = xk.id
and x.indid = xk.indid
left join
sysindexes Clx
on o.id = Clx.id
and Clx.indid = 1 -- only clustered indexes
left join sysobjects pko
on pko.xtype = 'pk'
and pko.parent_obj = o.id
where o.xtype = 'u'
-- and x.rows > 100 -- more than 100 rows
and x.indid <> 1 -- only non clustering
and Clx.indid is null -- only with no cluster ix
-- and x.keys is not null
and x.name not like '_w%'
and o.name <> 'dtproperties'
-- and ( x.name = pko.name or pko.name is null ) -- enkel de primary key indexen selecteren
and x.firstiam <> 0x000000000000 -- enkel indexen
group by u.name , o.name , L.rowlength , x.name
, pko.name , x.status
, o.id , x.indid, x.rows
, clx.name
-- having count(*) = 1
order by TbName, pk desc, ixname
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
The following simple query might solve your problem,
Select Name
From sysObjects
Where xtype = 'U'
and ObjectProperty(id,'isIndexed') = 0
and ObjectProperty(id,'TableHasPrimaryKey') = 0
-
Hi
with the information_schema you can find what you need too:
select * from information_schema.tables where table_name not in
(select distinct table_name from information_schema.KEY_COLUMN_USAGE)
with this you can find those tables which don't have a unique index e.g.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply