February 27, 2012 at 8:28 am
I'm trying to write SQL query to bring all columns for a view, below query does not retrieve any result. Any help is appreciated.
If I expand on View name in SSMS , I can see all the columns with data types
select a.name View_name,b.name column_name
from sys.all_objects a,sys.all_columns b
where a.object_id=b.object_id
and a.type='V'
and a.name like '%_vv%'
February 27, 2012 at 8:41 am
Most chances are that the line and a.name like '%_vv%' is the one that filters your results. What is the name of the view that you are trying to see it’s columns’ names?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 27, 2012 at 8:45 am
Thank you Adi. View name is student_master_vv2
February 27, 2012 at 8:47 am
Try to base you query on the following:
select *
from INFORMATION_SCHEMA.VIEWS v
join INFORMATION_SCHEMA.COLUMNS c on c.TABLE_SCHEMA = v.TABLE_SCHEMA
and c.TABLE_NAME = v.TABLE_NAME
February 27, 2012 at 9:43 am
Thank you all!
February 27, 2012 at 10:56 am
svakka (2/27/2012)
Thank you all!
Do you know what went wrong and why you didn't get the expected results? In my opinion your query should have shown the columns of the view with the name that you posted. I've even checked it by creating a view with the same name as you posted, then using the query that you posted and got the columns' names. The script bellow shows it, so it is very interesting to me, what went wrong in your environment.
create view student_master_vv2
as
select object_id, name, type
from sys.objects
go
select a.name View_name,b.name column_name
from sys.all_objects a,sys.all_columns b
where a.object_id=b.object_id
and a.type='V'
and a.name like '%_vv%'
go
drop view student_master_vv2
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 27, 2012 at 11:40 am
I'm not sure why none of the view.columns are making it to sys.all_columns, but the views are created under sys.all_objects
February 27, 2012 at 11:50 am
Users that don't have permissions to work with the view, won't be able to see the columns from sys.all_collumns, but if you can see it in sys.all_objects, you do have permissions to work with the view. I have to admit that I don't see any reason why you won't be able to see the columns.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 27, 2012 at 11:59 am
Which permission can I check for the all_columns
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply