How to list all the System Views?

Parminder Pal Singh

9y1.4k

Hi,

I hope you all are doing well. Today i came with new challenge. The matter included in this form is just for the knowledge purpose, so following is the thing what i am looking for.

As We have metadata of all objects inside the System Views of Views like sys.tables, sys.all_objects, sys.columns etc. I don't want to go with any user interface, just want to list all the things with a query. How can i list all the System Views?

or
I need a query that can list all the System Views, e.g output should be like that:

query: SELECT * FROM [dbo].[abc]

Result:
-- Name --
1) sys.tables
2) sys.columns
3) sys.views
4) information_schema.schemata
......
N) upto so on....

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Anil Jha9y ago
Accepted answer
Use below sql that gives system views.
SELECT * FROM SYS.all_views WHERE type = 'V'
saida chary9y ago
Hi
If you want to get views SELECT object_id FROM SYS.all_views WHERE type = 'V'

If you want to get Columns of particular view/tables

select * from SYS.Columns where object_id in(SELECT object_id FROM SYS.objects WHERE name = )
Parminder Pal Singh9y ago
Thanks Anil and Manish.
Midhun Tp9y ago
Hi,

You can get list of all views using below query -
SELECT * FROM sys.objects WHERE type='V'
If you need all columns of a query, just run below query -
SELECT * FROM sys.columns WHERE object_id=@object_id
In the above query, we need to pass @object_id in where condition which can be obtained from first query.
Manish Kumar9y ago
I guess sys.views returns only user defined view.

Try below script to find all DMV list

SELECT name, type, type_desc ,*
FROM sys.system_objects where
type_desc='VIEW'
Gokhul Varman9y ago
Its just a simple query you can get full explantion about it in the given link:https://blog.sqlauthority.com/2010/10/29/sql-server-list-of-all-the-views-from-database/
Naveen Bisht9y ago
```
SELECT * FROM sys.views
```