March 22, 2013 at 8:28 am
I've read several articles I found online suggesting various ways to do this, but I thought I would check here. Basically, I need to find every instance of 'COMM" in one of our databases. Any help is appreciated!
EDIT: I'm running 2008 R2 in case that helps.
March 22, 2013 at 8:54 am
ir depends on if you are looking for a string, a reference to an object(table/view/etc), or a reference to a column.
-- a string
select * from sys.all_sql_modules where definition like '%COMM%'
-- an object
--Objects:
SELECT objz.name,
fn.*
FROM sys.objects objz
CROSS APPLY sys.dm_sql_referenced_entities(schema_name(schema_id) + '.' + objz.name,'OBJECT')fn
WHERE referenced_entity_name ='COMM'
-- a column name:
SELECT objz.name,
fn.* ,
colz.*
FROM sys.objects objz
CROSS APPLY sys.dm_sql_referenced_entities(schema_name(schema_id) + '.' + objz.name,'OBJECT')fn
LEFT OUTER JOIN sys.columns colz ON objz.object_id = colz.object_id
AND colz.column_id = referenced_minor_id
--WHERE referenced_entity_name ='GMACT'
WHERE colz.name ='COMM'
Lowell
March 22, 2013 at 8:56 am
Assuming like Lowell said you are looking for these references in code and not in the actual values you could also just install SQL Search from RedGate. It is a free SSMS extension that does this quite nicely.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 22, 2013 at 9:07 am
Those are great, and exactly what I'm looking for. But I'm also wanting to check for values in rows. So literally looking for everything.
I don't even know if checking in rows is possible (and it it is I'm sure its something that would be really resource intensive). But I thought I would check.
March 22, 2013 at 9:08 am
The big issue with this kind of search, is that it must involve a TABLE SCAN of every table in the database.
If you have any tables with millions of rows, this is going to be excruciatingly slow.
this kind of solution is indended for a DBA investigation,and should never be given to or deployed to end users. It could easily cripple your server as it tries to process billions of rows across lots of tables.
Another long time poster on these forums, Sean Lange and myself have posted this pair of solutions many times. His does some things differently than mine and depending on your situation may be better suited. Both these scripts have a huge caveat: It is slow, it will cripple your sql server while it is running. DO NOT run this in production!!!
Sean Lange
I put this next piece of code together several years for just this sort of thing. DO NOT run this in production. Expect it to take a long time. It will eventually give you the results you are after.
declare @table_name varchar(2000)
declare @sSQL nvarchar(4000)
declare @result varchar(20)
declare @column_name varchar(2000)
declare @SearchVal varchar(200)
set @SearchVal = '%your search val here%'
declare @ColName varchar (250)
set @ColName = '%use this if you want to limit to a naming convention on the columns to search (i.e. email)%'
declare SearchList cursor for
select distinct so.name,sc.name from syscolumns sc
inner join sysobjects so on sc.id = so.id
where sc.name like @ColName
and so.type = 'U'
open SearchList
fetch next from SearchList into @table_name, @column_name
while(@@fetch_status = 0)
begin
select @sSQL = 'if exists (select ''' + @table_name + ''' as TableName,' + @column_name + ' from ' + @table_name + ' where ' + @column_name + ' like ''' + @SearchVal + ''') select ''' + @table_name + ''' as TableName,' + @column_name + ' from ' + @table_name + ' where ' + @column_name + ' like ''' + @SearchVal + ''''
exec sp_executesql @sSQL
--select @ssql
fetch next from SearchList into @table_name, @column_name
end
close SearchList
deallocate SearchList
This is my version, parameterized as a procedure; it's going to return SQL statements that allow a drill down to the offending tables/columns and their data.
CREATE PROCEDURE sp_UGLYSEARCH
/*
--Purpose: to search every string column in a database for a specific word
--returns sql statement as a string which identifies 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
SELECT * FROM #RESULTS ORDER BY TBLNAME,COLNAME
END --PROC
GO
Lowell
March 22, 2013 at 9:18 am
Thanks for the nod Lowell.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 22, 2013 at 9:19 am
Yep, that's perfect. And I love that you named yours "uglysearch" 🙂 Indeed, but necessary. Thanks! And no production runs - I promise!
March 22, 2013 at 9:24 am
That's quite a block of code. I thought of the database objects, but searching every column got my head spinning with how long it would take. I don't even want to think about what it would do to the server.
AND colz.max_length >= LEN(@SEARCHSTRING)
My highest compliments on this simple line limiting the length of the fields you're searching. At least you won't search every string column. 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply