Search Code in multiple Views

Question

Post reply

Search Code in multiple Views

dan-572483

SSCoach

Points: 15870
More actions
May 22, 2013 at 9:56 am

#394830

I am migrating a group of related databases to a different server, and am changing the name of one of the databases. There are many views in the other databases, some of which may reference tables & views in the database with the changed name. So I'll need to change the database name in the view queries when it occurs.
Some of these DBs contain dozens of views. Is there any way to query ALL of views in a DB to identify references original DB name other than opening & searching or testing each view individual view?

Viewing 8 posts - 1 through 8 (of 8 total)

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

Sean Lange SSC Guru Points: 286609 More actions · Answer 1

Take a look at RedGate's SQL Search. It is free and totally awesome for this type of thing.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Robert Klimes SSCoach Points: 17865 More actions · Answer 2

I tend to use this when looking for references in views, procs, functions.

SELECT *

FROM sys.sql_modules

WHERE definition like '%<search string>%'

Bob
-----------------------------------------------------------------------------
How to post to get the best help[/url]

Sean Lange SSC Guru Points: 286609 More actions · Answer 3

Robert klimes (5/22/2013)
I tend to use this when looking for references in views, procs, functions.
SELECT *
FROM sys.sql_modules
WHERE definition like '%<search string>%'

I would recommend that you too check out SQL Search. It makes this type of thing super easy. Not to sound like a fan boy or something but it is something I use daily. And they are the sponsors of SSC. 😀

http://www.red-gate.com/products/sql-development/sql-search/%5B/url%5D

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Robert Klimes SSCoach Points: 17865 More actions · Answer 4

It is a pretty slick tool. It does make for much easier searching than sys.sql_modules. 🙂

Bob
-----------------------------------------------------------------------------
How to post to get the best help[/url]

dan-572483 SSCoach Points: 15870 More actions · Answer 5

dan-572483

SSCoach

Points: 15870

May 22, 2013 at 1:02 pm

#1617552

sys.sql_modules worked great. Thanks!

Lowell SSC Guru Points: 323546 More actions · Answer 6

OK I stopped dragging my feet, and broke down and downloaded Redgate's SQL Search;

I admit it, i already see it's a great tool; very nice. Much Quicker and easier to access than any SQL i would write to do the same thing, plus it has a nice presentation.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Lange SSC Guru Points: 286609 More actions · Answer 7

Lowell (5/22/2013)
OK I stopped dragging my feet, and broke down and downloaded Redgate's SQL Search;
I admit it, i already see it's a great tool; very nice. Much Quicker and easier to access than any SQL i would write to do the same thing, plus it has a nice presentation.

And it can search all databases at once. I used to write my own sql for this type of stuff too. Then I tried that tool and wondered how I went without it all those years.

Sort of like the SSMSToolPack. 😉

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/