While cleaning up a project, I needed a list of all the views in the database. SQL Server Management Studio provides a menu for this, but I needed a way to get this data in text form. In SQL Server, you can get this information from the INFORMATION_SCHEMA.TABLES view.
In this view, you get a list of tables and views. If you need only the views, you can filter by TABLE_TYPE = ‘VIEW’:
|
1 2 3 4 |
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'VIEW' ORDER BY TABLE_SCHEMA, TABLE_NAME |
If you only need the tables, you can filter by TABLE_TYPE = ‘BASE TABLE’:
|
1 2 3 4 |
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_SCHEMA, TABLE_NAME |
The queries do not look like much, but trust me, they are a great help.