Query for a specific content but I don't know in which table

Hi,

I need to know which table stores a specific context "XYZ". How can I write a query to look for this content in all tables of the database and return the table and column?

Thank you in advance.
DistillingExpertsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

this has been a fairly standard approach to this problem....
0
DistillingExpertsAuthor Commented:
Thank you. What if I don't have rights to create a store proc? Is that possible to have a single query to do just that?
0
LowfatspreadCommented:
well you could just copy the stored proc and run the code ... within it...

convert the store proc parameters into a Declare ... / set...

BUT BUT if you don't have the authorisation to create a store procedure then you probably shouldn't be attempting to do this processing either....

you should talk to the DBA/System administrator as this sort of query is going to put a considerable load on the system, that they may wish to control/ameliorate...

0
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

Scott PletcherSenior DBACommented:
>> this has been a fairly standard approach to this problem.... <<

An extraordinarily inefficient one, though.
0
Scott PletcherSenior DBACommented:
Are you looking for an exact match to XYZ or XYZ anywhere in the column, such as "something XYZ something else"?
0
DistillingExpertsAuthor Commented:
ScottPletcher, thanks for having sent a better answer (?)
0
DistillingExpertsAuthor Commented:
Sorry :-) Yes, could be  "something XYZ something else"!
0
Scott PletcherSenior DBACommented:
>> Is that possible to have a single query to do just that? <<

It's theoretically possible, but you wouldn't want to do that.  You really need to use a separate statement for each table (but not a separate statement for every column -- yikes!).
0
Scott PletcherSenior DBACommented:
>.> ScottPletcher, thanks for having sent a better answer (?)  <<

LOL.  No, haven't done that yet.

How large is the db?  And how powerful is the server?

If you have a small db and a powerful server, the "standard approach" will work for you.


You may not have authority to run this command, but if you can:

EXEC sp_helpdb 'your_db_name'

Please report the size(s) of your data (only) file(s); ignore log files.
0
LowfatspreadCommented:
oops again thanks scott....

i thought that one did it per table...   i usually write my own search when this sort of thing is required as
with a bit of thought the areas to search can usually be considerably reduced....

sorry haven't got my toolbox with me at present ...
0
Scott PletcherSenior DBACommented:
WOW, I've got exactly the same problem.  I wrote a script to do that but don't have it handy.

I'm sure I can code up something if needed that beats one column at a time for large tables.
0
DistillingExpertsAuthor Commented:
You may not have authority to run this command, but if you can:

EXEC sp_helpdb 'your_db_name'

Scott, thank you. I executed the command and the db size is 214 MB. What should I do next?
0
Scott PletcherSenior DBACommented:
Nice, that's (very) small.  You should be fine using the sample code provided by Lowfat's link.
0
DistillingExpertsAuthor Commented:
Scott, thank you. Lowfat, sorry for the basic question, but how would look like a snippet of the command to run that without creating the stored proc?
0
LowfatspreadCommented:

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

	SET NOCOUNT ON

	DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
	SET  @TableName = ''
	SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

	WHILE @TableName IS NOT NULL
	BEGIN
		SET @ColumnName = ''
		SET @TableName = 
		(
			SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
			FROM 	INFORMATION_SCHEMA.TABLES
			WHERE 		TABLE_TYPE = 'BASE TABLE'
				AND	QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
				AND	OBJECTPROPERTY(
						OBJECT_ID(
							QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
							 ), 'IsMSShipped'
						       ) = 0
		)

		WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
		BEGIN
			SET @ColumnName =
			(
				SELECT MIN(QUOTENAME(COLUMN_NAME))
				FROM 	INFORMATION_SCHEMA.COLUMNS
				WHERE 		TABLE_SCHEMA	= PARSENAME(@TableName, 2)
					AND	TABLE_NAME	= PARSENAME(@TableName, 1)
					AND	DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
					AND	QUOTENAME(COLUMN_NAME) > @ColumnName
			)
	
			IF @ColumnName IS NOT NULL
			BEGIN
				INSERT INTO #Results
				EXEC
				(
					'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
					FROM ' + @TableName + ' (NOLOCK) ' +
					' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
				)
			END
		END	
	END

	SELECT ColumnName, ColumnValue FROM #Results

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
His code also doesn't do a basic check for efficency, viz:

if the search string is longer than the column, don't search that column.

That is, if the string to be found is 10 bytes, there's no reason at all to search a varchar(9) column.
0
DistillingExpertsAuthor Commented:
Lowfatspread,

Sorry for the long time to answer. I created the table but I don't know where it was stored. Could you please let me know where?

Thank you.

Best regards.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.