Design Errors : PK's with multiple columns on table with identity

-------------------------------------------------------------------------------------------------
-- Purpose: Gets tables that have an Identity together with a PrimaryKey with multiple columns
-- Reasoning: Generally, we expect a Primary Key on the Identity Column only, and if the table is clustered on the PK, this will fatten up the clustered index so fattening up all Indexes,
-- as well as introduce a column into the Clustered index which may undergo change, hence increasing the number of objects needing to be updated if an update occurs on that column.
-- Additionally, You could do identity insert and accidentally insert duplicate ID's into a table where the PK is ill-defined.
-- Author : Doran Mackay
--compatibility : should work on 2005+
-------------------------------------------------------------------------------------------------


select 
S.name+'.'+o.name TableName,I.Name IndexName,count(*) NumberOfColumnsIndexObject
from sys.indexes I
inner join sys.index_columns INC on I.object_id = INC.object_id and I.index_id = INC.index_id 
inner join sys.objects O on O.object_id = I.object_id
inner join sys.schemas S on o.schema_id = s.schema_id
inner join sys.identity_columns IC on i.object_id = IC.object_id 
and IC.column_id in (select column_id from sys.index_columns IC2 where IC.object_id = IC2.Object_id and IC2.index_id = INC.index_id and is_included_column = 0)--Thanks to SeanPearce
where o.type = 'U' --and is_primary_key = 1 
group by S.name+'.'+o.name,I.Name
having count(*) >1
order by 1

--
--TestData
--
CREATE TABLE TestTable1 --Should not be in results
(
   ID        INT IDENTITY(1, 1)
 , sessionid UNIQUEIDENTIFIER
 , CONSTRAINT PK_TestTable1 PRIMARY KEY CLUSTERED ( ID ASC ) ON [PRIMARY]
);

ALTER TABLE TestTable1 ADD   CONSTRAINT CI_Sessionid UNIQUE( Sessionid);
GO

CREATE TABLE TestTable2 --Should be in results
(
   ID        INT IDENTITY(1, 1)
 , sessionid UNIQUEIDENTIFIER
 , CONSTRAINT PK_TestTable2 PRIMARY KEY CLUSTERED ( ID ASC, sessionid ASC) ON [PRIMARY]
);

CREATE TABLE TestTable3 --Should not be in results
(
   ID        INT IDENTITY(1, 1)
 , sessionid UNIQUEIDENTIFIER
 , CONSTRAINT PK_TestTable3 PRIMARY KEY NONCLUSTERED ( ID ASC ) ON [PRIMARY]
);
create unique index UIX_TestTable3 on TestTable3 (Sessionid) where Sessionid is not null

CREATE TABLE TestTable4 --Should not be in results
(
   ID        INT IDENTITY(1, 1)
 , sessionid UNIQUEIDENTIFIER
 , CONSTRAINT PK_TestTable4 PRIMARY KEY NONCLUSTERED ( ID ASC ) ON [PRIMARY]
);
create unique index UIX_TestTable4 on TestTable3 (Sessionid)

CREATE TABLE TestTable5 --Should be in results
(
   ID        INT IDENTITY(1, 1)
 , sessionid UNIQUEIDENTIFIER
 , CONSTRAINT PK_TestTable5 PRIMARY KEY NONCLUSTERED ( ID ASC ) ON [PRIMARY]
);
create unique index UIX_TestTable5 on TestTable5 (Sessionid,ID)

CREATE TABLE TestTable6 --Should be results
(
   ID        INT IDENTITY(1, 1)
 , sessionid UNIQUEIDENTIFIER
 , CONSTRAINT PK_TestTable6 PRIMARY KEY CLUSTERED ( ID ASC ) ON [PRIMARY]
);

ALTER TABLE TestTable6 ADD   CONSTRAINT CI_Sessionid6 UNIQUE( ID,Sessionid);
GO


CREATE TABLE TestTable7 --Should not be in results since ID is not in the PK
(
   ID        INT IDENTITY(1, 1)
 , sessionid UNIQUEIDENTIFIER, MyRandomColumn varchar(10)
 , CONSTRAINT PK_TestTable7 PRIMARY KEY CLUSTERED ( sessionid,MyRandomColumn ) ON [PRIMARY]
);
CREATE TABLE TestTable8 --Should not be in results since ID is not in the UC  
(
   ID        INT IDENTITY(1, 1)
 , sessionid UNIQUEIDENTIFIER, MyRandomColumn varchar(10) 
);

ALTER TABLE TestTable8 ADD   CONSTRAINT CI_Sessionid8 UNIQUE( sessionid,MyRandomColumn);
create nonclustered index IX_test on TestTable8(sessionid,MyRandomColumn)include (ID) --Test case suggested by SeanPearce
GO
--END TestData
--Results 
--dbo.TestTable2PK_TestTable2
--dbo.TestTable5UIX_TestTable5
--dbo.TestTable6CI_Sessionid6
--End Results