January 29, 2003 at 2:21 pm
Using SQLSRVR2k (sp3)
Here is my scenario..
I have a table called T120 - the table has an associated view called TR_Rating - Column names on Table and view are not the same.
What I would like to do is pass the table name (T120) to a sql query and have it give me back the any asscoiated view names and the column names in the views. Any help will be greatly appreciated. Thank You.
February 3, 2003 at 10:54 am
Give this a whirl:
select * from information_schema.view_column_usage where view_name='TR_Rating'
OR
select * from information_schema.view_column_usage where table_name = 'T120'
-Dan
-Dan
February 6, 2003 at 12:46 pm
Perhaps you are looking for something like this:
SELECT sv.name 'View', svc.name 'Column'
FROM sysobjects so (NOLOCK)
INNER JOIN sysdepends sd (NOLOCK) ON sd.depid = so.id
INNER JOIN syscolumns svc (NOLOCK) ON svc.id = so.id and svc.colid = sd.depnumber
INNER JOIN sysobjects sv (NOLOCK) on sv.id = sd.id
WHERE so.name = <original table name>
ORDER BY sv.name, svc.name
Guarddata-
February 6, 2003 at 4:51 pm
That is nearly perfect. I say nearly because I want the column names of the view and not the column names of the table. They are different on my tables. Thanks a million.
quote:
Perhaps you are looking for something like this:SELECT sv.name 'View', svc.name 'Column'
FROM sysobjects so (NOLOCK)
INNER JOIN sysdepends sd (NOLOCK) ON sd.depid = so.id
INNER JOIN syscolumns svc (NOLOCK) ON svc.id = so.id and svc.colid = sd.depnumber
INNER JOIN sysobjects sv (NOLOCK) on sv.id = sd.id
WHERE so.name = <original table name>
ORDER BY sv.name, svc.name
Guarddata-
February 9, 2003 at 7:08 am
I do not see any difference in the first one you wrote vs. the second one. I am still getting the same result. I get the View name of the table and the columns listed are the base columns from the table and not the column names of the view. The problem is that my column names on the view are different from my column names on the table and I am really interested in the column names of the viw itself.
-Ibrahim Akar
February 10, 2003 at 10:20 am
This will give you the column names from the view. I am not quite happy with it yet - I cannot get the view column name and the table name in the same query. That will have to come next.
Guarddata-
select sv.name 'View Name', svc.name 'View Column'
from sysobjects sv (nolock)
inner join syscolumns svc (nolock) on svc.id = sv.id
where sv.type = 'V' and sv.id in ( select distinct sdt.id
from sysobjects st (nolock)
inner join sysdepends sdt (nolock) on sdt.depid = st.id
where st.name = <orignal table name> )
order by sv.name, svc.name
February 10, 2003 at 10:26 am
That is exactly what I wanted. It is perfect.
-Ibrahim Akar
February 10, 2003 at 12:20 pm
Is it possible to get all three - i.e. a combination of the two queries..
WHat i would like to get now is the View Name, the View Columns and the fields that the View columns depend on. Thanks a million.
February 12, 2003 at 3:24 pm
Wish I could help on that score, iakar. I played with this for quite a while before deciding that it would take a better understanding of the internals than I have. I cannot see any connection between the name of a view column and the name of the referenced table.
Anyone else?
Guarddata-
February 12, 2003 at 3:42 pm
I have not really followed this thread, but you may want to look at what sp_depends SP does. It might give you a clue to what you are looking for. Here is an example of calling sp_depends:
use Northwind
sp_depends invoices
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply