SQL Database Search or Find - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Database Search or Find

Author

Topic

gnidesign
Starting Member

2 Posts

Posted - 2010-08-19 : 09:34:49

Hey all, I hope this is the right forum for my question.
I have a SQL 2005 database that has some 500+ views and SP's.

The previous owner of the database apparenlty hard coded his date parameters, so I am wondering is there any way I can search the views and stored procedures for a specific term like '2009' instead of opening each one?

Thanks!

Bill @ gnidesign

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-08-19 : 09:41:34

Right click on database -> Tasks -> Generate Scripts...

Or use syscomments.

The first option is probably best if you want to replace them all, just be careful as if you do a drop create, you will lose any permissions unless you script those as well..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-19 : 13:57:58

i use this


SELECT OBJECT_NAME(object_id) AS Name,definition FROM sys.sql_modules m
JOIN sys.objects o
ON o.object_id = m.object_id
WHERE o.type IN ('V','P')
AND m.definition LIKE '%2009%'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-20 : 05:17:41

Also try

select specific_name from information_schema.routines
where object_definition(object_id(specific_name)) like '%2009%'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources