Post reply

Script to get distinct count and column name

  • January 15, 2020 at 3:56 pm

    #3715683

    Hi,

    declare @table varchar(500) ='person'

    Select 'select count(*), count(distinct('+ STRING_AGG( name, ')),count(distinct(')+ + ')) from '+ @table from [sys].[all_columns]

    where object_id=(Select object_id from [sys].[tables]

    where name=@table)

    result

    select count(*), count(distinct(a)),count(distinct(b)),count(distinct(c)) from person

    how to modify script to add column name in result like

    select count(*), count(distinct(a)) as 'a', count(distinct(b)) as 'b', count(distinct(c)) as 'c' from person

    need generic script to check columns cardinality  before index creation

    Thank you

     

     

     

     

     

  • January 15, 2020 at 5:32 pm

    #3715720

    this should generate the syntax you need for each table

     


    ;with cte as ( Select c.table_schema + '.' + c.table_name as Tablename, 'count(distinct('+ c.column_name + ')) as ' + c.column_name as ColumnName
    from INFORMATION_SCHEMA.Columns c
    join INFORMATION_SCHEMA.TABLES t
    on c.TABLE_SCHEMA = t.TABLE_SCHEMA
    and c.TABLE_NAME = t.TABLE_NAME)

    select 'select ''' + TableName + ''', count(1), ' + 
    STUFF(
    (select ',' + ColumnName 
    from cte c2
    where c.TableName = c2.TableName 
    for XML path ('')
    ),
    1,
    1,'') + ' from ' + c.TableName
    from cte c
    group by c.Tablename

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • January 15, 2020 at 6:14 pm

    #3715734

    Perfect script !

    Thanks a lot

     

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply