Skip to main content

Columns Inconsintency Check

Another hopefully useful script  from my toolbox.

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

Comments

  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:

    ;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];

    ReplyDelete
  2. Thanks, I've added a filter on system and diagram objects (using extended properties).

    ReplyDelete

Post a Comment

Popular posts from this blog

SqlDbAid DBA tool

Latest version (v2.6.0.0 - 2017-09-03) SqlDbAid.zip
SqlDbAid is an intuitive standalone application that will enable you to easily script database items and data.


SqlDbAid is free for personal and commercial usage.

Main Features SQL Server 2005+ supportText search inside code definition with highlitghtingDBA reports (missing indexes, missing foreign key indexes, indexes status, top queries, table MBytes)One file per object or single file scripts (tables, views, triggers, procedures, functions, indexes...)Select, Insert and Update scriptsTable data insert script creationTable/View data exportOffline database compare tool
Requirements .Net Framework 2.0+ For questions and suggestions write to Miken.

Database Permissions Script

DatabasePermissions.zip

This time I want to share a simple script to extract database permissions.
The code is based on sys.database_persmissions system view.
It basically contains the object ids, principal ids and related permissions (GRANT, DENY, ...).


SELECTCASEWHEN P.class IN(0,3)THEN P.class_desc ELSE O.type_desc END type_desc,CASEWHEN P.class IN(0,3)THEN''ELSE SCHEMA_NAME(O.schema_id)END object_schema,CASEWHEN P.class =3THEN SCHEMA_NAME(P.major_id)WHEN P.class =1THEN OBJECT_NAME(P.major_id)ELSE''END object_name, ISNULL(C.name,'') column_name, P.class_desc, USER_NAME(P.grantee_principal_id) grantee, P.permission_name, P.state_desc, USER_NAME(P.grantor_principal_id) grantor,CASE P.state WHEN'W'THEN'GRANT'ELSE P.state_desc END+' '+CASEWHEN P.class =0THEN P.permission_name +' TO '+ QUOTENAME(USER_NAME(P.grantee_principal_id)) COLLATE database_default WHEN P.class =3THEN P.permission_name +' ON SCHEMA::'+ QUOTE…