how to get tables in db?

How do I get the list of user tables in a database?

help plz..

There are three ways to get the list of user tables in a database. The first method is by querying the [dbo].[sysobjects] system table. The [dbo].[sysobjects] contains one row for each object, such as constraint, table, view, stored procedure, function and so on, created within a database. To determine the type of object, you will query the [xtype] column which contains the object type. For user tables the [xtype] value is 'U' which stands for user tables.

SELECT [Name] FROM [dbo].[sysobjects]

WHERE [xtype] = 'U'

The second method is by querying the [Information_Schema].[Tables] system view. The [Information_Schema].[Tables] system view contains one row for each table in the current database for which the current user has permissions. This viw is based on the [dbo].[sysobjects] system table. The [Information_Schema].[Tables] system view will also include views in the list. To filter out just the user tables, you will only output those records where the [Table_Type] is 'BASE TABLE', as can be seen from the following query:

SELECT * FROM [Information_Schema].[Tables]

WHERE [Table_Type] = 'BASE TABLE'

The third method of listing the user tables in a database is by using the sp_tables system stored procedure. The sp_tables system stored procedure returns a list of objects that can appear in a FROM clause. Since you are only concerned with user tables and not system tables or views, you must set the @.table_type parameter to "'TABLE'", as can be seen from the following query:

EXEC sp_tables @.table_type = "'TABLE'|||

in sql 2005 you have a catalog view called sys.tables

select *From sys.tables

From BOL : Returns a row for each table object, currently only with sys.objects.type = U.

Madhu

|||thx for a reply... sys.tables is a gud way|||

That's all good, but suppose I want to see all databases on a server that contain a certain table. I do that to find a candidate database , on servers where there are user databases that I don't want to SELECT from.

What system table shows me all the databases that have a specific table name?

The code below will work, but is there a better way?

CREATE TABLE ##MyTable(MyDB varchar(80));

EXEC sp_msforeachdb 'INSERT ##MyTable SELECT table_catalog

FROM ?.information_schema.tables

WHERE table_name like ''account%''';

SELECT * from ##MyTable;