SQL Select a 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 databases. 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 get the value dbo.UserDB, dbo.detailsDB,dbo.goodsDB

But I do not know what an exact SQL query is.

I tried many ways like

SELECT * FROM userlogin.Tables; and

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

I have seen quite a few posts that suggest using show databasesand show tables, but they don't seem to work either.

Can I first select a list of table names in the database?

Thanks for any help in advance.

---

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

However, I would like to specify specific tables in which TABLE_NAME contains "user".

? sql, :

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

Answer 1

iNFORMATION_SCHEMA.

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

.

Answer 2

sys SQL Server 2005 :

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

, - MSDN - - !

Answer 3

INFORMATION_SCHEMA, @TomTom:

USE <<YOUR_DB_NAME>>

SELECT TABLE_SCHEMA + '.' + TABLE_NAME, *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_SCHEMA + '.' + TABLE_NAME

.

, "", :

USE <<YOUR_DB_NAME>>
Exec sp_tables '%user%'

Answer 4

You can use this

Use ***database name***

SELECT *
FROM   sys.tables
WHERE name like '%user%'

Sorry, you saw @marc_s provide an answer in front of me!

Answer 5

Try:

SELECT *
from sysobjects 
where type = 'U' AND
NAME LIKE '%user%'
GO