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