CODEWITHSUNDEEP
sql-serverjdbcdatabase-metadata
abubakkar
abubakkar

Reputation: 187

List user defined views from database

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?

Upvotes: 1

Views: 2822

Answers (2)

Fredy Fischer
Fredy Fischer

Reputation: 458

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.

Upvotes: 0

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67331

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

Upvotes: 3

Related Questions