June 11, 2009 at 4:02 pm
Need a SQL script for the following:
To get the stored procedure started with sp_
To get the list of tables without clustered index
To get list of tables without indexing
Tables and indexed views that have duplicate index
Any links ?
June 11, 2009 at 4:44 pm
Here you go.
--To get the stored procedure started with sp_
select name from sys.sysobjects where name like '%sp_%' and xtype='p'
--To get the list of tables without clustered index
SELECT DISTINCT
= OBJECT_NAME(OBJECT_ID)
FROM SYS.INDEXES
WHERE INDEX_ID = 0
AND OBJECTPROPERTY(OBJECT_ID,'IsUserTable') = 1
ORDER BY
GO
-- To get list of tables without indexing
select 'table_name'=object_name(i.id)
from sys.sysindexes i
,sys.filegroups f ,sys.database_files d
,sys.data_spaces s
where objectproperty(i.id,'IsUserTable') = 1
and f.data_space_id = i.groupid
and f.data_space_id = d.data_space_id
and f.data_space_id = s.data_space_id
and i.indid = 0
order by f.name,object_name(i.id),groupid
-- To get list of tables without indexing
with indexcols as
(
select object_id as id, index_id as indid, name,
(select case keyno when 0 then NULL else colid end as [data()]
from sys.sysindexkeys as k
where k.id = i.object_id
and k.indid = i.index_id
order by keyno, colid
for xml path('')) as cols,
(select case keyno when 0 then colid else NULL end as [data()]
from sys.sysindexkeys as k
where k.id = i.object_id
and k.indid = i.index_id
order by colid
for xml path('')) as inc
from sys.indexes as i
)
select
object_schema_name(c1.id) + '.' + object_name(c1.id) as 'table',
c1.name as 'index',
c2.name as 'exactduplicate'
from indexcols as c1
join indexcols as c2
on c1.id = c2.id
and c1.indid < c2.indid
and c1.cols = c2.cols
and c1.inc = c2.inc;
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply