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.
| Author |
Topic |
|
Samadhi69
Starting Member
20 Posts |
 Posted - 2011-09-23 : 13:09:10
|
| I'm using the term "mirror" since I don't know what it's actual term is. I VPN to a client and they have all of their tables "mirrored" as views on the server/db I connect to.I need to routinely check these to see if they've been changed/updated or if new tables have been added (they *never* tell me). The problem is that there are several hundred and there are also other developers who create regular views in the same place.For example, say there are the tables "Company" and "Employee" that are being mirrored and a view that uses information from those "mirrors". The mirrors would look like this in design:SQL Pane: Select [Field1], et al From XXX.dbo.Company --(or Employee)Diagram Pane: The table shows as [Company_1]An actual view would look something like this:SQL Pane: Select * From dbo.Employee --(no XXX. here)Diagram Pane: The table shows as Employee (dbo)So it's easy to see whether something is a "mirror" view or not *if* I go to design it. However, since there are a large number of them I would really like to make it more automatic.I've gone through sysobjects and syscolumns and I haven't found any way to see the difference.Any ideas/pointers would be most appreciated.If con is the opposite of pro, what's the opposite of progress? |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-09-23 : 13:21:38
|
| Don't quite understand your question, but you can query sys.tables and sys.views.And what you're calling a "mirror" is simply called a "view." If they've placed views over everything, it's to restrict access to the actual tables. |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-09-23 : 13:26:04
|
I got it. They've created views from other databases in this one.Assuming the only schema used in these cross-database views is "dbo" AND they aren't prefixing "local" views with the database name then this will work:SELECT v.nameFROM sys.sql_modules mJOIN sys.views vOn v.object_id = m.object_idWHERE definition like '%.dbo.%' |
|
|
|
Samadhi69
Starting Member
20 Posts |
Posted - 2011-09-26 : 12:52:40
|
| You got it exactly, thank you. I wish they'd made it so our logins (mine and the other few developers for our company) used a different schema than theirs then it would have been easier.If con is the opposite of pro, what's the opposite of progress? |
|
|
|
|
|
|
How to tell "mirror" view from other views