List all the table in given database

Think that you have a database more than 1-2 hundred database tables and you want to get list of all the tables in particular database. I was working on a project with more than 356 table and required to get the list of all the table with column names, column types and length of data type so first I searched for list of table finding query and it give me different different result.

Then I thought let me write a quick post about all the available query which can list all the tables from specific database.

For a demo purpose I have created only 2 tables in one database and write a query for quick result and better image

Let's check one by one.


SELECT * FROM information_schema.tables

Above query will return the data from information_schema table. It has only tables related info.

look at the result with below screen shot

SELECT * FROM sysobjects WHERE xtype='U'

Above query will retrieve result from system object table which have all the information related to database like Primary Key, Stored Procedure, User table etc....

look at the result with below screen shot

SELECT * FROM Sys.Tables

Above query get the result along with object_id which will be very useful for join if you want to get column names etc. This object id will be tied with other table.

look at the result with below screen shot

exec sp_msforeachtable 'print ''?'''

Above query is little bit different than all other options. It will simply prints table name on the screen rather than returning a result in table.

look at the result with below screen shot