Query to search all tables in all dbs on a sever for a value
-
Hay,
I need to know about query to search all tables in all dbs on a SQL 2000 sever for a string value.
We changed our servername & facing some issues with application. I want to search all tables in all databases for value 'OldServername'
Is there an easy way?
Thanks
-
You could bake your own script using the undocumented SPs 'sp_MSforeachdb', 'sp_MSforeachtable' and from there construct and execute a select, for every table in every database, with a WHERE clause, on the columns that are of the right type (CHAR,VARCHAR,NCHAR,NVARCHAR,TEXT, and maybe you should check your sql_variant also), wich checks for '%OldServername%' ...
-
I made an ugly server intensive cursor for a similar question a while back;noone came up with a better example so far.
see the thread here:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=181099
it whips through every varchar column in the database and searches for the searchstring....
note that this would be ok for a developer, but something like this in production could severly impact perfromance, especially if some table had a million rows in it, and that table had a lot of varchar columns to be searched.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Nobody came up with a better solution because it's not something that should be run on the server(s). However, this is a case where you have no choice. But keep in mind that this can run for hours (days) before finishing. Definitly not something I'd want to run on a production server. Also there's no workaround for the dynamic sql here, it just can be done without human intervention with a permanent sql approach.
Good luck with this adventure.
-
True, best not to do. And best not to do it many times
Reading http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=181099 made me want to specify that it is probably better to write only ONE query per table. And not one per column... It would still not be nice to your server. But having at least one table with more then one column of CHAR, VARCHAR, NCHAR, NVARCHAR, TEXT or sql_variant, would gain performance and save the server some breath...
//Hans
-
Surely, a scan is bad but n scans is n times worse...
Didn't read the whole script posted, so there might have some more optimizations to do on it.
-
we all agree that a monster scan like this is bad, but bad or not, sometimes you need to do it.
Here's a slightly updated version, this simply uses if exists, and inserts into a temp table the info i thought a developer might need to use to follow up.
it returns results like this:
TBLNAME COLNAME SQL
CITYDATA CITYNAME SELECT * FROM CITYDATA WHERE CITYNAME LIKE 'TEST'
GMHUDMX HUDMXTITLE SELECT * FROM GMHUDMX WHERE HUDMXTITLE LIKE 'TEST'
RB_FIELD FIELD_ALIAS SELECT * FROM RB_FIELD WHERE FIELD_ALIAS LIKE 'TEST'
RB_FIELD FIELD_NAME SELECT * FROM RB_FIELD WHERE FIELD_NAME LIKE 'TEST'
tell me what you think of this:
CREATE PROCEDURE UGLYSEARCH
-- EXEC UGLYSEARCH 'TEST'
@SEARCHSTRING VARCHAR(50)
AS
SET NOCOUNT ON
DECLARE @SQL VARCHAR(500),
@TABLENAME VARCHAR(60),
@COLUMNNAME VARCHAR(60)
CREATE TABLE #RESULTS(TBLNAME VARCHAR(60),COLNAME VARCHAR(60),SQL VARCHAR(600))
SELECT
SYSOBJECTS.NAME AS TBLNAME,
SYSCOLUMNS.NAME AS COLNAME,
TYPE_NAME(SYSCOLUMNS.XTYPE) AS DATATYPE
INTO #FKFINDER
FROM SYSOBJECTS
INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID=SYSCOLUMNS.ID
WHERE SYSOBJECTS.XTYPE='U'
AND TYPE_NAME(SYSCOLUMNS.XTYPE) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR')
ORDER BY TBLNAME,COLNAME
DECLARE C1 CURSOR FOR
SELECT TBLNAME,COLNAME FROM #FKFINDER ORDER BY TBLNAME,COLNAME
OPEN C1
FETCH NEXT FROM C1 INTO @TABLENAME,@COLUMNNAME
WHILE @@FETCH_STATUS <> -1
BEGIN
--SET @SQL = 'SELECT ''' + @TABLENAME + ''' AS TABLENAME,''' + @COLUMNNAME + ''' AS COLUMNNAME,* FROM ' + @TABLENAME + ' WHERE ' + @COLUMNNAME + ' LIKE ''%' + @SEARCHSTRING + '%'''
SET @SQL = 'IF EXISTS(SELECT * FROM ' + @TABLENAME + ' WHERE ' + @COLUMNNAME + ' LIKE ''%' + @SEARCHSTRING + '%'') INSERT INTO #RESULTS(TBLNAME,COLNAME,SQL) VALUES(''' + @TABLENAME + ''',''' + @COLUMNNAME + ''','' SELECT * FROM ' + @TABLENAME + ' WHERE ' + @COLUMNNAME + ' LIKE ''''' + @SEARCHSTRING + ''''' '') ;'
PRINT @SQL
EXEC (@SQL)
FETCH NEXT FROM C1 INTO @TABLENAME,@COLUMNNAME
END
CLOSE C1
DEALLOCATE C1
SELECT * FROM #RESULTS
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
The big optimization idea was to do something like this :
Select * from dbo.TableName where col1 like '%Servername%' or col2 like '%Servername%' or col3 like '%Servername%'.
Makes only 1 clustered scan per table instead of one per column.
Then if needed the programmer can go back to that specifid table and redo the scan to correct the rows.
-
Thanx for understanding
-
Hey I got a server to keep alive too you know
. -
Thanks for the ideas Remi and Hans;
based on your input, i enhanced the uglysearch a bit more; now i check to make sure columns are at least as long as the search param, in order for the cursor recordset to be smaller.
added an optional parameter to search by table or by table and column.
the search by table dynamically gets the column names and created the "col1 like '%test%' Or col2 like '%test%'
i ran into trouble with some really wide tables that have lots of varchar fields; i tried to make the results table as wide as possible for the investigation sql, but your results may vary if you have tables with so many varchar columns that the dynamic sql is bigger than 7800 chars.
DROP PROCEDURE UGLYSEARCH
go
CREATE PROCEDURE UGLYSEARCH
-- EXEC UGLYSEARCH 'TEST'
-- EXEC UGLYSEARCH 'TEST',1
@SEARCHSTRING VARCHAR(50),
@SEARCHBYCOLUMN INT = 0
AS
SET NOCOUNT ON
DECLARE @SQL VARCHAR(8000),
@TABLENAME VARCHAR(60),
@COLUMNNAME VARCHAR(60),
@COLZ VARCHAR(7000)
CREATE TABLE #RESULTS(TBLNAME VARCHAR(60),COLNAME VARCHAR(60),SQL VARCHAR(7800))
SELECT
SYSOBJECTS.NAME AS TBLNAME,
SYSCOLUMNS.NAME AS COLNAME,
TYPE_NAME(SYSCOLUMNS.XTYPE) AS DATATYPE
INTO #FKFINDER
FROM SYSOBJECTS
INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID=SYSCOLUMNS.ID
WHERE SYSOBJECTS.XTYPE='U'
AND TYPE_NAME(SYSCOLUMNS.XTYPE) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR')
AND SYSCOLUMNS.LENGTH >= LEN(@SEARCHSTRING)
ORDER BY TBLNAME,COLNAME
IF @SEARCHBYCOLUMN = 0
BEGIN
DECLARE C1 CURSOR FOR
SELECT DISTINCT TBLNAME FROM #FKFINDER ORDER BY TBLNAME
OPEN C1
FETCH NEXT FROM C1 INTO @TABLENAME
WHILE @@FETCH_STATUS <> -1
BEGIN
SET @COLZ=''
SELECT @COLZ= @COLZ + COLNAME + ' LIKE ''%' + @SEARCHSTRING + '%'' OR ' FROM #FKFINDER WHERE TBLNAME=@TABLENAME
--@COLZ has a trailing 'OR ' which must be removed
SET @COLZ = SUBSTRING(@COLZ,1,LEN(@COLZ) -3)
--PRINT @COLZ
SET @SQL = 'IF EXISTS(SELECT * FROM ' + @TABLENAME + ' WHERE ' + @COLZ + ') INSERT INTO #RESULTS(TBLNAME,COLNAME,SQL) VALUES(''' + @TABLENAME + ''',''-'','' SELECT * FROM ' + @TABLENAME + ' WHERE ' + REPLACE(@COLZ,'''','''''') + ''') ;'
--PRINT @SQL
EXEC (@SQL)
FETCH NEXT FROM C1 INTO @TABLENAME
END
CLOSE C1
DEALLOCATE C1
END --IF
ELSE
BEGIN
DECLARE C1 CURSOR FOR
SELECT TBLNAME,COLNAME FROM #FKFINDER ORDER BY TBLNAME,COLNAME
OPEN C1
FETCH NEXT FROM C1 INTO @TABLENAME,@COLUMNNAME
WHILE @@FETCH_STATUS <> -1
BEGIN
SET @COLZ=''
SELECT @COLZ= @COLZ + COLNAME + ' LIKE ''%' + @SEARCHSTRING + '%'' OR ' FROM #FKFINDER WHERE TBLNAME=@TABLENAME
--@COLZ has a trailing 'OR ' which must be removed
SET @COLZ = SUBSTRING(@COLZ,1,LEN(@COLZ) -3)
--SET @SQL = 'SELECT ''' + @TABLENAME + ''' AS TABLENAME,''' + @COLUMNNAME + ''' AS COLUMNNAME,* FROM ' + @TABLENAME + ' WHERE ' + @COLUMNNAME + ' LIKE ''%' + @SEARCHSTRING + '%'''
SET @SQL = 'IF EXISTS(SELECT * FROM ' + @TABLENAME + ' WHERE ' + @COLUMNNAME + ' LIKE ''%' + @SEARCHSTRING + '%'') INSERT INTO #RESULTS(TBLNAME,COLNAME,SQL) VALUES(''' + @TABLENAME + ''',''' + @COLUMNNAME + ''','' SELECT * FROM ' + @TABLENAME + ' WHERE ' + @COLUMNNAME + ' LIKE ''''' + @SEARCHSTRING + ''''' '') ;'
PRINT @SQL
EXEC (@SQL)
FETCH NEXT FROM C1 INTO @TABLENAME,@COLUMNNAME
END
CLOSE C1
DEALLOCATE C1
END --ELSE
SELECT * FROM #RESULTS
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanx for the script... Will try to make good use of it (or not use it if possible
). -
Thanx from me too. Maybe you could post in in the scripts section? I am quite sure others will profit from it's use...
//Hanslindgren
-
I second that, since I've been proposed to do the same thing with another query that deals with defaults.
-
You could also use this and loop through all DB
http://vyaskn.tripod.com/sql_server_search_and_replace.htm
may be using sp_MSforeachdb
* Noel
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply