Select all user defined tables from database – SQL SERVER

There are different ways to select all User defined tables from databases

Option 1: Using Object Catalog view

   1: SELECT sobjects.name 

   2: FROM sysobjects sobjects

   3: WHERE sobjects.xtype = 'U'

   4: order by sobjects.name

Alternatively here is a list of other object types you can search for as well:

C: Check constraint
D: Default constraint
F: Foreign Key constraint
L: Log
P: Stored procedure
PK: Primary Key constraint
RF: Replication Filter stored procedure
S: System table
TR: Trigger
U: User table
UQ: Unique constraint
V: View
X: Extended stored procedure

Option 2: Using Information schema view

   1: SELECT * FROM INFORMATION_SCHEMA.TABLES 

   2: WHERE TABLE_TYPE = 'BASE TABLE'

Here is something you can do as well

   1: select * from sys.tables

Suppose you might need to get all tables from your SQL SERVER Instance. Here is what you can do

   1: --for all databases

   2: sp_msforeachdb 'select "?" AS db, * from [?].sys.tables'