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