There are situations where you have to search for a text/string in sql server, to find out from which table, column & row the data persists and coming to UI.

There are only 2 steps to do that,

  1. Need to create a stored procedure.
  2. Execute the stored procedure with valid input parameter(s).

Usage of Code

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

Result will show the Table Name, Column Name, Column Value (your search text).