Reference multiple databes sys.all_views

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

SQL Server 2005 Forums

Transact-SQL (2005)

Reference multiple databes sys.all_views

Author

Topic

dvanatta
Starting Member

6 Posts

Posted - 2009-06-04 : 11:50:23

I need to reference sys.all_view in multiple databases. I tried using "use dbname" in my proc but that's not allowed. I tried using a three part qualifier in the select statement like :

select *
from MyDB.sys.all_views

but it does not work.

In my proc, the database name is passed in as a parameter so I need to concatenate the db name to sys.all_views but I can't get the syntax correct.

Any suggestions?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-04 : 12:54:26

you cant change database context inside a procedure. is your attempt to run this against each db in your server?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-05 : 02:23:08

You need to use dynamic sql

EXEC('SELECT * FROM '+@MyDB+'.sys.all_views')

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources