with cte as (
	select distinct
		TableName = OBJECT_NAME(ic.OBJECT_ID),
		ColumnName = COL_NAME(ic.OBJECT_ID,ic.column_id)
	FROM sys.indexes AS i
		INNER JOIN sys.index_columns AS ic
			ON i.OBJECT_ID = ic.OBJECT_ID
				AND i.index_id = ic.index_id
	WHERE i.index_id = 1
),
cte2 as (
	select 
		TableName, 
		ColumnsInClusteredIndex = count(*),
		RowNum = ROW_NUMBER() OVER (ORDER BY TableName)
	from cte
	group by TableName
	),
cte3 as (
	select TotalCount = count(*) from cte2)

select 
	ColumnsInClusteredIndex, 
	TablesCount = count(*),
	TotalCount,
	Percentage = CONVERT(decimal(5, 2), ((count(*)*1.0) / TotalCount) * 100)
from cte2
	cross join cte3
group by ColumnsInClusteredIndex, TotalCount
order by ColumnsInClusteredIndex desc

ColumnsInClusteredIndex	TablesCount	TotalCount	Percentage
16	32	3327	0.96
15	15	3327	0.45
14	23	3327	0.69
13	33	3327	0.99
12	38	3327	1.14
11	66	3327	1.98
10	69	3327	2.07
9	105	3327	3.16
8	165	3327	4.96
7	245	3327	7.36
6	322	3327	9.68
5	458	3327	13.77
4	535	3327	16.08
3	631	3327	18.97
2	407	3327	12.23
1	183	3327	5.50

quote:

---

Originally posted by robvolk

Their developers feel that all data integrity should be done in the application rather than the database. Could be stupidity, arrogance, or the need to make it work on multiple RDBMS products. I'm sure they've looked at other ERP products and saw they didn't have them either, so why break tradition?

---