Searching all tables for a value, return table name

Question

Post reply

Searching all tables for a value, return table name

squeekz01

SSC-Addicted

Points: 445
More actions
February 2, 2014 at 9:58 pm

#303975

Hi guys,
I am wanting to search all tables in my database for a value & then return the name of the table that contains the data. Does anyone know how I do this? I am hopelessly stuck :unsure:
Thanks, appreciate the help 🙂

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 1

Google this. "Searching all tables for a value, return table name".

--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)

squeekz01 SSC-Addicted Points: 445 More actions · Answer 2

I don't mean to waste your time, but I have googled this and a variety of other strings and have not been able to find anything of value.

RTaylor2208 SSChampion Points: 13298 More actions · Answer 3

If you use the extract string to search in google what Jeff added the 3rd result i found was this:

http://gallery.technet.microsoft.com/scriptcenter/c0c57332-8624-48c0-b4c3-5b31fe641c58

MCITP SQL 2005, MCSA SQL 2012

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

RTaylor2208 (2/3/2014)

If you use the extract string to search in google what Jeff added the 3rd result i found was this:

http://gallery.technet.microsoft.com/scriptcenter/c0c57332-8624-48c0-b4c3-5b31fe641c58%5B/quote%5D

Looks like a solution indeed. Kinda spiffy functionality too!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

Here is a script that was posted by ChrisM. I modified it slightly to make a little more bullet proof. This will generate the code you need to run. It has the ability to find multiple values and there is no cursor to destroy your sql box.

DECLARE @MySearchCriteria VARCHAR(500)

SET @MySearchCriteria = '''value1'', ''value2''' --you do need all these quotation marks because this string is injected to another string.

SELECT 'SELECT ' + c.columnlist + '] FROM [' + t.name + '] WHERE ' + w.whereclause as SelectStatement

FROM sys.tables t

CROSS APPLY (

SELECT STUFF((

SELECT '], [' + c.Name AS [text()]

FROM sys.columns c

WHERE t.object_id = c.object_id

AND c.collation_name IS NOT NULL

AND c.max_length > 6

FOR XML PATH('')

), 1, 2, '' )

) c (columnlist)

CROSS APPLY (

SELECT STUFF((

SELECT ' OR [' + c.Name + '] IN (' + @MySearchCriteria + ')' AS [text()]

FROM sys.columns c

WHERE t.object_id = c.object_id

AND c.collation_name IS NOT NULL

AND c.max_length > 6

FOR XML PATH('')

), 1, 4, '' )

) w (whereclause)

where c.columnlist is not null

ORDER BY t.name

_______________________________________________________________

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/

dbassassin Default port Points: 1410 More actions · Answer 6

Here is my favorite. Written by SQLDenis

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/the-ten-most-asked-sql-server-questions--1/#2

Why is it that people who can't take advice always insist on giving it? - James Bond, Casino Royale