February 21, 2013 at 3:18 pm
Hello - I seem to remember that there's a way to find the table and column where a certain value exists.
For example, I could write a query where the word I'm looking for is "Master Access" then the query would return a row that provides the table and column name where that data value is located.
I think I've only used this once or twice in the past 10 years so I don't remember the query for this. Since a lot of people on this forum are full time DBA's I'm hoping one of you can reply with the query for this?
February 21, 2013 at 3:29 pm
take a look at this thread, which has three different but basically the same way of finding a value across all tables and columns:
Need help with finding a string in a database...
Lowell
February 22, 2013 at 10:40 am
Hi Lowell I used sp_UGLYSEARCH that you posted in the previous thread. The query ran for a few hours yesterday afternoon and was still running when I left work. When I came back this morning the following messages were displayed in SSMS:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Has anyone else had a similar experience when attempting a google-type search on SQL Server?
February 22, 2013 at 11:37 am
sounds like you ran out of memory in SSMS on the client machine that called it; massive database i'm thinking; especially if you thought you needed to run it overnight.
Seans example is more geared to wards limiting your results by table, i think;
otherwise with mine, maybe modify it to have a parameter for the table name, and then run it against specific tables that might contain the desired value.
Lowell
February 22, 2013 at 11:50 am
this seems to work for me: if it gets interrupted, at least you have intermediate results from the successful looks of the cursor.
CREATE TABLE #RESULTS(SCHEMANAME VARCHAR(100), TBLNAME VARCHAR(100),COLNAME VARCHAR(100),SQL VARCHAR(max))
DECLARE @SearchTerm VARCHAR(5)
SET @SearchTerm = 'Master Access'
declare
@isql varchar(2000),
@tbname varchar(64)
declare cs1 cursor for select name from sys.tables
open cs1
fetch next from cs1 into @tbname
While @@fetch_status <> -1
begin
INSERT INTO #Results
EXEC sp_UGLYTABLESEARCH @tbname,@SearchTerm
fetch next from cs1 into @tbname
end
close cs1
deallocate cs1
SELECT * FROM #Results
--DROP TABLE #Results
and the modified version:
CREATE PROCEDURE sp_UGLYTABLESEARCH
/*
--Purpose: to search every string column in a databasefor a specific word
--returns sql statement as a string which idnetifies the matching table
-- or when the optional parameter is used, the sql statement for the specific matching column.
--usage:
-- EXEC sp_UGLYTABLESEARCH 'Provisional'
-- EXEC sp_UGLYTABLESEARCH 'TEST'
-- creates one SQL for each table that actually has a match for the searched value i.e.
-- SELECT * FROM [ACACTSCR] WHERE [DESCRIP] LIKE '%TEST%' OR [TITLE] LIKE '%TEST%'
--optional parameter SEARCHBYCOLUMN
-- EXEC sp_UGLYTABLESEARCH 'TEST',1
-- creates one SQL for each Column that actually has a match for the searched value i.e.
-- SELECT * FROM [dbo].[ACACTSCR] WHERE [DESCRIP] LIKE '%TEST%'
-- SELECT * FROM [dbo].[ACACTSCR] WHERE [TITLE] LIKE '%TEST%'
*/
@WhichTableName varchar(128),
@SEARCHSTRING VARCHAR(50),
@SEARCHBYCOLUMN INT = 0
AS
BEGIN
SET NOCOUNT ON
DECLARE @SQL VARCHAR(max),
@SCHEMANAME VARCHAR(100),
@TABLENAME VARCHAR(100),
@COLUMNNAME VARCHAR(100),
@COLZ VARCHAR(max)
CREATE TABLE #RESULTS(SCHEMANAME VARCHAR(100), TBLNAME VARCHAR(100),COLNAME VARCHAR(100),SQL VARCHAR(max))
SELECT
SCHEMA_NAME(schema_id) AS SCHEMANAME,
objz.name AS TBLNAME,
colz.name AS COLNAME,
TYPE_NAME(colz.user_type_id) AS DATATYPE
INTO #TEMP
FROM sys.objects objz
INNER JOIN sys.columns colz ON objz.object_id = colz.object_id
WHERE objz.type='U'
AND TYPE_NAME(colz.user_type_id) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR')
AND colz.max_length >= LEN(@SEARCHSTRING) --smart: don't search varchar(1) columns for 'TEST' 4xmpl
AND objz.name = @WhichTableName
ORDER BY TBLNAME,COLNAME
IF @SEARCHBYCOLUMN = 0
BEGIN
DECLARE C1 CURSOR FOR
SELECT SCHEMANAME,TBLNAME,COLNAME FROM #TEMP ORDER BY SCHEMANAME,TBLNAME,COLNAME
OPEN C1
FETCH NEXT FROM C1 INTO @SCHEMANAME,@TABLENAME,@COLUMNNAME
WHILE @@FETCH_STATUS <> -1
BEGIN
SET @COLZ=''
SELECT @COLZ = @COLZ + QUOTENAME(COLNAME) + ' LIKE ''%' + @SEARCHSTRING + '%'' OR ' FROM #TEMP 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 ' + QUOTENAME(@SCHEMANAME) + '.' + QUOTENAME(@TABLENAME) + ' WHERE ' + @COLZ + ') INSERT INTO #RESULTS(TBLNAME,COLNAME,SQL) VALUES(''' + @TABLENAME + ''',''-'','' SELECT * FROM ' + QUOTENAME(@TABLENAME) + ' WHERE ' + REPLACE(@COLZ,'''','''''') + ''') ;'
--PRINT @SQL
EXEC (@SQL)
FETCH NEXT FROM C1 INTO @SCHEMANAME,@TABLENAME,@COLUMNNAME
END
CLOSE C1
DEALLOCATE C1
END
ELSE --@SEARCHBYCOLUMN <> 0
BEGIN
DECLARE C2 CURSOR FOR
SELECT SCHEMANAME,TBLNAME,COLNAME FROM #TEMP ORDER BY SCHEMANAME,TBLNAME,COLNAME
OPEN C2
FETCH NEXT FROM C2 INTO @SCHEMANAME,@TABLENAME,@COLUMNNAME
WHILE @@FETCH_STATUS <> -1
BEGIN
SET @SQL = 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@SCHEMANAME) + '.' + QUOTENAME(@TABLENAME) + ' WHERE ' + QUOTENAME(@COLUMNNAME) + ' LIKE ''%' + @SEARCHSTRING + '%'')
INSERT INTO #RESULTS(SCHEMANAME,TBLNAME,COLNAME,SQL) VALUES(''' + @SCHEMANAME + ''',''' + @TABLENAME + ''',''' + @COLUMNNAME + ''',''
SELECT * FROM ' + QUOTENAME(@SCHEMANAME) + '.' + QUOTENAME(@TABLENAME) + ' WHERE ' + QUOTENAME(@COLUMNNAME) + ' LIKE ''''%' + @SEARCHSTRING + '%'''' '') ;'
PRINT @SQL
EXEC (@SQL)
FETCH NEXT FROM C2 INTO @SCHEMANAME,@TABLENAME,@COLUMNNAME
END
CLOSE C2
DEALLOCATE C2
END --@SEARCHBYCOLUMN <> 0
SELECT * FROM #RESULTS ORDER BY TBLNAME,COLNAME
END --PROC
GO
Lowell
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply