Query to find list of all primary keys and check if they are clustered and if they are on uniqueidentifier column

Question

Dear Experts,

I have this requirement to query all the tables in my database to check if the tables have clustered indexes on the primary key and if the key column is an uniqueidentifier. I prepared this script below

select o.name as TableName
, i.name as IndexName,c.name as ColumnName, i.type_desc,i.is_primary_key,TYPE_NAME(c.system_type_id) as Datatype
from sys.objects o
inner join sys.indexes i on i.object_id = o.object_id
inner join sys.columns c on c.object_id=o.object_id
where o.is_ms_shipped = 0
and i.index_id =1 and i.is_primary_key=1 and c.system_type_id in ('36')
--order by o.name
-- , i.index_id;

It appears to work but when I change the c.system_type_id to something like 61 it works too.
Is there anyway I can fine tune above query please? I don't want it to output any rows if I put anything other than 36 as system_type_id.

Could you please share your inputs?

Answer 1

Your query needs to include to sys.indexed_columns. Note that this will list the table more than once for tables with composite primary keys containing more than one uniqueidentifer column. Add DISTINCT to return the table only only once in that case.

SELECT
      o.name AS TableName
    , i.name AS IndexName
    , c.name AS ColumnName
    , i.type_desc
    , i.is_primary_key
    , TYPE_NAME(c.system_type_id) as Datatype
FROM sys.objects o
INNER JOIN sys.indexes i ON i.object_id = o.object_id
INNER JOIN sys.index_columns ic ON ic.object_id=i.object_id AND ic.index_id = i.index_id
INNER JOIN sys.columns c ON c.object_id=ic.object_id AND c.column_id = ic.column_id
WHERE
    o.is_ms_shipped = 0
    AND i.index_id = 1
    AND i.is_primary_key = 1
    AND c.system_type_id in ('36')
ORDER BY
    o.name;

Answer 2

Awesome, works perfect.

By the by, how do we know to query sys.indexed_columns

Is there any easy way to identify this?