Extract Column details from table

Question

Hello,

I will have to pass table name as input parameter. The return will like

Can anyone please help me on this?

Thanks in advance.

Answer 1

Found this https://www.techbrothersit.com/2016/03/how-to-find-percentage-of-empty-or.html although I was trying to find my own solution for this topic. I remember there was a blog post where several interesting solutions (mine included) were presented several years back. Unfortunately, I forgot who was MS employee who made that series and trying to use 'Naomi count limno' as search terms didn't yield the result. (BTW, it would be nice if this editor would support spell check).

Answer 2

This is not what I was specifically searching for, but it's close
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/50b4f688-e294-434b-aa6f-e9fd1f64fa5f/dynamic-query-for-null-field-checking?forum=sqlexpress

Answer 3

Try this:

DECLARE @tbl sysname = 'Orders',
        @sql nvarchar(MAX)

SELECT @sql = string_agg(
         convert(nvarchar(MAX), 'SELECT ') + quotename(name, '''') + ' AS column_name, 
         COUNT(*) AS total_count, COUNT(' + quotename(name) + ') AS not_null_count,
         COUNT(DISTINCT ' + quotename(name) + ') AS distinct_count
         FROM ' + quotename(@tbl), 
         char(13) + char(10) + 'UNION ALL' + char(13) + char(10))
FROM   sys.columns
WHERE  object_id = object_id(@tbl)

PRINT @sql

EXEC(@sql)

It does not handle "blank" values. That's requires more work, since this is only applicable to string columns.

Answer 4

Hi @sourav dutta
Please check this:
A little modification on Erland's answer ,which add 'nullif' to deal with blank values:

DECLARE @tbl sysname = 'Table_name',  
        @sql nvarchar(MAX)  
SELECT @sql = string_agg(  
         convert(nvarchar(MAX), 'SELECT ') + quotename(name, '''') + ' AS column_name,   
         COUNT(*) AS total_count, COUNT(nullif(convert(varchar,' + quotename(name) + '),'''')) AS not_null_count,  
         COUNT(DISTINCT nullif(convert(varchar,' + quotename(name) + '),'''')) AS distinct_count  
         FROM ' + quotename(@tbl),   
         char(13) + char(10) + 'UNION ALL' + char(13) + char(10))  
FROM   sys.columns  
WHERE  object_id = object_id(@tbl)  
PRINT @sql  
EXEC(@sql)  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.