"grep" a database

• tikoti

  Mr or Mrs. 500

  Points: 565

  More actions

  September 12, 2012 at 5:09 am

  #263966

  Hi!

  Is there anyway to "grep" a database in sql server 2008?

  I mean, look for a specific string in any table, in any column, in any row

  I know that it can be accomplished in mysql but I don't know if this is even possible in sql server

  Thank you!

• Lowell

  SSC Guru

  Points: 323522

  More actions

  September 12, 2012 at 5:38 am

  #1536662

  tikoti (9/12/2012)

  ---

  Hi!

  Is there anyway to "grep" a database in sql server 2008?

  I mean, look for a specific string in any table, in any column, in any row

  I know that it can be accomplished in mysql but I don't know if this is even possible in sql server

  Thank you!

  it depends on what you want to search for...if you mean the NAMES of the tables or columns, that's just selecting from sys.tables or sys.columns;

  if you want to search for a specific value contained in a column, it's going to involve an ugly, cursor performing lots and lots of table scans;

  here's a procedure i created that i've posted a number of times; note that if you have a MillionBillionRow table, it's going to take a long time to resolve just that one table...so don't run this on production.

  this does a table scan on every table in a given database; or optionally, a table scanfor every column in all the tables in every database.,

  so 1000 tables, is 1000 table scans, assuming they have varchar columns meeting the minimum length criteria.

  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

  ---

  --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!

• tikoti

  Mr or Mrs. 500

  Points: 565

  More actions

  September 12, 2012 at 6:16 am

  #1536674

  Thanks!

  It seems to me like the only way in sql server as far as I can see through the Internet

  I will use it with extreme precaution

  Thank you again!