I was documenting the list of tables/views in a data mart & staging databases & I found the following scripts useful:
TSQL To get list of all tables:
[sourcecode type=”sql” wraplines=”false”]
SELECT * FROM <DatabaseName>.information_schema.tables
where TABLE_TYPE=’BASE TABLE’
[/sourcecode]
TSQL To get list of all views:
[sourcecode type=”sql” wraplines=”false”]
SELECT * FROM <DatabaseName>.information_schema.tables
where TABLE_TYPE=’VIEW’
[/sourcecode]
Alternatives (for SQL 2005 onwards):
[sourcecode type=”sql” wraplines=”false”]
SELECT * FROM SYS.TABLES
[/sourcecode]