Sunday, April 5, 2009

How to Get All table names of a DataBase

While working in sql server and writing queries in the Query analyzer I often need to go to the Object browser window and go through the database and then find out the name of the table to which i need to write query. Then i start my search on how to find the table name by using any command in the query analyzer. And here is the solution to my problem, now i can find whole list of the table names which are in the database. which is found from this source
USE NORTHWIND
SELECT * FROM information_schema.tables
ORDER BY table_TYPE
Contains one row for each table in the current database for which the current user has permissions. The INFORMATION_SCHEMA.TABLES view is based on the sysobjects system table. To retrieve information from these views, specify the fully qualified name of INFORMATION_SCHEMA view_name. And here is the second way you can use to get list of table names from database of northwind. And here is the output of the above command.


As you can see that it consist of the following columns.
Column nameData typeDescription
TABLE_CATALOGnvarchar(128)Table qualifier.
TABLE_SCHEMAnvarchar(128)Table owner.
TABLE_NAMEsysnameTable name.
TABLE_TYPEvarchar(10)Type of table. Can be VIEW or BASE TABLE.
Or you can use the sysobjects directly and get more information about the objects contained in the database. The sysobjects contains one row for each object (constraint, default, log, rule, stored procedure, and so on) created within a database. From the below select command i have set the type of the return value to 'U', which only return me the values of the tables contained in the database.
USE NORTHWIND
SELECT * FROM sysobjects
WHERE TYPE='U'
Here is the list of possible values for the type column
  • C = CHECK constraint
  • D = Default or DEFAULT constraint
  • F = FOREIGN KEY constraint
  • FN = Scalar function
  • IF = Inlined table-function
  • K = PRIMARY KEY or UNIQUE constraint
  • L = Log
  • P = Stored procedure
  • R = Rule
  • RF = Replication filter stored procedure
  • S = System table
  • TF = Table function
  • TR = Trigger
  • U = User table
  • V = View
  • X = Extended stored procedure
By removing the where clause from the above select statement you can get all the objects.Hope you will get some idea of how to get the information of the object in the database.
All and any comments / bugs / suggestions are welcomed!

4 comments:

aj said...

Does it work with all versions of SQL Server or is it version specific?
What about Oracle, MySQL and SqlLite, can we do the same in them?

Asim said...

aj: it will work in sql server 2005and sql server 2000 as i have check them both, but i haven't check this for Oracle, MySql and sqlLite as i don't have in my Enviroment.

TasleemArif said...

Instead of using queries there is another technique known as SMO (Sql server Management Objects), basically a DLL is shipped with .net (we can use by add reference) name space are like
using Microsoft.SqlServer.Management;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;


), that helps you to get/create tables data bases queries...
in short with this dll we can do all things that will normally do with SQL server, by using Managemt Studio.

Asim said...

Tasleem: thanks for you comments, but i have write queries regarding the query analyzer. or when you are working in store procedure.