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:
please provide the details of how to implement this in the front end ie., in the html page
Post a Comment