Returning field names and field types from a view...

[mmorancbt]

I am not sure whether this should be in the Microsoft Access forum or here but I will try here first.

I want to return a list of views and then when selected, the field names and field data types from the selected views.

How is this best done?

Thanks.

Matthew Moran
Read my career blog at:

http://www.cbtoolkit.com

Career Blog:

http://blogs.ittoolbox.com/pm/career/

Todo esta bien.. Todo esta divertido (it's all good, it's all fun)

 

[SQLDenis]

Here is an example

use pubs

select t.TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,* from information_schema.tables t
join information_schema.columns c on t.TABLE_NAME= c.TABLE_NAME
where t.TABLE_TYPE='VIEW'

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

So if you want only the views the query would be

select t.TABLE_NAME from information_schema.tables t
where t.TABLE_TYPE='VIEW'

and then you would run the second query like this

select t.TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,* from information_schema.tables t
join information_schema.columns c on t.TABLE_NAME= c.TABLE_NAME
where t.TABLE_TYPE='VIEW'
and t.TABLE_NAME = @SomeViewName

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[mmorancbt]

SQLDenis,

Thanks. That was quick and simple. I added your blogs to my list. Your sql blog has some good information for me.

Thanks.

Matthew Moran (career blog and podcast below)
Career Advice with Attitude for the IT Pro

http://blogs.ittoolbox.com/pm/career

http://techcareerconsult.blogspot.com