string search in objects

Question

Post reply

string search in objects

Tara-1044200

SSCoach

Points: 15951
More actions
April 6, 2011 at 6:45 am

#389216

I have 100's of views in a database but would like a list of views htat has the string "where empid=", how can i do that?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

You can find view definitions in the sys.sql_modules system view.

John

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 2

Like John wrote in his replay you can query sys.sql_modules, but don't forget that you might miss some views. For example if the view checks the value of empid, but it is written in a different way (more then one space separates between empid and the equal sign, tab instead of space). Also there could be the situation that empid is not used as first criteria in the view. Because of this I would use a different code:

..where definition like '%where%empid%=%'

I would also join sys.sql_modules with sys.objects in order to make sure that I'll get only views.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Mark Cowne One Orange Chip Points: 26953 More actions · Answer 3

Try this

SELECT name

FROM sys.views

WHERe OBJECT_DEFINITION(object_id) LIKE '%where empid=%'

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 4

Hi

Try this

Select * from sys.sysobjects where xtype='v' and id in(

Select id from sys.syscomments where text like '%where empid=%')

instead of V you can put P also to derive SP's also.

Thanks
Parthi