Finding table name based on a specific value

Question

Finding table name based on a specific value

Raghavender Chavva

SSCrazy Eights

Points: 8669
More actions
April 13, 2018 at 4:26 pm

#403262

Comments posted to this topic are about the item Finding table name based on a specific value
Thank You.
Regards,
Raghavender Chavva

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

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

jeffgonnering SSChasing Mays Points: 659 More actions · Answer 1

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: 286590 More actions · Answer 2

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 More actions · Answer 3

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 More actions · Answer 4

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.