SQL SERVER – List All Tables of Database

USE YourDBName
GO
SELECT *
FROM sys.Tables
GO

or

USE YourDBName
GO
SELECT  *
FROM sysobjects where xtype='u'
GO


This will return all the tables in the database which user have created.