SQL 选择数据库中的表列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8293335/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Select list of tables in a database
提问by gymcode
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:
我正在使用 SQL Server 2005。我试图在我的一个数据库中选择一个表列表。这是我的 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
我想检索的值dbo.UserDB，dbo.detailsDB，dbo.goodsDB
But i do not know what is the exact sql query needed.
但我不知道所需的确切 sql 查询是什么。
I have tried many ways like
我尝试过很多方法，比如
SELECT * FROM userlogin.Tables;and
SELECT * FROM userlogin.Tables;和
SELECT * FROM userlogin.Company.Tables;, but none of them works.
SELECT * FROM userlogin.Company.Tables;，但它们都不起作用。
I have seen quite a few posts which suggests using show databasesand show tables, but they don't seem to work as well.
我已经看到很多帖子建议使用show databasesand show tables，但它们似乎不起作用。
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.
感谢@TomTom 提供的 MSDNA 链接，我现在可以在我的数据库中列出我的表。
However, I would want to list specific tables where TABLE_NAME contains "user".
但是，我想列出 TABLE_NAME 包含“用户”的特定表。
How can I do it? I am trying on the following sql but it is not displaying result at the moment:
我该怎么做？我正在尝试以下 sql，但目前没有显示结果：
SELECT DISTINCT TABLE_NAME
FROM Company.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE '%"user"%';
GO
回答by TomTom
Try the iNFORMATION_SCHEMA.
试试 iNFORMATION_SCHEMA。
http://msdn.microsoft.com/en-us/library/ms186778.aspx
http://msdn.microsoft.com/en-us/library/ms186778.aspx
They contain all the schema information you need.
它们包含您需要的所有架构信息。
回答by marc_s
Use the new syssystem catalog in SQL Server 2005 and up:
sys在 SQL Server 2005 及更高版本中使用新的系统目录：
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 lotsmore to read and learn!
你可以阅读更多有关系统目录视图以及如何查询这些MSDN联机丛书-和被警告-有很多更多的阅读和学习！
回答by VS1
You can use INFORMATION_SCHEMA as told by @TomTom:
您可以按照@TomTom 的说明使用 INFORMATION_SCHEMA：
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:
对于查找名称包含“user”的表的其他问题，请执行以下语句：
USE <<YOUR_DB_NAME>>
Exec sp_tables '%user%'
回答by Ric
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!
抱歉，已经看到@marc_s 在我之前提供了答案！
回答by Wim
Try:
尝试：
SELECT *
from sysobjects
where type = 'U' AND
NAME LIKE '%user%'
GO
