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
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 name||Data type||Description|
|TABLE_TYPE||varchar(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.
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
All and any comments / bugs / suggestions are welcomed!