List user defined views from database

Question

Is there a way to list user defined views alone. In MSSQL, when I tried to list tables and views using getTables() function of DatabaseMetadata of jdbc, it is showing all the views. But I don't need system views into my result set.

DatabaseMetadata dmd = connection.getMetaData();
tablesResultSet= dmd.getTables(dbName, null, null, new String[] { "TABLE", "VIEW" });

This is the code, I'm using to extract metadata. Can anyone help me to solve this?

score 3 · Accepted Answer · edited Feb 27 '17 at 11:32

3

You might ask the database directly with a SELECT call and analyse the result:

SELECT * FROM sys.objects WHERE [type]='V' AND is_ms_shipped=0

[type]='V' will filter for VIEWs and is_ms_shipped=0 will filter for objects which are created by users (were not installed from MS)

Find details here

edited Feb 27 '17 at 11:32

Arockia Nirmal

737
1
6
20

answered Feb 27 '17 at 11:03

Shnugo

66,100
9
53
114

2

@abubakkar - `SELECT TABLE_NAME AS VIEW_NAME FROM [INFORMATION_SCHEMA].[VIEWS]` might be somewhat more portable. – Gord Thompson Feb 27 '17 at 11:47

score 0 · Answer 2 · answered Feb 27 '17 at 12:28

0

You have to take the schema into consideration. The default schema on MS SQL is dbo. So your call to the metadata should be:

DatabaseMetadata dmd = connection.getMetaData();
tablesResultSet= dmd.getTables(dbName, "dbo", null, new String[]   { "VIEW" });

Or you get all Schemas before by

dmd.getSchemas(dbName,"%");

And then loop all your 'working' schemas.

answered Feb 27 '17 at 12:28

Fredy Fischer

458
3
12

yes, except, that the default schema might have a different name. – Fredy Fischer Feb 27 '17 at 13:19
In MySQL schema is not common, because its contained in the Database. So you use just the '%' as the schema name in MySQL. – Fredy Fischer Feb 27 '17 at 13:30

List user defined views from database

2 Answers2