Jan 3, 2014

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.

1 comment:

  1. please provide the details of how to implement this in the front end ie., in the html page

    ReplyDelete