How To List Tables In A MSSQL Database With Criteria

Here are some simple T-SQL statements to show table information with set of criterias.

-- show tables sort by name ascending  
 use DatabaseName  
 go  
 select * from sys.tables order by sys.tables.name asc  
 go  
 -- show tables that starts with s  
 use DatabaseName  
 go  
 select * from sys.tables where upper(sys.tables.name) like 'S%';  
 go  
 -- show tables sort by date created descending  
 use DatabaseName  
 go  
 select * from sys.tables order by create_date desc;  
 go  
 -- show tables where type description is user  
 use DatabaseName  
 go  
 select * from sys.tables where type_desc like lower('%user%')  
 go  

Reference: BOL