SQL Select list of tables in a database

Question

I am using SQL Server 2005. I am trying to SELECT a list of tables in one of my database. Here is my structure of my SQL Server:

- <IP>(SQL Server 9.0 -userlogin)
   - Databases
      - Company
          - Tables
             - dbo.UserDB
             - dbo.detailsDB
             - dbo.goodsDB

I would like to retrieve the values of dbo.UserDB, dbo.detailsDB, dbo.goodsDB

But i do not know what is the exact sql query needed.

I have tried many ways like

SELECT * FROM userlogin.Tables; and

SELECT * FROM userlogin.Company.Tables;, but none of them works.

I have seen quite a few posts which suggests using show databases and show tables, but they don't seem to work as well.

Is it possible to select a list of table names in a database in the first place?

Thank you for any help in advance.

---

Thanks for the MSDNA link that @TomTom provided, I can now list my tables in my database.

However, I would want to list specific tables where TABLE_NAME contains "user".

How can I do it? I am trying on the following sql but it is not displaying result at the moment:

SELECT DISTINCT TABLE_NAME
FROM Company.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE '%"user"%';
GO

Answer 1

Try the iNFORMATION_SCHEMA.

http://msdn.microsoft.com/en-us/library/ms186778.aspx

They contain all the schema information you need.

Answer 2

Use the new sys system catalog in SQL Server 2005 and up:

SELECT Name
FROM sys.tables
WHERE is_ms_shipped = 0   -- only list *your* tables - not the system / MS table

You can read more about system catalog views and how to query them in the MSDN Books Online - and be warned - there's lots more to read and learn!