Get the list all tables in a sql server database

Suppose I want to see the list of table and vises exist on our database. Object explorer with in sql server management studio can be used to get the list of tables in a specific database.

  

In Object explorer we can see the tables and views but if we want to use it in our program or our sql script we can’t use it. For getting this information we have to write a query to achieve the same, there are 3 system views that we can use.

1. SYSOBJECTS

2. SYS.TABLES

3. INFORMATION_SCHEMA.TABLES

Gets the list of tables only using SYSOBJECTS

use AdventureWorksDW2008R2 Select id,name,crdate,xtype from SYSOBJECTS where XTYPE='U'

  

Below is the list of object type

sys.objects.type

AF = Aggregate function (CLR)

C = CHECK constraint

D = DEFAULT (constraint or stand-alone)

F = FOREIGN KEY constraint

FN = SQL scalar function

FS = Assembly (CLR) scalar-function

FT = Assembly (CLR) table-valued function

IF = SQL inline table-valued function

IT = Internal table

P = SQL Stored Procedure

PC = Assembly (CLR) stored-procedure

PG = Plan guide

PK = PRIMARY KEY constraint

R = Rule (old-style, stand-alone)

RF = Replication-filter-procedure

S = System base table

SN = Synonym

SQ = Service queue

TA = Assembly (CLR) DML trigger

TF = SQL table-valued-function

TR = SQL DML trigger

TT = Table type

U = Table (user-defined)

UQ = UNIQUE constraint

V = View

X = Extended stored procedure

 Gets the list of tables only using SYS.TABLES

use AdventureWorksDW2008R2 Select object_id,name,type,type_desc,create_date, modify_date from SYS.TABLES

  

  

Gets the list of tables and views

INFORMATION_SCHEMA.TABLES has in information about the table and view.

use AdventureWorksDW2008R2 --List of tables and views select * from INFORMATION_SCHEMA.TABLES --List of tables select * from INFORMATION_SCHEMA.TABLES where TABLE_TYPE like '%BASE TABLE%' --List of views select * from INFORMATION_SCHEMA.TABLES where TABLE_TYPE like '%VIEW%'