Data Profiling with T-SQL

• mschluper

  Grasshopper

  Points: 19

  More actions

  January 17, 2019 at 7:16 am

  #2019204

  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

• Joe Kelly-8908

  SSC-Addicted

  Points: 492

  More actions

  February 23, 2021 at 5:23 pm

  #3849143

  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
  

• danajshields

  Grasshopper

  Points: 12

  More actions

  February 1, 2022 at 2:10 pm

  #3979599

  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, 10 months ago by  danajshields.