Find correct db where view is - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Find correct db where view is

Author

Topic

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2008-03-19 : 08:10:25

Hi,

Is there a way to write a select statement in order to find out which database a certain view is in? One way is to write "select * from view" and switch between available databases until "Invalid object name 'vw_X'" does not show.

Any other ways?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-19 : 08:11:20

You can query INFORMATION_SCHEMA.VIEWS view.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Kurmanc
Yak Posting Veteran

92 Posts

Posted - 2008-03-19 : 08:13:38

Perfect, thanks for your quick reply.

However, doesn't that query force you to choose the correct database?

There are more than 40 databases in the dropdown list. It would be very nice to write a query without selecting the correct database. What I mean is that SQL should show a result from a query regardless of which database is chosen and list which correct database the view is available in.

Regards

PABluesMan
Starting Member

26 Posts

Posted - 2008-03-24 : 15:26:25

Here are a couple of different methods to try.

/* Quick 'n' dirty method using the undocumented [sp_MSforeachdb] stored procedure */
CREATE TABLE #Views (DBName VARCHAR (128), SchemaName VARCHAR (128), ViewName VARCHAR (128))
EXEC sp_MSforeachdb 'INSERT #Views (DBName, SchemaName, ViewName)
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME FROM ?.INFORMATION_SCHEMA.VIEWS'

SELECT * FROM #Views

DROP TABLE #Views

/* Preferred method, as there is no risk of deprecation due to the undocumented */
/* [sp_MSforeachdb] stored procedure being removed from future versions. Also, */
/* this gives you the freedom to be a bit more selective with your databases */
/* (for example, omitting system databases as I have done here). */
DECLARE @Schema VARCHAR (128)
DECLARE @DBName VARCHAR (128)
DECLARE @ViewName VARCHAR (128)
DECLARE @SQL NVARCHAR (4000)

CREATE TABLE #Views (DBName VARCHAR (128), SchemaName VARCHAR (128), ViewName VARCHAR (128))

DECLARE curDB CURSOR STATIC FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE dbid > 4
ORDER BY name
OPEN curDB
FETCH FIRST FROM curDB INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'INSERT #Views (DBName, SchemaName, ViewName)
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME FROM ' + @DBName + '.INFORMATION_SCHEMA.VIEWS'

EXEC sp_executesql @SQL

FETCH NEXT FROM curDB INTO @DBName
END
CLOSE curDB
DEALLOCATE curDB

SELECT * FROM #Views

DROP TABLE #Views

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources