How to get list of tables in SQL Server

sp_tables returns a list of tables and views that can be queried in a selected database. The basic usage is as follows:

sp_tables [ [ @table_name = ] 'name' ]
     [ , [ @table_owner = ] 'owner' ]
     [ , [ @table_qualifier = ] 'qualifier' ]
     [ , [ @table_type = ] "type" ]
     [ , [@fUsePattern = ] 'fUsePattern'];

On its own, calling "exec sp_tables" will simply list all the tables and views for the current database, as shown in the following example resultset:

TABLE_QUALIFIER	TABLE_OWNER	TABLE_NAME	TABLE_TYPE	REMARKS
MyDBName	dbo	ExampleTable1	TABLE
MyDBName	dbo	ExampleTable1	TABLE

You can use the @table_name @table_owner and @table_qualifier parameters with wildcards to return a smaller resultset. @table_name queries the table names, @table_owner the owners and @table_qualifier the database name. For example, to only return tables which start with the letter "a" you could execute either of these:

EXEC sp_tables "a%";
EXEC sp_tables @table_name = "a%";

The @table_type parameter lets you specify whether tables, system tables and/or views should be returned. If not specified then it will return all of them. To use this parameter, the values are a single quote and comma separated list inside double quotes, as shown in the following example:

EXEC sp_tables @table_type = "'view'";

EXEC sp_tables @table_type = "'table', 'view'";The @fUsePattern allows you to specify whether _ % and [ ] characters are interpreted as wildcard characters or not. If set to 0 then pattern matching is switched off and those characters must be present in the table name etc to be returned. If set to 1 (or not specified - the default is on) then wildcard pattern matching is switched off.

Source:http://www.tutorialsdesk.com/2012/09/get-list-of-tables-in-sql-server.html