How to know what are the tables\views present in SQL Server database

You can always view the tables and views using SQL Server Management Studio/ Enterprise Manager. If we want to list all the tables’ information we can use the following system level stored procedure.

• To list all the tables/views information in a database. Execute the following command.
  EXEC SP_TABLES
• To list only table names for dbo user. Execute the following command.
  EXEC SP_TABLES @Table_Type = ‘TABLE’, @Table_Owner = ‘dbo’
• You can use the other parameters like @table_name = '%' for further filter the table names

We can also use the following select statement to list all the tables information:

• From SysObjects table
  SELECT Name AS TableName FROM sysObjects WHERE xtype='u' ORDER BY TableName
• From Information_Schema.Tables
SELECT TABLE_NAME FROM Information_Schema.Tables WHERE TABLE_TYPE='BASE TABLE' ORDER BY Table_Name