Please help fix stored procedure

USE [Database_name] GO /****** Object: StoredProcedure [dbo].[SearchAllTables] Script Date: 2/19/2019 3:27:09 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROC [dbo].[SearchAllTables] ( @SearchStr nvarchar(100) ) AS BEGIN

DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110) SET @TableName = '' SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL BEGIN SET @ColumnName = '' SET @TableName = ( SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName AND OBJECTPROPERTY( OBJECT_ID( QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) ), 'IsMSShipped' ) = 0 )

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL) BEGIN SET @ColumnName = ( 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 )

IF @ColumnName IS NOT NULL BEGIN INSERT INTO @Results EXEC ( 'SELECT ''' + @TableName + ''' LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2 ) END END END

SELECT distinct ColumnName, ColumnValue into #results1 FROM @Results END

select schema_name(tab.schema_id) as [schema_name], --pk.[name] as pk_name, --ic.index_column_id as column_id, col.[name] as column_name, tab.[name] as table_name into #results from sys.tables tab inner join sys.indexes pk on tab.object_id = pk.object_id and pk.is_primary_key = 1 inner join sys.index_columns ic on ic.object_id = pk.object_id and ic.index_id = pk.index_id inner join sys.columns col on pk.object_id = col.object_id and col.column_id = ic.column_id order by schema_name(tab.schema_id), pk.[name], ic.index_column_id

declare @table varchar(100) set @table = (SELECT '[' + SCHEMA_NAME + ']' + '.' + '[' + TABLE_NAME + ']' AS ColumnZ FROM #results) -- this gives the schema plus table

declare @column varchar(30) set @column = (select column_name from #results) --this gives the columns

select A.table_name,

--this store procedure will create two different temp tables(#results and #results!) which will be used --to determine what table will be used to run a delete statement, if the schema_name.table_name(#results1) is in #results, --then a delete statement will catch the passed in varchar and run it against the matching tables