Select Columns of a View

Question

I'm attempting to select the column names of a view in a similar way as selecting from information_schema.columns.

I can't seem to find a way to do this. Has anyone else done this before or know if it is even possible?

score 73 · Accepted Answer · answered Jun 29 '12 at 20:19

73

information_schema.columns.Table_name (at least under Sql Server 2000) includes views, so just use

SELECT * FROM information_schema.columns WHERE table_name = 'VIEW_NAME'

answered Jun 29 '12 at 20:19

Ghost

2,146
19
21

7

returns nothing in SSMS 2012 – Imad Jul 30 '15 at 10:32
8

Works for me :-) But I'm smart enough to replace `'VIEW_NAME'` with the actual name of my view ;-) – Simon_Weaver Oct 30 '18 at 03:42
`join INFORMATION_SCHEMA.TABLES (...) where TABLE_TYPE = 'VIEW'` – Peet Brits May 20 '22 at 07:47

score 20 · Answer 2 · answered Jun 29 '12 at 20:18

Try this:

SELECT *
FROM sys.views

This gives you the views as such - if you need the columns, use this:

SELECT * 
FROM sys.columns
WHERE object_id = OBJECT_ID('dbo.YourViewNameHere')

Not sure how you can do it using the INFORMATION_SCHEMA - I never use that, since it feels rather "clunky" and unintuitive, compared to the sys schema catalog views.

See the MSDN docs on the Catalog Views for all the details of all the views available, and what information they might contain.

This gives me the name of all the views, but not the names of the columns in the view. — steventnorris, Jun 29 '12 at 20:19

score 4 · Answer 3 · answered Apr 27 '19 at 11:25

4

I found this way working for views (SQL 2017). I was not able to get data from information_schema.columns and sys.columns:

    SELECT * FROM sys.all_columns WHERE object_id = OBJECT_ID('<view_name>')

answered Apr 27 '19 at 11:25

Karl

41
2

prabhat · Answer 4 · 2015-09-25T04:34:55.740

3

INFORMATION_SCHEMA views holds metadata about objects within database. INFORMATION_SCHEMA.COLUMNS uses underlying sys tables to retrive the metadata ( check sp_helptext 'master.Information_schema.columns' ). You can use this simpler query to select column names used in any view.

SELECT col.name 
FROM <db_name>.sys.columns col, <db_name>.sys.views vew
WHERE col.object_id = vew.object_id
AND vew.name = '<view_name>'

edited Sep 25 '15 at 04:34

answered Sep 24 '15 at 05:28

prabhat

41
1
5

welcome to stack, whenever you answer a problem always try to explain how the solution will help the asker. please take some time and edit your answer – Kumar Saurabh Sep 24 '15 at 05:34
2

Instead of multiple tables on `FROM` clause, you can use `JOIN`. So the query will be `SELECT col.name FROM sys.columns col JOIN sys.views vew ON col.object_id = vew.object_id WHERE vew.name = ''` – Arulkumar Sep 24 '15 at 06:06

score 1 · Answer 5 · answered Oct 15 '14 at 17:54

1

SELECT distinct VIEW_NAME
  ,TABLE_SCHEMA
  ,TABLE_NAME
  ,COLUMN_NAME
FROM   INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
--WHERE  TABLE_SCHEMA = 'Person'
ORDER BY
   VIEW_NAME
  ,TABLE_SCHEMA
  ,TABLE_NAME
  ,COLUMN_NAME

answered Oct 15 '14 at 17:54

Howard Rothenburg

1,220
1
11
7

1

This is a very interesting query but it actually returns the table columns used to get the results in the query, not the view columns returned. Therefore it is incorrect for the answer but very useful none the less. – AnthonyVO Jan 15 '18 at 18:56

score 0 · Answer 6 · answered Apr 20 '21 at 19:13

0

select schema_name(v.schema_id) as schema_name,
   object_name(c.object_id) as view_name,
   c.column_id,
   c.name as column_name,
   type_name(user_type_id) as data_type,
   c.max_length,
   c.precision
from sys.columns c
join sys.views v 
 on v.object_id = c.object_id
order by schema_name,
     view_name,
     column_id;

answered Apr 20 '21 at 19:13

Murat

15
5

Please, edit your question to add an explanation. See [how to answer](https://stackoverflow.com/help/how-to-answer). – Syscall Apr 20 '21 at 19:28

score 0 · Answer 7 · edited Aug 13 '21 at 00:28

I needed to create something similar, so I expounded on what Ghost suggested. I needed to create a top 1000 view for every view in a database order by the first ordinal column descending.

SELECT CONCAT (
              'CREATE VIEW dbo.temp_',
              v.name,
              ' AS select top (1000) * from ',
              OBJECT_SCHEMA_NAME (v.object_id),
              '.',
              v.name,
              ' ORDER BY [',
              c.COLUMN_NAME,
              '] DESC;'
          ) AS viewName
FROM sys.views AS v
    LEFT OUTER JOIN INFORMATION_SCHEMA.COLUMNS AS c
             ON v.name = c.TABLE_NAME
WHERE c.ORDINAL_POSITION = 1;

Select Columns of a View

7 Answers7

Linked