How to get the table names of a SQL Server 2000 database

[troysf]

This might sounds a dumb question to some people. I need to
know the table names of a SQL Server 2000 database inside a stored procedure.


Which system table (or SP) should I use? Thanks.

Troy
San Francisco

 

[lachie]

You can open the sysobjects table and query all objects with xtype U..

or you can (and should use the stored procedure sp_tables)

(if you want all the user defined tables... use
sp_tables @table_type = "'TABLE'"

 

[brownbear]

There are system stored procedures which give you info but it is easier to use get info directly from the system tables for some things. It is worthwhile getting one of the relationship diagrams of the system tables in SQL Server and learning a bit about them.

One of the ones I reference quite frequently is sysobjects which gives you names. sysobjects is in every database.

Run the following query to get the object types that are stored so you can see how to get a similar list for Views etc.

SELECT DISTINCT type FROM sysobjects

To get a list of tables with the names sorted:

SELECT name FROM sysobjects
WHERE type = 'U'
ORDER BY name

 

[troysf]

Thanks much for your time. All of them worked.

Regards,
Troy

 

[lachie]

Unless you have absolutely no other option, you are generally better off using stored procedures.

If Microsoft decide (as they are quite likely) to change the internal workings of SQL Server, there's a good chance that querying the system tables will not produce the same results.

Using a system stored procedure however, will most likely be updated to reflect any changes in the underlying workings.

It just makes it that little bit easier when porting your database (and database-based apps) to a new version of SQL.

Just my two cents...

L