Checking Consistency of Database Column Data Types

Working on a database project, I noticed that column data types where not specified consistently.

For example, one table might have a column “Name” specified as nvarchar(50), and another one as nvarchar(100). Or a column “Description” specified nvarchar(500) in one table, and nvarchar(max) in others.

To find the inconsistencies, I wrote a script which finds inconsistently typed table columns, and then writes out the column specifications:

with cte as (
  select c.name, t.name tname, c.max_length, count(*) C
  from sys.columns c
  inner join sys.objects o on o.object_id = c.object_id
  inner join sys.types t on c.user_type_id = t.user_type_id
  where o.type = 'U'
  and o.name not in ('sysdiagrams', 'dtproperties')
  group by c.name, t.name, c.max_length
)

The first CTE retrieves all unique combinations of column names and type specifications

, cols as (
  select name from cte
  group by name
  having count(*) > 1
)

The second CTE, which operates on the result of the first CTE, filters out the column names with different type specifications

select c.name, o.name, t.name tname,
  case 
    when t.name = 'nvarchar' and c.max_length = -1 then null
    when t.name = 'nvarchar' then c.max_length / 2
    when t.name = 'varchar' then c.max_length 
    else null
  end max_length
from sys.columns c
inner join cols on c.name = cols.name
inner join sys.objects o on o.object_id = c.object_id
inner join sys.types t on c.user_type_id = t.user_type_id
where o.type = 'U'
and o.name not in ('sysdiagrams', 'dtproperties')
order by c.name, o.name

And finally, we select all table names and column specifications along with their effective data type length.

Note that we need to explicitly exclude the built-in tables “sysdiagrams” and “dtproperties”.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: