Identyfying which tables have gone into a view
-
I am trying to find the list of tables that make up any particular view in SQL server 2000. The information schema - VIEW_TABLE_USAGE would be the best tool, if only the sysdepends table worked reliably! When I use the schema I get some but not all of my views.
Has anyone got a solution, prefably one without cursors, that can identitfy the source tables for a view?
I have used the following SQL, but unfortunately it gives too many results:
SELECT VIEWS.name AS VIEW_NAME,
TABLES.name AS TABLE_NAME,
VIEW_SQL.text
FROM sysobjects VIEWS
INNER JOIN
syscomments VIEW_SQL
ON VIEWS.id = VIEW_SQL.id
INNER JOIN
sysobjects TABLES
ON VIEW_SQL.text LIKE '%.' + TABLES.name + '%'
WHERE (VIEWS.xtype = 'V')
AND (TABLES.xtype = 'U')
ORDER BY VIEWS.name, TABLES.name
Justin
-
The problem with this approach is that if you have the tables Users and UsersPermissions and that the view uses the table UsersPermissions, then the like will catch a hit for both UsersPermissions and Users. This is something really tricky to avoid (never actually made it work 100%) without advanced string manipulation that the server doesn't provide.
-
I've tried to get the catalog information from "the other side". Looking for tables in syscomments and then joining to views.
select
v.name as View_Name,
t.name as Table_Name,
c.text as View_Definition
from sysobjects t
inner join syscomments c
on c.text like '%.' + t.name + '%'
inner join sysobjects v
on v.id = c.id
and v.xtype = 'V'
where t.xtype = 'U'
order by v.name, t.name
Of curse the View_Name and the View_Definition repeats for each used Table_Name.
-
if you alter a view to make it use it's same definition, wouldn't sysdepends get updated with the correcct dependancies?
i would think that simply altering all views would 'refresh' the view and you could use you original plan.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
In theory it would work... but you could lose object to object dependences. But if you're only after the table use then you're alright.
-
Remi,
by object to object dependencies, do you mean for example if i had a view or stored proc that was dependant on another view? I would think you would be right...you'd end up having to refresh all the objects in dependency order right?...whew..that's annoying...
if you don't know stored proc x has a dependency on a view, and recreate them out of order of dependencies...but you are trying to find the dependencies in the first place...oww my head hurts.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Precisely
. I had my head hurt a long time over that one. I found out it was less trouble trying to find matches manually once the autamated part couldn't sort everything out by itself. But it can get you a long long way even if it's not all the way.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply