This stored procedure creates a table in the database when I make the select * into, and because of that, when there is more than one user, an error appears and says SearchTMP table already exists, this happens even if I make a drop of that table. So I decided to make a temporary table, as the following code shows:
INSERT INTO @SQLTbl(Tablename, WHEREClause)
SELECT QUOTENAME(SCh.name) + '.' + QUOTENAME(ST.NAME),
(
SELECT '[' + SC.Name + ']' + ' LIKE ''' + REPLACE(SearchSTR.SearchString,'''','''''') + ''' OR ' + CHAR(10)
FROM SYS.columns SC
JOIN SYS.types STy
ON STy.system_type_id = SC.system_type_id
AND STy.user_type_id =SC.user_type_id
CROSS JOIN @SearchStringTbl SearchSTR
WHERE STY.name in ('varchar','char','nvarchar','nchar','text')
AND SC.object_id = ST.object_id
ORDER BY SC.name
FOR XML PATH('')
)
FROM SYS.tables ST
JOIN @CheckTableNames chktbls
ON chktbls.Tablename = ST.name
JOIN SYS.schemas SCh
ON ST.schema_id = SCh.schema_id
AND Sch.name = chktbls.Schemaname
WHERE ST.name <> '#MyTempTable' -- it was SearchTMP
GROUP BY ST.object_id, QUOTENAME(SCh.name) + '.' + QUOTENAME(ST.NAME);
UPDATE @SQLTbl SET SQLStatement = 'SELECT * INTO #MyTempTable FROM ' + Tablename + ' WHERE ' + substring(WHEREClause,1,len(WHEREClause)-5)
Since I didn't want to declare or create the table previously, the temptable will be created in the select * into. But when I try to run the procedure by filling the parameters it says:
>Msg 208, Level 16, State 0, Procedure SP_SearchTables_TEST, Line 265, Invalid object name #MyTempTable
The entire stored procedure:
ALTER PROCEDURE [dbo].[SearchTables_TEST] @SearchStr NVARCHAR(60) ,@GenerateSQLOnly Bit = 0 ,@SchemaNames VARCHAR(500) ='%' AS
SET NOCOUNT ON
DECLARE @MatchFound BIT
SELECT @MatchFound = 0
DECLARE @CheckTableNames Table
(
Schemaname sysname
,Tablename sysname
)
DECLARE @SearchStringTbl TABLE
(
SearchString VARCHAR(500)
)
DECLARE @SQLTbl TABLE
(
Tablename SYSNAME
,WHEREClause VARCHAR(MAX)
,SQLStatement VARCHAR(MAX)
,Execstatus BIT
)
DECLARE @SQL VARCHAR(MAX)
DECLARE @TableParamSQL VARCHAR(MAX)
DECLARE @SchemaParamSQL VARCHAR(MAX)
DECLARE @TblSQL VARCHAR(MAX)
DECLARE @tmpTblname sysname
DECLARE @ErrMsg VARCHAR(100)
IF LTRIM(RTRIM(@SchemaNames)) =''
BEGIN
SELECT @SchemaNames = '%'
END
IF CHARINDEX(',',@SchemaNames) > 0
SELECT @SchemaParamSQL = 'SELECT ''' + REPLACE(@SchemaNames,',','''as SchemaName UNION SELECT ''') + ''''
ELSE
SELECT @SchemaParamSQL = 'SELECT ''' + @SchemaNames + ''' as SchemaName '
SELECT @TblSQL = 'SELECT SCh.NAME,T.NAME
FROM SYS.TABLES T
JOIN SYS.SCHEMAS SCh
ON SCh.SCHEMA_ID = T.SCHEMA_ID
INNER JOIN [DynaForms].[dbo].[Enums_Tables] et on
(et.Id = T.NAME COLLATE Latin1_General_CI_AS) '
INSERT INTO @CheckTableNames
(Schemaname,Tablename)
EXEC(@TblSQL)
IF NOT EXISTS(SELECT 1 FROM @CheckTableNames)
BEGIN
SELECT @ErrMsg = 'No tables are found in this database ' + DB_NAME() + ' for the specified filter'
PRINT @ErrMsg
RETURN
END
IF LTRIM(RTRIM(@SearchStr)) =''
BEGIN
SELECT @ErrMsg = 'Please specify the search string in @SearchStr Parameter'
PRINT @ErrMsg
RETURN
END
ELSE
BEGIN
SELECT @SearchStr = REPLACE(@SearchStr,',,,',',#DOUBLECOMMA#')
SELECT @SearchStr = REPLACE(@SearchStr,',,','#DOUBLECOMMA#')
SELECT @SearchStr = REPLACE(@SearchStr,'''','''''')
SELECT @SQL = 'SELECT ''' + REPLACE(@SearchStr,',','''as SearchString UNION SELECT ''') + ''''
INSERT INTO @SearchStringTbl
(SearchString)
EXEC(@SQL)
UPDATE @SearchStringTbl
SET SearchString = REPLACE(SearchString ,'#DOUBLECOMMA#',',')
END
INSERT INTO @SQLTbl(Tablename,WHEREClause)
SELECT QUOTENAME(SCh.name) + '.' + QUOTENAME(ST.NAME),
(
SELECT '[' + SC.Name + ']' + ' LIKE ''' + REPLACE(SearchSTR.SearchString,'''','''''') + ''' OR ' + CHAR(10)
FROM SYS.columns SC
JOIN SYS.types STy
ON STy.system_type_id = SC.system_type_id
AND STy.user_type_id =SC.user_type_id
CROSS JOIN @SearchStringTbl SearchSTR
WHERE STY.name in ('varchar','char','nvarchar','nchar','text')
AND SC.object_id = ST.object_id
ORDER BY SC.name
FOR XML PATH('')
)
FROM SYS.tables ST
JOIN @CheckTableNames chktbls
ON chktbls.Tablename = ST.name
JOIN SYS.schemas SCh
ON ST.schema_id = SCh.schema_id
AND Sch.name = chktbls.Schemaname
WHERE ST.name <> '#MyTempTable' -- it was SearchTMP
GROUP BY ST.object_id, QUOTENAME(SCh.name) + '.' + QUOTENAME(ST.NAME);
UPDATE @SQLTbl SET SQLStatement = 'SELECT * INTO #MyTempTable FROM ' + Tablename + ' WHERE ' + substring(WHEREClause,1,len(WHEREClause)-5)
DELETE FROM @SQLTbl
WHERE WHEREClause IS NULL
DECLARE @output TABLE (Id VARCHAR(50), Name VARCHAR(100))
WHILE EXISTS (SELECT 1 FROM @SQLTbl WHERE ISNULL(Execstatus ,0) = 0)
BEGIN
SELECT TOP 1 @tmpTblname = Tablename , @SQL = SQLStatement
FROM @SQLTbl
WHERE ISNULL(Execstatus ,0) = 0
IF @GenerateSQLOnly = 0
BEGIN
IF OBJECT_ID('#MyTempTable','U') IS NOT NULL -- this line was uncomment
DROP TABLE #MyTempTable -- this line was uncomment
EXEC (@SQL)
IF EXISTS(SELECT 1 FROM #MyTempTable) -- It was like this: SearchTMP
BEGIN
SELECT @MatchFound = 1
INSERT INTO @output (Id, Name)
Select * from [DynaForms].[dbo].[Enums_Tables] where id in (SELECT parsename(@tmpTblname,1) FROM #MyTempTable) -- It was like this: SearchTMP
END
END
ELSE
BEGIN
PRINT REPLICATE('-',100)
PRINT @tmpTblname
PRINT REPLICATE('-',100)
PRINT replace(@SQL,'INTO #MyTempTable','')
END
UPDATE @SQLTbl
SET Execstatus = 1
WHERE Tablename = @tmpTblname
END
SELECT * FROM @output
IF @MatchFound = 0
BEGIN
SELECT @ErrMsg = 'No Matches are found in this database ' + DB_NAME() + ' for the specified filter'
PRINT @ErrMsg
RETURN
END
SET NOCOUNT OFF
