Maybe a Crazy Idea? Build a Custom Dynamic Summary of a Table..

[kjv1611]

Maybe a Crazy Idea? Build a Custom Dynamic Summary of a Table..

What I'd like to do is be able to feed a table name into my procedure, and then have it search through the fields in the table, and basically more or less give me these results:

1. Whether the column contains ALL NULLS or not
2. What the format of the column is
3. If the column is numeric, then the Max, Min, Median, Average
4. If the column is varchar or nvarchar, then perhaps the values from the top 2 or 3 non-null records.
5. Any oddities I can think of to be added in later, perhaps.

Once the above is done, I'd like to then loop through the columns, and select the NON-NULL columns, leaving those that are all NULLs. And the other columns, I may do something differently with them based on format, certain numeric thresholds, etc. And that may actually vary each time.

But if I could just get the summary built dynamically as start, that's really what I'm after.

I've read of using Cursors, and may have used one once, but don't remember at the moment. I'd imagine I have to loop through or use a cursor to accomplish this.

Thanks for any thoughts or suggestions on this.

I'll try to add a mock-up of what I'd like to output as the temp table hopefully soon.

 

[Qik3Coder]

To what end?

You've got questions and source code. We want both!

 

[kjv1611]

Basically, fairly regularly, I get a really huge "master table" of data for research and analysis. Well, it usually ends up being that probably half of the columns are null. So I can manually go through each time and pick out what I need whether I build a table off the table or not, but I thought it might be useful to put together a dynamic method in SQL to grab what I need semi-automatically.

It's probably more-so that I want to do it than that it has to be done, of course.

 

[Alan0568]

Have you had a look at the SSIS Data Profiling task and the utility to read the output DataProfileViewer.exe ?

 

[kjv1611]

Sure haven't. I'll look it up, and see if I can use it at all. Thanks. I'll check on that hopefully in the next day or two, and post back with my findings. Thanks for the reference, Alan0568.