Solved

Microsoft sql server "frequency report"

Posted on 2014-07-22
4
396 Views
Last Modified: 2016-02-13
Hello there!

I've been doing a lot of "initial analysis" on data files we receive from external entities.  Part of what I do with that data is to look at it and see what we have and describe it.  

I like to work in SSMS but find myself often using other tools to run "frequencies" (e.g., SPSS or AgileDataSoftware FileQuery").  Is there a function in SSMS that would allow me to run frequencies on data without having to define each field etc?  Something I could quickly run against a table that would tell me for each field in the table the count of distinct values, count of "empty" values, min and max length, types of characters (letters, digits, whitespace, special characters), and then 10 or 20 sample values?  

The output would be best in a report format.  

I feel like I'm looking for a plugin or something but I'm not really sure.  

TIA!
0
Comment
Question by:ttist25
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 17

Accepted Solution

by:
Barry Cunney earned 500 total points
ID: 40213970
Hi
In SSIS there is a Data Profiler Task.
You can add this task to an SSIS worflow and execute it on a specified table.

It will produce an XML file which you can open with the Data Profile Viewer tool which is in the Integration Services folder in the SQL Server program group.

It can display column length, pattern, null ratio statistics and does so in a 'dashboard' type view

Please let me know if you would like more detail on this SSIS Data Profiler Task
0
 
LVL 1

Author Closing Comment

by:ttist25
ID: 40215248
Hi Barry!

Thanks for the response.  Although I don't have time to figure out how to do it at the moment, it sounds like this is what I'm looking for.  I'm not sure when I'll have the time to look into workflows and how to add tasks but I didn't want to leave you hanging while I figured that out so, I awarded the points.  

If you have time and could do a quick list of steps that'd be great but definitely not necessary as it's outside the scope of the original question.  

THanks again!
0
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 40219805
Hi
Please see print screen attached which depicts and existing such task in SSIS.
I would advise maybe doing a few basic SSIS tutorials first if you are not familiar with SSIS.

You add the 'Data Profiling Task' from the toolbox to the main pane and add 2 connection managers, one for the DB/table that contains your data and the other for the profiling results XML file. You can then right-click on the  'Data Profiling Task' and choose Execute
SSIS-DataProfiler.png
0
 
LVL 1

Author Comment

by:ttist25
ID: 40220438
Thanks Barry!

Have a great weekend!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question