Finding table name based on a specific value

  • Raghavender Chavva

    SSCrazy Eights

    Points: 8669

    Comments posted to this topic are about the item Finding table name based on a specific value

    Thank You.

    Regards,
    Raghavender Chavva

  • jeffgonnering

    Mr or Mrs. 500

    Points: 594

    query does not take into account the table schema so it is fails in my database.  It will only work if all tables are in the dbo schema, which is not a best practice.

  • Sean Lange

    SSC Guru

    Points: 286411

    jeffgonnering - Tuesday, April 17, 2018 8:38 AM

    query does not take into account the table schema so it is fails in my database.  It will only work if all tables are in the dbo schema, which is not a best practice.

    This is only the beginning of the challenges. It only looks for .com so if you have any other top level domain this will miss it. Also using antiquated join syntax and mixing system catalog view with informationschema views is a bit odd.

    I would suggest to the OP that a better solution to updating all the email addresses in your database and hoping you don't miss some would be use something like Papercut so you can intercept ANY email sent to ANY address from a given machine. It is super flexible and allows for all sorts of exceptions and detailed rules. There are other options out there of course but papercut is one I have used for this exact type of thing. It eliminates the accidental emails to customers/clients from a QA or dev machine much more accurately than updating a bunch of tables and hoping you didn't miss any.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Raghavender Chavva

    SSCrazy Eights

    Points: 8669

    jeffgonnering - Tuesday, April 17, 2018 8:38 AM

    query does not take into account the table schema so it is fails in my database.  It will only work if all tables are in the dbo schema, which is not a best practice.

    Thank You Jeff.
    We can add the table schema in the script. I will test it and update the script.

    Thank You.

    Regards,
    Raghavender Chavva

  • bwalsh 41768

    SSC Rookie

    Points: 33

    To improve performance, you should also want to take isc.data_type into account, you won't be storing email addresses in datetime, interger etc columns.
    You may however, have columns with the TEXT data type, and this throws an error:

    Operand data type text is invalid for count operator.

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

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