Select ColumnsNames and ColumnsTypes from View

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Select ColumnsNames and ColumnsTypes from View

Author

Topic

ya3mro
Starting Member

37 Posts

Posted - 2006-07-17 : 10:11:49

I have an urgent Question ::

1- How Can I return Views Names by SQL Query?
2- I want a SQL Query that Select ColumnsNames and ColumnsTypes from View( in SQL Server)

for example if i created a View in SQL server that RETURN id,name,age,job,managername
and i want a query that show me the columns in this View and their Types
result be as :
Columns Type
-------- ------
ID int
Name nvarchar
age tinyint
Job nvarchar
managername nvarchar

plz help me

How I Came To Islam?
http://english.islamway.com

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-17 : 10:24:28

You can use information_schema.columns...

--preparation (for example purposes)
create view v1 as
select
    cast(1 as int) as id,
    cast('Fred' as nvarchar) as name,
    cast(3 as tinyint) as age,
    cast('xyz' as nvarchar) as job,
    cast('Dave' as nvarchar) as managername 
go

--calculation
select COLUMN_NAME as 'Columns', DATA_TYPE as 'Type'
from information_schema.columns
where TABLE_NAME = 'v1'
order by ORDINAL_POSITION

--tidy up
drop view v1

/*results
Columns        Type      
-------------- ----------
id             int
name           nvarchar
age            tinyint
job            nvarchar
managername    nvarchar
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

ya3mro
Starting Member

37 Posts

Posted - 2006-07-17 : 10:34:18

thanx MR RyanRandall very much for ur interest

How I Came To Islam?
http://english.islamway.com

humanpuck
Yak Posting Veteran

94 Posts

Posted - 2006-07-17 : 10:40:31

This will work, it takes the data directly from the sys tables

select a.name,b.name,c.name,b.length
from sysobjects a
inner join syscolumns b on a.id = b.id
inner join systypes c on b.xtype = c.xtype
where a.name like 'VIEW NAME'
order by b.colorder

column 1 is view/tbl/proc name, col 2 is field name, column c is field type and length is pretty straight forward.

humanpuck
Yak Posting Veteran

94 Posts

Posted - 2006-07-17 : 10:43:59

also this query against the sysobjects tbl will list out all views

select name from sysobjects where type = 'v'

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-18 : 03:51:11

yeah...but it is not recommended way to directly query the system tables, Information_Schema views is the better option !

Harsh Athalye
India.
"Nothing is Impossible"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources