sql : get all the table in a database

select *  from information_schema.tables
where TABLE_CATALOG = 'databaseName' and  table_schema = 'schemaname' and  TABLE_TYPE = 'BASE TABLE' and table_name LIKE 'App_CandidateLoginDetail'

or SELECT [name] AS 'TableName' FROM SYSOBJECTS WHERE xtype = 'U' order by [name];

example

select *  from information_schema.tables
where TABLE_CATALOG = 'dbSample' and  table_schema = 'dbo' and  TABLE_TYPE = 'BASE TABLE' and table_name LIKE 'App_CandidateLoginDetail'