September 1, 2010 at 1:22 pm
So I have a database server with several different databases on it.
I have the name of an object, say a view. Basically I need a query to see what database on this database server that the view is on.
I can't seem to find the relation between sys.all_objects and sys.databases
Anyone know?
September 1, 2010 at 1:44 pm
You can use the undocumented sp_MSForeachdb procedure and then do something like:
EXEC sp_MSForeachdb '
USE ?
SELECT DB_NAME()
SELECT * from sysobjects
WHERE name like "%objectname%"'
'
Then this should only return data back for the database that has the object. I use the SELECT DB_NAME() to get what database the SELECT statement is called against.
There is probably a cleaner way of doing it, this is just something I thought might work :hehe:
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
September 1, 2010 at 2:11 pm
This should get you started.
declare @sql varchar(max)
select @sql =
'
declare @t table ([objectname]NVARCHAR(1000), seq int not null identity(1,1) primary key clustered )
'
select
@sql = @sql +
'
insert into @t ( [objectname] )
select quotename(TABLE_CATALOG)+''.''+quotename(TABLE_SCHEMA)+''.''+quotename(TABLE_NAME)
from '+quotename(a.name)+'.information_schema.tables
where TABLE_TYPE = ''VIEW''
'
from
sys.databases a
where
state_desc = 'online' and
user_access_desc = 'multi_user'
order by
a.name
select @sql = @sql+
'
select * from @t order by seq
'
exec ( @sql )
September 1, 2010 at 5:12 pm
You could try SQL Search, which is a free tool we offer here at Red Gate.
http://www.red-gate.com/products/SQL_Search/
I'd be interested to get your feedback if it doesn't match your requirements.
Regards,
David
September 2, 2010 at 8:40 am
Thanks for the quick response on solutions, it really helped.
Michael's solution ended up being the best for what I was doing.
FYI...all the developers have been trying to convince the higher ups to get the red gate source control...I've heard good things about it.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply