October 9, 2013 at 8:20 am
We have
Server name- TESTXXAB
Database name - TEST_USER
This DB have many Tables
Now we have to write a query which will search the data in the tables which contains the Server name(TESTXXAB) in it..:w00t:
can this be done??
************************************
Every Dog has a Tail !!!!! :-D
October 9, 2013 at 8:36 am
OnlyOneRJ (10/9/2013)
We haveServer name- TESTXXAB
Database name - TEST_USER
This DB have many Tables
Now we have to write a query which will search the data in the tables which contains the Server name(TESTXXAB) in it..:w00t:
can this be done??
Yes it can be done. However I am not entirely sure what you are trying to do. Are you wanting to look in every single column of every single table for the value 'TEXTXXAB'? This is not going to be fast and I hope you don't need to do this very often.
My next question is, if this is a server name, why would it be in a lot of tables???
_______________________________________________________________
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/
October 9, 2013 at 8:42 am
you probably wont get EXACTLY what you need unless you will provide enough detail
Like table defintion , sample data and expected output.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 9, 2013 at 8:45 am
Don't rush into posting table definitions just yet. I suspect you are wanting to look at all tables, there is no point in posting that much detail for this type of thing.
_______________________________________________________________
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/
October 9, 2013 at 8:48 am
Sean Lange (10/9/2013)
OnlyOneRJ (10/9/2013)
We haveServer name- TESTXXAB
Database name - TEST_USER
This DB have many Tables
Now we have to write a query which will search the data in the tables which contains the Server name(TESTXXAB) in it..:w00t:
can this be done??
Yes it can be done. However I am not entirely sure what you are trying to do. Are you wanting to look in every single column of every single table for the value 'TEXTXXAB'? This is not going to be fast and I hope you don't need to do this very often.
My next question is, if this is a server name, why would it be in a lot of tables???
Sean, actually one application picks the Server name specified in column of a table...
we are not getting in sync with the app guy..
we want to search a text in all tables of a database..
************************************
Every Dog has a Tail !!!!! :-D
October 9, 2013 at 9:00 am
OK gotcha. Lowell and I have posted a couple of fairly ugly cursor based solutions over the years around here. However ChrisM posted a really slick set based approach to this a few weeks/months ago. I took the liberty of modifying his code to suit what you are doing.
DECLARE @MySearchCriteria VARCHAR(500)
SET @MySearchCriteria = 'TEXTXXAB'
SELECT 'SELECT ' + c.columnlist + ' FROM ' + t.name + ' WHERE ' + w.whereclause as SearchQuery
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 + ' like ''%' + @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 'SELECT ' + c.columnlist + ' FROM ' + t.name + ' WHERE ' + w.whereclause is not null
ORDER BY t.name
This will basically generate all of the sql you need to execute. Keep in mind that executing these queries in production can have disastrous performance issues. Hope this helps.
_______________________________________________________________
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/
October 9, 2013 at 9:01 am
OnlyOneRJ (10/9/2013)
we want to search a text in all tables of a database..
All the text of all the columns of all the tables? That's going to be slow as molasses...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 9, 2013 at 9:08 am
Thanks Sean & Gail for suggestions & query.. it worked..
We caught the Table where we need to do the change 🙂
Appreciate your prompt replies.. god bless u all..
************************************
Every Dog has a Tail !!!!! :-D
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply