January 8, 2010 at 9:33 am
hi all
i need to find a single word in a field i don't know in a whole sql db in sql express 2008.
is there a easy way?
Thank you
January 8, 2010 at 12:45 pm
there are a number of such scripts in the scripts section of this site. If the database is any size they are likely to take a while to run.
Heres one I came across
/*
* CATEGORY: Script
* AUTHOR: Luiz Barros
* OBJECTIVE: Find and Replace a string in all string fields (char, varchar, etc) of all tables in the database
*
* PARAMETERS:
* @SearchChar is the string to be found. Use wildcard %
* @ReplaceChar is the string to replace occurrences of @SearchChar
* @Replace=0 => search for @SearchChar; @Replace=1 => Find and replace occurrences
*/
SET NOCOUNT ON
DECLARE????@SearchChar????VARCHAR(8000),
????@ReplaceChar????VARCHAR(8000),
????@SearchChar1????VARCHAR(8000),
????@Replace????????BIT
SET @Replace = 0 -- 0 => only find; 1 => replace
SET @SearchChar = '%FIND THIS STRING%' -- Like 'A%', '%A' or '%A%'
SET @ReplaceChar = 'REPLACE BY THIS STRING' -- don't use wildcards here
IF @Replace=1 AND (@SearchChar IS NULL OR @ReplaceChar IS NULL) BEGIN
????PRINT 'Invalid Parameters' Return
END
SET @SearchChar1 = REPLACE(@SearchChar, '%', '')
declare????@sql????????varchar(8000),
????@ColumnName????varchar(100),
????@TableName????varchar(100)
CREATE TABLE #T (
????TableName????????VARCHAR(100),
????FieldName????????VARCHAR(100),
????Value????????VARCHAR(Max)
)
????
declare db cursor for
SELECT????b.Name as TableName,
????c.Name as ColumnName
FROM????sysobjects b, syscolumns c
WHERE????C.id = b.id
????and b.type='u'
????AND c.xType IN (35, 99, 167, 175, 231, 239) -- string types
order by????b.name
open db
fetch next from db into @TableName, @ColumnName
WHILE @@FETCH_STATUS = 0 BEGIN
????IF @Replace = 0
????????SET @sql = 'INSERT #T SELECT '''+@TableName+''', ''' +@ColumnName+ ''', ['+@ColumnName+'] FROM '+@TableName+' WHERE ['+@ColumnName+'] LIKE '''+@SearchChar+''''
????ELSE
????????SET @sql = 'UPDATE '+@TableName+' SET ['+@ColumnName+'] = REPLACE(convert(varchar(max),['+@ColumnName+']),'''+@SearchChar1+''','''+@ReplaceChar+''') WHERE ['+@ColumnName+'] LIKE '''+@SearchChar+''''
????EXEC(@sql)
????print @TableName+' - '+@ColumnName
????fetch next from db into @TableName, @ColumnName
END
IF @Replace=0 SELECT * FROM #T ORDER BY TableName
DROP TABLE #T
close db
deallocate db
---------------------------------------------------------------------
January 11, 2010 at 4:17 am
sorry, but i'm really a newbie.
i want to search a word infor in the entire db and i don't want to replace it. where can i put this info in the query?
thanks a lot
January 11, 2010 at 5:07 am
In the case of the code above you would just leave @replace = 0
code seems to have ended up with spurious characters so needs all those 0 type characters removed, might be better off searching site for an alternative.
whichever one you use in the end test on a dev database first and take a backup beforehand to be sure.
---------------------------------------------------------------------
January 14, 2010 at 7:15 pm
Here is a blog post about searching across all fields in all tables across a database.
http://chrisbarba.wordpress.com/2009/02/28/search-across-…-in-sql-server/
January 15, 2010 at 5:59 am
Francisco Rosado (1/11/2010)
i want to search a word infor in the entire db
Do you realize this business requirement does not makes any sense?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply