Data Profiling with T-SQL

  • Two more errors I ran into:

    (1) Line 217 and 218 read
    ,MAX(' + QUOTENAME(@COLUMN_NAME) + ')
    ,MIN(' + QUOTENAME(@COLUMN_NAME) + ')
    I propose
    ,MAX(CAST(' + QUOTENAME(@COLUMN_NAME) + ' AS NUMERIC(36,2)))
    ,MIN(CAST(' + QUOTENAME(@COLUMN_NAME) + ' AS NUMERIC(36,2)))
    to avoid exceptions.

    (2) On line 558 a SQL query is constructed, and a cursor is created to complete it. If the cursor yields not records, the SQL query is invalid. So it's better to wrap this in an IF statement, something like
    DECLARE @RecordCount int
    SELECT @RecordCount = COUNT(*)
    FROM #ProfileData
    WHERE NoDistinct < @DistinctValuesMinimum

    IF @RecordCount > 0
    BEGIN
    ... -- original code here (lines 558 - 585)
    END ELSE BEGIN
        INSERT INTO #DomainAnalysis (DomainName, DomainElement, DomainCounter) VALUES ('Too many distinct values - No Analysis', NULL, NULL)
    END

  • Hi Adam,

    Nice piece of work. I had started on my own and then thought, I bet someone who is better at code than I has done this already.

    Enjoy the kudos 🙂

    Joe


    Tks,

    JK

  • Fantastic script! It reduces hours-long tasks down to mere seconds. Post the output to Excel and return to your customer (the originator of the data) and they will be impressed!

    One minor thing to note, you have bullet point "Detect Foreign Keys" twice in your list above.

    • This reply was modified 2 years, 1 month ago by  danajshields.

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply