October 14, 2011 at 3:07 am
Hi,
I am looking for a script that will search through the whole database for a particular name in the data. I don't know the structure of the db. Any advice well appreciated.
Thanks
October 14, 2011 at 3:46 am
that will search through the whole database for a particular name in the data
Can you please rephrase your questions?
When you say data, does it mean table / view / column name?
Or
Does it mean the business data in some table?
Or
Does it mean text in Stored Procedure?
October 14, 2011 at 3:49 am
October 14, 2011 at 7:07 am
Thanks,
I don't know the structure at all, and the other team wanted me to do a general search on the database and try to catch the word for e.g. test. Pretty much a search on the whole db, in every table, column etc and search for a particular word.
October 14, 2011 at 7:23 am
DO NOT USE THIS ON PRODUCTION.
it builds a TABLE SCAN for every table which happens to contain a varchar/char/nvarchar/nchar type column.
a millionBillionRow table could cause this to gobble up some serious resources.
the optional parameter in it lets you drill down a bit more into the details, so it instead it does a TABLE SCAN FOR EACH varchar/char/nvarchar/nchar type column.
usage is simply EXEC sp_UGLYSEARCH 'test'
CREATE PROCEDURE sp_UGLYSEARCH
/*
--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_UGLYSEARCH 'Provisional'
-- EXEC sp_UGLYSEARCH '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_UGLYSEARCH '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%'
*/
@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
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
October 14, 2011 at 8:01 am
Lowell,
Fantastic and light faster.
October 14, 2011 at 10:53 am
I use this one-off script for similar things... not as formalized as Lowell's stored proc, but I just pull this up in SQL Server Management Studio, edit it to specify the string I want to search for, and run it... you can use wild-cards or specify exact matches, etc. Just set @value to whatever you want to look for. It PRINTS out the locations where it finds the string.
DECLARE @tableName sysname
DECLARE @columnName sysname
DECLARE @value varchar(100)
DECLARE @sql varchar(2000)
DECLARE @sqlPreamble varchar(100)
SET @value = '%test%' -- *** Set this to the value you're searching for *** --
SET @sqlPreamble = 'IF EXISTS (SELECT 1 FROM '
DECLARE theTableCursor CURSOR FAST_FORWARD FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME NOT LIKE '%temp%' AND TABLE_NAME != 'dtproperties' AND TABLE_NAME != 'sysdiagrams'
ORDER BY TABLE_NAME
OPEN theTableCursor
FETCH NEXT FROM theTableCursor INTO @tableName
WHILE @@FETCH_STATUS = 0 -- spin through Table entries
BEGIN
DECLARE theColumnCursor CURSOR FAST_FORWARD FOR
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tableName AND (DATA_TYPE = 'nvarchar' OR DATA_TYPE = 'varchar')
ORDER BY ORDINAL_POSITION
OPEN theColumnCursor
FETCH NEXT FROM theColumnCursor INTO @columnName
WHILE @@FETCH_STATUS = 0 -- spin through Column entries
BEGIN
SET @sql = @tableName + ' WHERE ' + @columnName + ' LIKE ''' + @value +
''') PRINT ''Value found in Table: ' + @tableName + ', Column: ' + @columnName + ''''
EXEC (@sqlPreamble + @sql)
FETCH NEXT FROM theColumnCursor INTO @columnName
END
CLOSE theColumnCursor
DEALLOCATE theColumnCursor
FETCH NEXT FROM theTableCursor INTO @tableName
END
CLOSE theTableCursor
DEALLOCATE theTableCursor
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply