Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Find All References to View

I've got various databases, and what to be sure I am removing something (a view in this case) that is truly orphaned. Is the the correct SQL to be using:

SELECT r.routine_name, 
       r.routine_definition
  FROM INFORMATION_SCHEMA.ROUTINES r
 WHERE r.routine_definition LIKE '%my_view_name%' 

The problem with it is that these references aren't picking up declarations in stored procedures, and I don't know what else.

I found the SO Question I'd remembered, but it's not helping either. This:

SELECT t.*
  FROM SYSCOMMENTS t
 WHERE CHARINDEX('my_view_name', t.text) > 0

...is close. I get the body of the stored procedure that I know is using the view, but I'm having trouble getting the actual procedure name.

like image 991
OMG Ponies Avatar asked Oct 01 '09 19:10

OMG Ponies


2 Answers

You have one option only.

select
    object_name(m.object_id), m.*
from
    sys.sql_modules m
where
    m.definition like N'%my_view_name%'

syscomments and INFORMATION_SCHEMA.routines have nvarchar(4000) columns. So if "myViewName" is used at position 3998, it won't be found. syscomments does have multiple lines but ROUTINES truncates.

like image 172
gbn Avatar answered Oct 26 '22 01:10

gbn


Your method is not fully correct. Read this article:

http://www.mssqltips.com/tip.asp?tip=1294

Your method will not return any result if another view uses this view.

SQL Server 2008 has special view (sys.dm_sql_referencing_entities), here it is not that easy.

like image 22
LukLed Avatar answered Oct 25 '22 23:10

LukLed