Tuesday, September 11, 2018

Check for Tables used in StoreProcedures


Check if there is a specific table that is used in the store procedure

USE [databasename]
GO
DECLARE @Tablename VARCHAR(255) = 'tablename'

SELECT DISTINCT so.name
  FROM syscomments sc
  JOIN sysobjects so 
    ON sc.id = so.id
 WHERE sc.[TEXT] LIKE '%' + @Tablename + '%'

----Option 2
SELECT DISTINCT o.name, o.xtype
  FROM syscomments c
  JOIN sysobjects
    ON c.id = o.id
 WHERE c.[TEXT] LIKE '%' + @Tablename + '%'

No comments:

Post a Comment