create PROC SearchSpecificTable
(
@tableName nvarchar(100),
@SearchStr nvarchar(100)
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @columnName NVARCHAR(100), @SearchStr2 nvarchar(128)
DECLARE @sql NVARCHAR(MAX) = 'SELECT * FROM ' + @tableName +' WHERE '
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
DECLARE columns CURSOR FOR
SELECT sys.columns.name FROM sys.tables
INNER JOIN sys.columns ON sys.columns.object_id = sys.tables.object_id
WHERE sys.tables.name = @tableName
OPEN columns
FETCH NEXT FROM columns
INTO @columnName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = @sql + @columnName + ' LIKE ' + @SearchStr2 + ' OR '
FETCH NEXT FROM columns
INTO @columnName
END
CLOSE columns;
DEALLOCATE columns;
SET @sql = LEFT(RTRIM(@sql), LEN(@sql) - 2) -- remove last OR
EXEC(@sql)
END
