none
Count number of distinct values for a column for all tables that contains that column RRS feed

  • Question

  • Imagine I have one Column called cdperson. With the query below I know which Tables have column cdperson

    select

    t.[name]fromsys.schemassinnerjoin  sys.tables  tons.schema_id=t.schema_idinnerjoin  sys.columnscont.object_id=c.object_idinnerjoin  sys.types   donc.user_type_id=d.user_type_idwherec.name ='cdperson'

    now I want to know for each table, how many distinct values of cdperson I have and I want the result ordered by the table that has more distinct values (descending)

    Table1                                                                                     
       cdperson                        select distinct(cdperson) = 10
       cdadress
       quant


    Table2 with 
       cdaddress                      (no column cdperson in this table)
       quant

    Table3
       cdperson                        select distinct(cdperson) = 100
       value

    Table 4
       cdperson                        select distinct(cdperson) = 18
       sum
      
    I want this result ordered by number of distinct cdperson

    table3   100
    table4   18
    table    10

    Thks for your answers

     

    Wednesday, January 21, 2015 12:01 PM

Answers

  • Here you go
    CREATE TABLE #temp(tablename sysname , CNT BIGINT)
    
    DECLARE @QRY NVARCHAR(MAX);
    SET @qry=(SELECT
    N'INSERT INTO #TEMP SELECT ''' +T.[name] +''' AS TableName, COUNT (DISTINCT cdperson) DistCount FROM  '+ schema_name(t.schema_id)+'.'+t.[name] +';'
    FROM sys.schemas s INNER JOIN  sys.tables  t ON s.schema_id=t.SCHEMA_ID INNER JOIN  sys.columns c ON t.object_id=c.object_id INNER JOIN  sys.types   d ON c.user_type_id=d.user_type_id WHERE c.name ='cdperson'
    FOR XML PATH(''))
    EXEC(@QRY)
    
    SELECT * FROM #temp



    Satheesh
    My Blog | How to ask questions in technical forum


    • Proposed as answer by Kalman TothModerator Wednesday, January 21, 2015 12:41 PM
    • Unproposed as answer by Kalman TothModerator Wednesday, January 21, 2015 12:45 PM
    • Edited by Satheesh Variath Wednesday, January 21, 2015 12:57 PM
    • Proposed as answer by VSPatel Wednesday, January 21, 2015 1:06 PM
    • Marked as answer by novreis Wednesday, January 21, 2015 3:35 PM
    Wednesday, January 21, 2015 12:36 PM
  • I had to add schema name to the above script to make it work in AdventureWorks:

    CREATE TABLE #temp(TableName sysname , CNT BIGINT)
    
    DECLARE @QRY NVARCHAR(MAX);
    SET @qry=(SELECT
    N'INSERT INTO #TEMP SELECT '''+schema_name(t.schema_id)+'.'+T.[name] +''' AS TableName, COUNT (DISTINCT ProductID) DistCount FROM  '+ 
    schema_name(t.schema_id)+'.'+t.[name] +';'
    FROM sys.schemas s INNER JOIN  sys.tables  t ON s.schema_id=t.SCHEMA_ID 
    INNER JOIN  sys.columns c ON t.object_id=c.object_id INNER JOIN  sys.types   d ON c.user_type_id=d.user_type_id 
    WHERE c.name ='ProductID'
    FOR XML PATH(''))
    EXEC(@QRY)
    
    SELECT * FROM #temp ORDER BY TableName
    
    DROP TABLE #temp
    
    /*
    Production.Product	504
    Production.ProductCostHistory	293
    Production.ProductDocument	31
    Production.ProductInventory	432
    Production.ProductListPriceHistory	293
    Production.ProductProductPhoto	504
    Production.ProductReview	3
    Production.TransactionHistory	441
    Production.TransactionHistoryArchive	497
    Production.WorkOrder	238
    Production.WorkOrderRouting	149
    Purchasing.ProductVendor	211
    Purchasing.PurchaseOrderDetail	211
    Sales.SalesOrderDetail	266
    Sales.ShoppingCartItem	3
    Sales.SpecialOfferProduct	295
    */





    Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014





    Wednesday, January 21, 2015 12:46 PM
    Moderator

All replies

  • Here you go
    CREATE TABLE #temp(tablename sysname , CNT BIGINT)
    
    DECLARE @QRY NVARCHAR(MAX);
    SET @qry=(SELECT
    N'INSERT INTO #TEMP SELECT ''' +T.[name] +''' AS TableName, COUNT (DISTINCT cdperson) DistCount FROM  '+ schema_name(t.schema_id)+'.'+t.[name] +';'
    FROM sys.schemas s INNER JOIN  sys.tables  t ON s.schema_id=t.SCHEMA_ID INNER JOIN  sys.columns c ON t.object_id=c.object_id INNER JOIN  sys.types   d ON c.user_type_id=d.user_type_id WHERE c.name ='cdperson'
    FOR XML PATH(''))
    EXEC(@QRY)
    
    SELECT * FROM #temp



    Satheesh
    My Blog | How to ask questions in technical forum


    • Proposed as answer by Kalman TothModerator Wednesday, January 21, 2015 12:41 PM
    • Unproposed as answer by Kalman TothModerator Wednesday, January 21, 2015 12:45 PM
    • Edited by Satheesh Variath Wednesday, January 21, 2015 12:57 PM
    • Proposed as answer by VSPatel Wednesday, January 21, 2015 1:06 PM
    • Marked as answer by novreis Wednesday, January 21, 2015 3:35 PM
    Wednesday, January 21, 2015 12:36 PM
  • I had to add schema name to the above script to make it work in AdventureWorks:

    CREATE TABLE #temp(TableName sysname , CNT BIGINT)
    
    DECLARE @QRY NVARCHAR(MAX);
    SET @qry=(SELECT
    N'INSERT INTO #TEMP SELECT '''+schema_name(t.schema_id)+'.'+T.[name] +''' AS TableName, COUNT (DISTINCT ProductID) DistCount FROM  '+ 
    schema_name(t.schema_id)+'.'+t.[name] +';'
    FROM sys.schemas s INNER JOIN  sys.tables  t ON s.schema_id=t.SCHEMA_ID 
    INNER JOIN  sys.columns c ON t.object_id=c.object_id INNER JOIN  sys.types   d ON c.user_type_id=d.user_type_id 
    WHERE c.name ='ProductID'
    FOR XML PATH(''))
    EXEC(@QRY)
    
    SELECT * FROM #temp ORDER BY TableName
    
    DROP TABLE #temp
    
    /*
    Production.Product	504
    Production.ProductCostHistory	293
    Production.ProductDocument	31
    Production.ProductInventory	432
    Production.ProductListPriceHistory	293
    Production.ProductProductPhoto	504
    Production.ProductReview	3
    Production.TransactionHistory	441
    Production.TransactionHistoryArchive	497
    Production.WorkOrder	238
    Production.WorkOrderRouting	149
    Purchasing.ProductVendor	211
    Purchasing.PurchaseOrderDetail	211
    Sales.SalesOrderDetail	266
    Sales.ShoppingCartItem	3
    Sales.SpecialOfferProduct	295
    */





    Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014





    Wednesday, January 21, 2015 12:46 PM
    Moderator
  • I just reused the query that OP had posed, So I believe he used the default schema. Anyways I have updated the script to include schema , thanks

    Satheesh
    My Blog | How to ask questions in technical forum

    Wednesday, January 21, 2015 12:58 PM
  • Thks Satheesh, it works very well with the connection I have defined at c# program! My problem now is a Timeout error once the database is big! My timeout is defined at connection string but not sure if there is a timeout parameter for queries. Thank you very much!

    Wednesday, January 21, 2015 3:33 PM
  • Thks Kalman, Satheesh olution works very well with the connection I have defined at c# program! My problem now is a Timeout error once the database is big! My timeout is defined at connection string but not sure if there is a timeout parameter for queries. Thank you very much for your contribution also.
    Wednesday, January 21, 2015 3:35 PM
  • try MS_foreachtable

    Sp_msforeachtable 'select t.[name]fromsys.schemassinnerjoin  sys.tables  tons.schema_id=t.schema_idinnerjoin  sys.columnscont.object_id=c.object_idinnerjoin  sys.types   donc.user_type_id=d.user_type_idwherec.name =''cdperson'' '


    Regards, Pradyothana DP. Please Mark This As Answer if it solved your issue. Please Mark This As Helpful if it helps to solve your issue. ========================================================== http://www.dbainhouse.blogspot.in/

    Wednesday, January 21, 2015 4:25 PM