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”.
devioblog 