Sql query to search a given text or string in all the columns of all the tables of a database in sql sever

Write a sql query or script which search or find a given or specifed text, string or data in all the columns of all the tables of a database in sql sever

ALTER PROC SerachAllTables(

    @Data VARCHAR(500),

    @DataType VARCHAR(20) = 'VARCHAR',

    @Table VARCHAR(500) = NULL)

AS

BEGIN

    SET NOCOUNT ON

    DECLARE @TableName VARCHAR(500)

    DECLARE @ColumnName VARCHAR(500)

    DECLARE @Query VARCHAR(2000)

    DECLARE Serach CURSOR  LOCAL FAST_FORWARD FOR  

    SELECT c.name,t.name  FROM sys.tables t

    INNER JOIN sys.columns c ON t.object_id = c.object_id

    INNER JOIN sys.types ty ON ty.system_type_id = c.system_type_id

    AND ty.name = @DataType

    AND ((@Table IS NOT NULL AND t.name = @Table) OR @Table IS NULL )

    CREATE TABLE ##SerachResult(

         TableName VARCHAR(500),

         ColumnName VARCHAR(500))

    OPEN Serach

    FETCH NEXT FROM Serach INTO @ColumnName,@TableName

    WHILE @@FETCH_STATUS = 0 BEGIN

         SET @Query = 'IF EXISTS(SELECT 1 FROM "' + @TableName +  '" WHERE "' + @ColumnName + '" LIKE  ''%' +

         @Data + '%'') INSERT INTO ##SerachResult SELECT ''' + @TableName + ''','''+ @ColumnName + ''''

   

         EXECUTE(@Query)

   

         FETCH NEXT FROM Serach INTO @ColumnName,@TableName

    END

    SELECT * FROM ##SerachResult

    DROP TABLE ##SerachResult

END

Serach statement

EXECUTE SerachAllTables 'price'

It will serach all the columns of type varchar of the all tables of database which contains or keep any data like price and list out the tables name with columns.

Serach statement

EXECUTE SerachAllTables '10','int','Student'

It will serach all the columns of type int in a given or speicfied table of database which contains or keep value 10 and list out the tables name with columns.