Like a boa constrictor, you just have to keep tightening your grip on it. I've converted hundreds of databases over the years. First I start out with row counts for all tables and I review them in descending count order. Next I define missing primary keys, alternate keys, and foreign keys. Next, convert null columns to not null where possible. Run statistics on each column to really see what is going on. Next, I define cross reference tables to convert their codes into my codes. Then I can start migrating data into my schema.
In other words: its a whole lot of work with no shortcuts.
Make some templates to do quick interrogations, one for each datatype
-- interrogate int column
-- to see if it is oversized, nullable, or standard values
select
COUNT(*) as CountRows,
COUNT(distinct(<colname,,>)) as DistinctCount,
Min(<colname,,>) as MinIntUsed,
MAX(<colname,,>) as MaxIntUsed,
sum(case when <colname,,> is null then 1 else 0 end) as NullsCount,
sum(case when <colname,,> = 0 then 1 else 0 end) as ZeroesCount,
sum(case when <colname,,> > 0 then 1 else 0 end) as PositivesCount,
sum(case when <colname,,> < 0 then 1 else 0 end) as NegativesCount
from
<tablename,,>
-- most frequent values
select top 100
<colname,,>,
COUNT(*) as frequencyCount
from
<tablename,,>
group by
<colname,,>
having
COUNT(*) > 1
order by
COUNT(*) desc