A nifty little trick is to be able to list all the Tables or Views, or even both, present in a database. A quick way to get All the Tables and Views follows:
SELECT * FROM information_schema.tables
WHERE Table_schema = 'dbo'
ORDER BY Table_name
In the above query, I retrieved all table-like objects based on the dbo schema. This produces a list of all views and tables.
This:
SELECT TOP 1000 [TABLE_CATALOG]
,[TABLE_SCHEMA]
,[TABLE_NAME]
,[VIEW_DEFINITION]
,[CHECK_OPTION]
,[IS_UPDATABLE]
FROM [lms_db].[INFORMATION_SCHEMA].[VIEWS]
Gets all Views only, whereas this:
SELECT * FROM information_schema.tables
WHERE Table_schema = 'dbo' and Table_type = 'BASE TABLE'
ORDER BY Table_name
Is a bit more narrowed down to get the tables only.
