Bill Talada (7/3/2014)

---

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