Post reply

Scanning for a specific value in all the tables

  • May 16, 2016 at 4:58 am

    #318886

    We have an OLAP system where we are assigning 0 value to any uniqueid column if the data is not following the business rule.

    eg we have the following tables

    i) fact_employee with columns (fct_empuniqueid , fct_salary,fct_prorated_salary)

    ii) fact_employee_transaction (fet_empuniqueid,fet_transactiontype)

    now in case the data is not following the business logic then data will look like :

    i) fact_employee (0,100,30)

    ii) fact_employee_transaction (0,"aa")

    Like this i am having around 100 tables and i want a tsql script which can give me the list of tables and columns having value "0". I have to scan only those columns which are ending with "Uniqueid" in the database.

  • May 16, 2016 at 5:44 am

    #1879202

    rahulsahay123 (5/16/2016)

    We have an OLAP system where we are assigning 0 value to any uniqueid column if the data is not following the business rule.

    eg we have the following tables

    i) fact_employee with columns (fct_empuniqueid , fct_salary,fct_prorated_salary)

    ii) fact_employee_transaction (fet_empuniqueid,fet_transactiontype)

    now in case the data is not following the business logic then data will look like :

    i) fact_employee (0,100,30)

    ii) fact_employee_transaction (0,"aa")

    Like this i am having around 100 tables and i want a tsql script which can give me the list of tables and columns having value "0". I have to scan only those columns which are ending with "Uniqueid" in the database.

    Lookup the sys.columns view in "Books Online" to find all of the columns and the tables that they belong to by ID. It's going to take some dynamic SQL to go through this. You'll also need to lookup the OBJECT_NAME() function.

    And, yes... a loop is ok to use for this.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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