SQL: query ALL view definitions in all databases

warrenrapson
warrenrapson used Ask the Experts™
on
Hi all,

Any way to apply the below SQL to all the DBs on my server instance?

select db_name() as DBName, object_definition(object_id) as ViewDef
from sys.objects
where object_definition(object_id) like '%MY_VALUE%'

I was dabbling with sp_msforeachdb but did not get very far...
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
create table #t (dbname sysname, name sysname, ViewDef varchar(max))
exec sp_msforeachdb 'use ?; insert #t select db_name() as DBName, name, object_definition(object_id) as ViewDef from sys.objects'
select * from #t where ViewDef like '%MY_VALUE%'
Principal Engineer
Commented:
The below is modified code of the above cyberkiwi's code

His code will also insert objects with null definition
So you can add a simple where clause in his code to minimize execution time:


create table #t (dbname sysname, name sysname, ViewDef varchar(max))
exec sp_msforeachdb 'use ?; insert #t select db_name() as DBName, name, object_definition(object_id) as ViewDef from sys.objects where object_definition(object_id) is not null'
select from #t where ViewDef like '%%'

Commented:
Hi,

If I understood right you can use the following to get all views in all databases too.

select * from INFORMATION_SCHEMA.VIEWS

Author

Commented:
Thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial