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?
 
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
 
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.