Click here to check my latest exciting videos on youtube
Search Mallstuffs

Flag Counter
Spirituality, Knowledge and Entertainment


Locations of visitors to this page


Latest Articles


Move to top
Get All Tables in a Database
Posted By Sarin on Sep 16, 2012     RSS Feeds     Latest Hinduism news
2956 Views

rgreh Get List of All Tables in a Database
  
There are many ways to show all tables of a database. Now one may ask why the developers of SQL server came up with many ways to arrive at the same solution. The answer is that although there are many ways to retrieve all tables of the database, each one of them differ from one another way in the way they derive the tables, or in the number of extra information they show along with the table name etc.
Let us some of the ways of getting all tables form the database:
  1)   The oldest of all ways is
        
SELECT * FROM sysobjects WHERE xtype='U'
One should note that it is the slowest of all ways to get all tables from the database. This is because sysobjects consist of all database objects like SP, constraints, trigger etc. So, the above query filters among all such objects to get the list of all tables which increases the retrieval time.

  2)   Faster way of retrieving all tables would be
        
select * from sys.tables;
This would list out all the tables but again it will list out many extra columns one might not be interested in:

  
     I don’t think anyone would be interested in object id, principle id etc.

  3)   So, the much faster and better way would be
        
SELECT * FROM information_schema.tables
This query will list out all tables along with schema, database name and table type. Note that qbove query would list out other types of tables (Virtual tables i.e Views) as well. To list out only base tables, one should add a where condition as


  
SELECT * FROM information_schema.tables where Table_Type =  'BASE TABLE'

Last way to list out all the tables is by calling the stored procedure sp_tables and passing table as paramater. Exact code is
  
  4)   Using Stored procedure
        
EXEC sp_tables  @table_type = "'TABLE'"

It might be that you have two or more tables with the same name and the only differentiation between them is the database schema. For example, you might have the table sarin as dbo.sarin or dbs.sarin where dbo and dbs is different schema respectively.
In these cases, retrieving only the table name is not enough. One needs to get the name of the table prepended by schema name. Only can implement this in many ways. I would list out two ways which I have used in my professional life:
  
  5)   Table name prepended by schema name
        
  SELECT FullName    = s.name + '.' + t.name
  ,SchemaName = s.name
  ,TableName  = t.name
  FROM sys.tables t JOIN sys.schemas s ON s.schema_id = t.schema_id
  ORDER BY s.name, t.name

  
We have appended schema to table name to get the full name of the table. However we have make use of join operation which would increase the data retrieval time. A much faster alternative would be  
  
  6)   Faster way to retrieve table name prepended by schema name
        
SELECT TABLE_SCHEMA + '.' + TABLE_NAME, *
            FROM INFORMATION_SCHEMA.TABLES
            WHERE TABLE_TYPE = 'BASE TABLE'
            ORDER BY TABLE_SCHEMA + '.' + TABLE_NAME
  
As shown above, from the single table without using join, we retrieve the table name prepended by schema name
 
Note: Images used on this website are either a production of Bhaktivedanta Book Trust(https://www.krishna.com), Iskcon Foundation or were found in google search under "Free to use and share". If any of the images presented here violates copyright issues or infringes anyone copyright or are not under "Fair use", then please bring it to our notice. Read Disclaimer for more.

Share this to your friends. One of your friend is waiting for your share.
Related Articles
Calling web service asynchronously using jquery ajax
Get All Tables in a Database
Get comma separated result of a database query
Testing performance issues with reflection
Get list of all database objects with table name or column name
When to use optional parameters of creating database statement
Different types of alter table command to change database schema
Delete duplicate records from table in SQL

Post Comment