SQL Server Meta Data

Recently came across a nice and easy way to retrieve List of Table and Views from SQL Server database.

The idea is to use SQL statement to query some system tables holding the schemas

using

SELECT RTRIM(tbl.table_name) AS TableName FROM INFORMATION_SCHEMA.TABLES tbl WHERE tbl.TABLE_TYPE = 'BASE TABLE'

Will get all Tables in a database.

Using "VIEW" instead of "BASE TABLE" will retrieve the List of Views.

And to go one step further, we can get details of columns in each table using

SELECT * FROM INFORMATION_SCHEMA.COLUMNS col WHERE col.TABLE_NAME = 'TableName'

The will show details of each column ( type, nullable or not, max capacity, ID or not, default value, etc.)