SEARCH TEXT IN TABLE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SEARCH TEXT IN TABLE

Author

Topic

pnpsql
Posting Yak Master

246 Posts

Posted - 2011-09-08 : 08:25:39

HOW TO SEARCH SOME TEXT IN TABLE COLUMN DATA .

challenge everything

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-09-08 : 08:37:31

WHAT DO YOU MEAN? CAN YOU GIVE AN EXAMPLE?

select * from table where column like '%CAPSLOCKRULES%'

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

pnpsql
Posting Yak Master

246 Posts

Posted - 2011-09-08 : 08:40:09

I NEED TO SEARCH SOME STRING IN ALL COLUMNS OF ALL TABLES....

challenge everything

pnpsql
Posting Yak Master

246 Posts

Posted - 2011-09-08 : 08:49:01

THE JOB IS DONE USING FOLLOWING QUERY BUT IT FEARS ME....

DECLARE
@search_string VARCHAR(100),
@table_name SYSNAME,
@table_id INT,
@column_name SYSNAME,
@sql_string VARCHAR(2000)

SET @search_string = 'MAKER'

DECLARE tables_cur CURSOR FOR SELECT name, object_id FROM sys.objects WHERE type = 'U'

OPEN tables_cur

FETCH NEXT FROM tables_cur INTO @table_name, @table_id

WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE columns_cur CURSOR FOR SELECT name FROM sys.columns WHERE object_id = @table_id AND system_type_id IN (167, 175, 231, 239)

OPEN columns_cur

FETCH NEXT FROM columns_cur INTO @column_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @sql_string = 'IF EXISTS (SELECT * FROM ' + @table_name + ' WHERE ' + @column_name + ' LIKE ''%' + @search_string + '%'') PRINT ''' + @table_name + ', ' + @column_name + ''''

EXECUTE(@sql_string)

FETCH NEXT FROM columns_cur INTO @column_name
END

CLOSE columns_cur

DEALLOCATE columns_cur

FETCH NEXT FROM tables_cur INTO @table_name, @table_id
END

CLOSE tables_cur

DEALLOCATE tables_cur

challenge everything

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2011-09-08 : 08:51:03

Here i have few links hope this will help..

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

http://beyondrelational.com/blogs/madhivanan/archive/2009/12/14/search-a-value-in-character-column-of-all-tables.aspx

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-09-08 : 08:58:46

WHAT'S THE DEAL WITH ALL THE CAPITAL LETTERS? OH WAIT...NOW I GET IT. IT LOOKS LIKE I'M SCREAMING...COOL!! I'M GONNA DO IT ALL THE TIME FROM NOW ON, THEN EVERYONE WILL THINK WHAT I'M SAYING IS REALLY IMPORTANT. HAHA, GOOD ONE MAN...KUDOS!

SELECT
table_name,
column_name
FROM dbname.information_schema.columns
WHERE
column_name LIKE '%searchterm%'

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

pnpsql
Posting Yak Master

246 Posts

Posted - 2011-09-08 : 09:01:10

any other help please ..

challenge everything

pnpsql
Posting Yak Master

246 Posts

Posted - 2011-09-08 : 09:07:08

soory boss

challenge everything

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources