0

I have this statement:

Declare @sql varchar(max) = ''
declare @tablename as varchar(255) = 'test'

select @sql = @sql + 'select [' + c.name + '],count(*) as ''' + c.name 
+  ''' from [' + t.name + ']' 
from sys.columns c
inner join sys.tables t on c.object_id = t.object_id
where t.name = @tablename

EXEC (@sql)

But it gives the output comes out in different results windows and when I try to combine it with a union all the text doesn't fit it. I want to try and get the results into a temp table for SQL server is there anyway i can do this?

I'm trying to get:

Column Name   Count     Distinct Count
    a              100    1
    b              100    5
    c              100    73
    d              100    9

The statement above isn't for distinct count but i'm hoping I could replicate the same logic.

T17
  • 71
  • 7
  • 1
    What is th above even trying to achieve? Why a `COUNT` grouped on every column? That makes no sense. – Thom A Nov 27 '20 at 12:13
  • Hi Larnu, for this code what i'm trying to see is a column with the corresponding row count – T17 Nov 27 '20 at 12:16
  • How can a column have a row count? Tables have rows counts, and each column in the table will have the same number of rows; you can't have varying numbers of rows for different columns in a table. – Thom A Nov 27 '20 at 12:19
  • Well i would want to see the row counts beside the column names and then the distinct counts of non nulls, etc. – T17 Nov 27 '20 at 12:21
  • Hi Larnu if you feel this approach is inefficient what would you recommend as a better approach? – T17 Nov 27 '20 at 12:27
  • @Larnu Either this is homework, or @T17 is trying to do a data quality check on their columns. Example... For each column, what percentage of it's values are non null? I think adding a `SUM(ISNULL(MyColumn))` could get this done, and wouldn't be a horrendously slow `DISTINCT COUNT`. OP, is just thinking about unioning temp tables instead of doing it in one go... One group by – Troy Witthoeft Nov 27 '20 at 12:57

1 Answers1

0

I suspect the query you want to construct really looks like:

select 'a' as column_name, count(column_name), count(distinct column_name)
from t
union all
select 'a' as column_name, count(column_name), count(distinct column_name)
from t
union all
. . .

To construct this in SQL Server, you can use logic like this:

declare @q nvarchar(max);
set @q = '
    select ''[column_name]'' as column_name, count([column_name]) as cnt, count(distinct [column_name]) as distinct_count
    from [table_name]
';

declare @sql nvarchar(max);

select @sql = string_agg(replace(replace(@q,
                                         '[column_name]',
                                         quotename(column_name)
                                        ),
                                 '[table_name]',
                                 quotename(table_name)
                                ), ' union all '
                        )
from information_schema.columns c
where table_name = @name;  -- should probably check the schema too!

exec sp_executesql @sql;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Hi @Gordon Linoff this looks really promising thank you very much, i just tried running this script but I got an error saying 'string_agg is not a recognised built_in function'. Is there a workaround I could do to fix this? – T17 Nov 27 '20 at 12:56
  • 1
    You'll need to use the old `FOR XML PATH` method, @T17 , if you're using an older version of SQL Server. – Thom A Nov 27 '20 at 13:24
  • Thanks @Larnu, apologies i'm quite new to Sql, what is the FOR XML PATH method? – T17 Nov 27 '20 at 13:28
  • [string_agg for sql server pre 2017](https://stackoverflow.com/q/49361088/2029983) – Thom A Nov 27 '20 at 13:32