Recently I had to check a quite denormalized database at the end of its development phase.
Frequently data types, length and other field attributes change during development, so I wrote a simple script that highlights possible mistakes (same column name but different definition).
UPDATE: system objects skipped
;WITH cte AS ( SELECT c.NAME cl_name ,Schema_name(t.schema_id) + '.' + t.NAME tb_name ,p.NAME [type] ,c.max_length ,c.scale ,c.PRECISION FROM sys.tables t INNER JOIN sys.columns c ON c.object_id = t.object_id INNER JOIN sys.types p ON p.user_type_id = c.user_type_id AND p.system_type_id = c.system_type_id WHERE NOT ( EXISTS (SELECT 1 FROM sys.extended_properties EP WHERE EP.major_id = t.object_id AND EP.name = 'microsoft_database_tools_support') OR t.is_ms_shipped = 1 ) ) SELECT DISTINCT x.* FROM cte x INNER JOIN cte y ON y.tb_name != x.tb_name AND y.cl_name = x.cl_name AND ( y.max_length != x.max_length OR y.PRECISION != x.PRECISION OR y.scale != x.scale OR y.[type] != x.[type] ) ORDER BY 1
The script take into account the system objects as well, which can produce incorrect result (or misleading information, as the user and systems objects are compared). Suggest to modify the script:
ReplyDelete;WITH cte AS (
SELECT
tSO.[object_id] AS [object_id]
,tSC.[name] AS [cl_name]
,tST.[name] AS [type]
,tSC.[max_length] AS [max_length]
,tSC.[scale] AS [scale]
,tSC.[precision] AS [precision]
FROM
[sys].[objects] tSO
INNER JOIN [sys].[columns] AS tSC ON
tSC.[object_id] = tSO.[object_id]
INNER JOIN [sys].[types] AS tST ON
tST.[user_type_id] = tSC.[user_type_id]
AND tST.[system_type_id] = tSC.[system_type_id]
WHERE
tSO.[type_desc] IN (
N'USER_TABLE'
)
AND OBJECTPROPERTY(tSO.[object_id], N'IsMSShipped') = 0
)
SELECT DISTINCT
x.[cl_name] AS [ObjectColumnName]
,tSS.[name] AS [SchemaName]
,tSO.[name] AS [ObjectName]
,tSO.[type_desc] AS [ObjectType]
,tSO.[create_date] AS [ObjectCreateDate]
,x.[type] AS [ObjectColumnType]
,x.[max_length] AS [ObjectColumnTypeMaxLength]
,x.[scale] AS [ObjectColumnTypeScale]
,x.[precision] AS [ObjectColumnTypePrecision]
FROM
cte AS x
INNER JOIN cte AS y ON
y.[object_id] <> x.[object_id]
AND y.[cl_name] = x.[cl_name]
AND (
y.[max_length] <> x.[max_length]
OR y.[precision] <> x.[precision]
OR y.[scale] <> x.[scale]
OR y.[type] <> x.[type]
)
INNER JOIN [sys].[objects] tSO ON
tSO.[object_id] = x.[object_id]
INNER JOIN [sys].[schemas] tSS ON
tSS.[schema_id] = tSO.[schema_id]
ORDER BY
x.[cl_name]
,tSS.[name]
,tSO.[name];
Thanks, I've added a filter on system and diagram objects (using extended properties).
ReplyDelete