Sql query to list all the user defined tables of a database in sql server

Sql query to list or gets all the user defined tables of a database in sql server

SELECT name FROM sys.tables

SELECT name FROM sys.objects WHERE [Type] = 'U'

Here sys.tables and sys.objects are system view. View sys.tables keeps only user defined tables while sys.objects keeps all the objects like table, view, synonyms, stored procedures functions etc.

Sql query to count the total numbers of user defined table:

SELECT COUNT(OBJECT_ID) AS TotalTables FROM sys.tables

In view sys.Objects meaning of Type:

Type	Meaning
U	User defined table
V	View
P	Stored procedure
FN	Scalar function
TF	Table valued function
SN	Synonym
D	Default constraint
PK	Primary key constraint
F	Foreign key constraint
C	Check constraint
UQ	Unique constraint
TR	Trigger
X	Extended stored procedure
RF	Replication filter procedure
TT	Table type
S	System table
IT	Internal table
IF	Inline table valued function
PG	Plan guid
R	Rule
SQ	Service queue
PC	CLR stored procedure
FS	CLR scalar function
FT	CLR table valued function
AF	CLR Aggregate function
TA	CLR DML trigger

Sql query to list all the stored procedures in database.

SELECT name FROM sys.objects WHERE [Type] = 'P'

Sql query to list all the scalar functions in database.

SELECT name FROM sys.objects WHERE [Type] = 'FN'

Sql query to list all the views in database.

SELECT name FROM sys.objects WHERE [Type] = 'V'

Sql query to list all the triggers in database.

SELECT name FROM sys.objects WHERE [Type] = 'TR'