use AdventureWorks2008R2
go

-- Columns that may want to be part of a FK. Exist in other tables.
select 
	t.name as TableName
	,c.name as ColumnName
from sys.columns c
inner join sys.tables t
	on c.object_id=t.object_id
inner join
(
	select distinct
		schema_name(t.schema_id)+'.'+object_name(t.object_id) as TableName
		,c.name as ColumnName
	from sys.columns c
	inner join sys.tables t
	on c.object_id=t.object_id
	inner join sys.index_columns ic
	on ic.object_id=c.object_id
	and c.column_id=ic.column_id
	inner join sys.indexes i
	on i.index_id=ic.index_id
	and i.object_id=ic.object_id
	where i.is_primary_key=1
	or i.is_unique_constraint=1
) as keys
on keys.ColumnName=c.name
	and keys.TableName<>t.name
left outer join sys.foreign_key_columns fkc
	on fkc.parent_object_id=c.object_id
	and fkc.parent_column_id=c.column_id
left outer join sys.index_columns ic
	on ic.object_id=c.object_id
	and ic.column_id=c.column_id
left outer join sys.indexes i
	on i.object_id=ic.object_id
	and i.index_id=ic.index_id
where fkc.parent_column_id is null
	and (i.is_primary_key=0
	or i.is_primary_key is null)
group by t.name, c.name
order by t.name, c.name

-- Find tables without PKs, or that are heaps
select
	SCHEMA_NAME(t.schema_id)+'.'+t.name as TableName
	,'Table is a heap' as Description
from sys.tables t
inner join sys.indexes i
on t.object_id=i.object_id
where i.type_desc ='HEAP'
union all
select
	SCHEMA_NAME(t.schema_id)+'.'+t.name
	,'Has no primary key' as Description
from sys.tables t
inner join sys.indexes i
on t.object_id=i.object_id
group by SCHEMA_NAME(t.schema_id)+'.'+t.name
having SUM(case when i.is_primary_key=1 then 1 else 0 end)=0 --no PK

-- FKs without indexes
select
	fk.name as ForeignKeyName
	,SCHEMA_NAME(parent_t.schema_id)+'.'+parent_t.name as ParentTableName
	,SCHEMA_NAME(reference_t.schema_id)+'.'+reference_t.name as ReferencedTableName --is the table to check
	,reference_c.name as ReferencedColumnName
	,SUM(case 
			when ic.key_ordinal is not null 
			then 1 else 0 end)
		as ReferencedColumnIndexCount
from sys.foreign_keys fk
inner join sys.foreign_key_columns fkc
	on fk.parent_object_id=fkc.parent_object_id
	and fk.referenced_object_id=fkc.referenced_object_id
inner join sys.tables parent_t
	on parent_t.object_id=fkc.parent_object_id
inner join sys.tables reference_t
	on reference_t.object_id=fkc.referenced_object_id
left outer join sys.columns reference_c
	on reference_c.object_id=fkc.referenced_object_id
	and reference_c.column_id=fkc.referenced_column_id
left outer join sys.index_columns ic
	on ic.object_id=fkc.referenced_object_id
	and ic.column_id=fkc.referenced_column_id
	and ic.is_included_column=0
	and ic.key_ordinal=1 --we're only interested in indexes where the first column in the index is the FK column
left outer join sys.indexes i
	on i.index_id=ic.index_id
	and i.object_id=ic.object_id
	and i.is_disabled=0 --ignore disabled indexes
	and i.filter_definition is null --ignore filtered indexes
group by 
	fk.name
	,SCHEMA_NAME(parent_t.schema_id)+'.'+parent_t.name
	,SCHEMA_NAME(reference_t.schema_id)+'.'+reference_t.name
	,reference_c.name
order by ReferencedColumnIndexCount asc

-- Find FKs with datatype mismatches
select
	fk.name as ForeignKeyName
	,SCHEMA_NAME(parent_t.schema_id)+'.'+parent_t.name as TableName
	,parent_c.name as ColumnName
	,SCHEMA_NAME(reference_t.schema_id)+'.'+reference_t.name as ReferencedTableName
	,reference_c.name as ReferencedColumnName
from sys.foreign_keys fk
inner join sys.foreign_key_columns fkc
	on fk.parent_object_id=fkc.parent_object_id
	and fk.referenced_object_id=fkc.referenced_object_id
inner join sys.tables parent_t
	on parent_t.object_id=fkc.parent_object_id
inner join sys.tables reference_t
	on reference_t.object_id=fkc.referenced_object_id
inner join sys.columns reference_c
	on reference_c.object_id=fkc.referenced_object_id
	and reference_c.column_id=fkc.referenced_column_id
inner join sys.columns parent_c
	on parent_c.column_id=fkc.parent_column_id
	and parent_c.object_id=fkc.parent_object_id
inner join sys.types parent_tp
on parent_tp.user_type_id=parent_c.user_type_id
inner join sys.types reference_tp
on reference_tp.user_type_id=reference_c.user_type_id
where parent_c.user_type_id<>reference_c.user_type_id --type mismatches
or parent_c.max_length<>reference_c.max_length
or parent_c.precision<>reference_c.precision
or parent_c.scale<>reference_c.scale
or coalesce(parent_c.collation_name,'')<>coalesce(reference_c.collation_name,'')

	
--Get a list of views and sprocs per stored procedure
select
	s.name+'.'+t.name as TableName
	,(
	select COUNT(*)
	from sys.views v
	inner join sys.sql_modules m
		on m.object_id=v.object_id
	where m.definition like '%'+t.name+'%'
	) 
	as ReferencingViewCount
	,(
	select COUNT(*)
	from sys.objects o
	inner join sys.sql_modules m
		on m.object_id=o.object_id
	where m.definition like '%'+t.name+'%'
		and o.type in ('FN','TF')
	) as ReferencingFunctionCount
	,
	(
	select COUNT(*)
	from sys.objects o
	inner join sys.sql_modules m
		on m.object_id=o.object_id
	where m.definition like '%'+t.name+'%'
		and o.type='P' --sprocs
	) as ReferencingStoredProcedureCount
from sys.tables t
inner join sys.schemas s
on t.schema_id=s.schema_id