Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server - Script to find all clustered indexes built on a specific data type

I'm trying to find all clustered indexes built on columns of the data type uniqueIdentifier. Obviously this is a terrible choice for a clustered index, and I'm trying to find all of them and remove them. The script that I've written so far returns all clustered indexes for every table that has a uniqueIdentifier on it. Please help. Here is the script:

select distinct object_name(i.object_id) AS tablename, i.name AS indexname, i.type_desc     as type 
from sys.indexes i
join sys.index_columns ic on ic.object_id = i.object_id and ic.index_id = i.index_id
join sys.columns c on c.column_id = ic.index_column_id 
join sys.types t on t.system_type_id = c.system_type_id
join sys.objects o on o.object_id = i.object_id
where t.name = 'uniqueidentifier'
and i.type_desc = 'clustered'
and object_name(i.object_id) not like 'sys%'
like image 725
user1825469 Avatar asked Nov 15 '12 02:11

user1825469


1 Answers

select o.name objectname, i.name indexname, c.name as columnname
from sys.objects o
join sys.indexes i on i.object_id = o.object_id
join sys.index_columns ic on ic.index_id = i.index_id and ic.object_id = i.object_id
join sys.columns c on c.object_id = o.object_id and c.column_id = ic.column_id
join sys.types t on c.system_type_id = t.system_type_id
where o.is_ms_shipped = 0
  and i.type_desc = 'CLUSTERED'
  and t.name = 'uniqueidentifier'
order by o.name, i.name
like image 191
RichardTheKiwi Avatar answered Oct 20 '22 22:10

RichardTheKiwi