CODEWITHSUNDEEP
sqlsql-server
gymcode
gymcode

Reputation: 4633

SQL Select list of tables in a database

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

Upvotes: 7

Views: 58464

Answers (5)

S2S2
S2S2

Reputation: 8512

You can use INFORMATION_SCHEMA as told by @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

You can customize above query as per your requirements.

For your other question to find tables where the name contains 'user', execute the following statement:

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

Upvotes: 4

Ric
Ric

Reputation: 13248

You could use this

Use ***database name***

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

Sorry, have seen that @marc_s has provided the answer before me!

Upvotes: 4

marc_s
marc_s

Reputation: 755361

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!

Upvotes: 6

Wim
Wim

Reputation: 1096

Try: 

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

Upvotes: 2

TomTom
TomTom

Reputation: 62157

Try the iNFORMATION_SCHEMA.

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

They contain all the schema information you need.

Upvotes: 8

Related Questions