CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Truecolor
Truecolor

Reputation: 469

How to find if a list of table names exist in one single stored procedure?

I have a temp table with 2 columns, one that lists all the table names in the database and the other column to show if it exists in one stored procedure or not. I want to loop the list of table names inside the stored procedure and see if they exist or not. If it exists, I will update the second column in the temp table with 'Yes'.

Is it something that is possible?

This is what I have so far, but when I run it, the results show that none of the table names in the list exist in the stored procedure when that's not true. 

DECLARE @TableName VARCHAR(50)

CREATE TABLE #TablesList 
(
     TableName VARCHAR(50), 
     TableExists VARCHAR(3)
)

INSERT INTO #TablesList (TableName)
    SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG='DB_Name'
    ORDER BY TABLE_NAME

DECLARE TableNames_Cursor Insensitive CURSOR FOR
    SELECT TableName        
    FROM #TablesList

OPEN TableNames_Cursor

FETCH NEXT FROM TableNames_Cursor INTO @TableName

-- read the cursor rows, and process accordingly -----------------------------------------
WHILE (@@Fetch_Status) = 0 
BEGIN
    --Update #TablesList
    --Set TableExists = 'Yes'
    -- where 
    SELECT
        obj.TableName
    FROM
        (SELECT tl.TableName
         FROM sysobjects obj
         JOIN syscomments com ON com.id = obj.id
         JOIN #TablesList tl ON tl.TableName = @TableName
         WHERE obj.name = 'sProc_Name'
           AND com.text LIKE '%' + @TableName + '%') obj
    ORDER BY 
        obj.TableName       

    FETCH NEXT FROM TableNames_Cursor INTO @TableName
END  -- WHILE (@@Fetch_Status) = 0 

DEALLOCATE TableNames_Cursor

DROP TABLE #TablesList

Upvotes: 2

Views: 94

Answers (2)

Dan Guzman
Dan Guzman

Reputation: 46241

It's good that you've found a solution that works and avoids the cursor but one should avoid the deprecated sysobjects and syscomments catalog views.

Below is an example that uses the newer catalog views along with some additional enhancements. No need for the temp table unless you use it later for purposes other than returning the result.

SELECT 
      sch.Name AS TableSchemaName
    , tbl.Name AS TableName
    , CASE WHEN EXISTS (
        SELECT 1
        FROM sys.objects AS obj
        JOIN sys.sql_modules AS com on com.object_id = obj.object_id
        WHERE
            obj.name = 'storedProc_Name'
            AND com.definition LIKE '%'+ tbl.name +'%')
      THEN 'Yes' ELSE 'No' END AS TableUsed
FROM sys.tables AS tbl
JOIN sys.schemas AS sch ON sch.schema_id = tbl.schema_id
WHERE tbl.name NOT LIKE 'MLS%';

Upvotes: 2

Truecolor
Truecolor

Reputation: 469

I was able to write a simpler query that worked

select 
tbl.Name,
isnull((Select top 1
  'Yes'
  From
    sysobjects obj
    join syscomments com on com.id = obj.id
  Where obj.name = 'storedProc_Name'
  and com.Text Like '%'+ tbl.name +'%'), 'No') as Used
  into #TblsUsed
from sys.tables tbl
where tbl.name not like 'MLS%'

select *
from #TblsUsed
where #TblsUsed.Used = 'Yes'
order by #TblsUsed.name

Upvotes: 1

Related Questions