Posted By

CDIDevs on 12/31/09


Tagged

sql search mssql tsql String for Microsoft


Versions (?)

Who likes this?

3 people have marked this snippet as a favorite

g8rpal
Tyster
johnw


Search Database for string


 / Published in: SQL
 

  1. CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
  2.  
  3. SET NOCOUNT ON
  4.  
  5. DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @SearchStr nvarchar(100)
  6.  
  7. SET @SearchStr = 'photochecks.aspx'
  8.  
  9. SET @TableName = ''
  10. SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
  11.  
  12. WHILE @TableName IS NOT NULL
  13. BEGIN
  14. SET @ColumnName = ''
  15. SET @TableName =
  16. (
  17. SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
  18. FROM INFORMATION_SCHEMA.TABLES
  19. WHERE TABLE_TYPE = 'BASE TABLE'
  20. AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
  21. AND OBJECTPROPERTY(
  22. OBJECT_ID(
  23. QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
  24. ), 'IsMSShipped'
  25. ) = 0
  26. )
  27.  
  28. WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
  29. BEGIN
  30. SET @ColumnName =
  31. (
  32. SELECT MIN(QUOTENAME(COLUMN_NAME))
  33. FROM INFORMATION_SCHEMA.COLUMNS
  34. WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
  35. AND TABLE_NAME = PARSENAME(@TableName, 1)
  36. AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
  37. AND QUOTENAME(COLUMN_NAME) > @ColumnName
  38. )
  39.  
  40. IF @ColumnName IS NOT NULL
  41. BEGIN
  42. INSERT INTO #Results
  43. EXEC
  44. (
  45. 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
  46. FROM ' + @TableName + ' (NOLOCK) ' +
  47. ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
  48. )
  49. END
  50. END
  51. END
  52.  
  53. SELECT ColumnName, ColumnValue FROM #Results
  54.  
  55. DROP TABLE #Results

Report this snippet  

You need to login to post a comment.