Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Data Profiling with T-SQL Expand / Collapse
Author
Message
Posted Monday, February 25, 2013 12:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 6, 2016 4:54 AM
Points: 105, Visits: 1,026
Comments posted to this topic are about the item Data Profiling with T-SQL
Post #1423484
Posted Monday, February 25, 2013 9:03 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, November 9, 2016 11:32 AM
Points: 453, Visits: 111
Thanks for the excellent article, and the wonderfully useful script. I can already think of a number of instances where something like this could have been huge time savers.

One thing of note, there appears to be a character that got lost in translation along the way.

On Line 114: There appears to be a single quote (') that was incorrectly converted to value '#39;'

WHERE DATA_TYPE IN ('Date', 'DateTime', 'SmallDateTime', #39;DateTime2', 'time')
Post #1423648
Posted Monday, February 25, 2013 9:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 6, 2016 4:54 AM
Points: 105, Visits: 1,026
Thanks Ken, I will see if I can get this corrected.
Glad that you find this script useful.

Adam
Post #1423650
Posted Monday, February 25, 2013 10:12 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 4, 2015 9:38 PM
Points: 42, Visits: 296
Adam, excellent article and my favorite topic.

I have several articles and scripts also on Data Profiling via SSIS(DataFlow script component) and also TSQL.


[url=http://www.sqlservercentral.com/Authors/Articles/Ira_Whiteside/382777/][/url]



Ira Warren Whiteside
Post #1423682
Posted Tuesday, May 21, 2013 3:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 18, 2016 6:40 AM
Points: 6, Visits: 364
Nice script Adam, but I found another typo. About line 257, you'll find the following:

+ QUOTENAME(@COLUMN_NAME) + ')
,NULL
,NLL
,NULL

NLL should be NULL, right?

Thanks for the script though. It works great after I cleaned up the 2 typos.
Post #1455235
Posted Wednesday, May 22, 2013 2:10 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 6, 2016 4:54 AM
Points: 105, Visits: 1,026
Thanks David!
Post #1455356
Posted Tuesday, December 17, 2013 4:08 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, October 11, 2016 4:41 AM
Points: 713, Visits: 3,323
This is great stuff! Thanks very much!

---------------------------------------------------------


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Post #1523597
Posted Thursday, October 9, 2014 1:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 23, 2016 9:42 AM
Points: 4, Visits: 44
when I execute the attached script for my table and for all columns, I am getting the following error:

ErrorNumbe ErrorMessage
207 Invalid column name 'NLL'.

Here are my values:
DECLARE @TABLE_SCHEMA NVARCHAR(128) = 'dbo' -- Your schema here
DECLARE @TABLE_NAME NVARCHAR(128) = 'Customer' -- Your table here
DECLARE @ColumnListIN NVARCHAR(4000) = '' -- Enter a comma-separated list of specific columns
-- to profile, or leave blank for all

Please help me on this error.
Post #1624428
Posted Thursday, December 4, 2014 12:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 11, 2015 8:53 AM
Points: 1, Visits: 47
Note to Author. This is a GREAT script but I also got the NLL error. Appears there's typo in line 257 ... just replace NLL with NULL and it works like a charm
Post #1641614
Posted Thursday, October 6, 2016 3:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 30, 2016 7:37 AM
Points: 14, Visits: 33
This is a really useful script, thanks.

I was using it on a 160,000,000 row table and noticed that I got an arithmetic overflow error. Traced this down to the following part

, AVG(LEN(' + QUOTENAME(@COLUMN_NAME) + '))

I cast the result of the LEN as bigint before calculating the AVG to overcome this:

, AVG(CAST(LEN(' + QUOTENAME(@COLUMN_NAME) + ') AS BIGINT))

Post #1823913
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse