Little SQL Server Tricks: Get All Views or Tables in Your Database

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':

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':

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.