-- here, you need to prefix the database name in INFORMATION_SCHEMA.TABLES
SELECT TABLE_NAME FROM [MSSQL-TEST].INFORMATION_SCHEMA.TABLES;
-- The next 2 ways will require you to point
-- to the specific database you want to list the tables
USE [MSSQL-TEST];
-- (1) Using sys.tables
SELECT * FROM sys.tables;
-- (2) Using sysobjects
SELECT * FROM sysobjects
WHERE type='U';
Any of the T-SQL code below will work in SQL Server 2019:
-- here, you need to prefix the database name in INFORMATION_SCHEMA.TABLES
SELECT TABLE_NAME FROM [MSSQL-TEST].INFORMATION_SCHEMA.TABLES;
-- The next 2 ways will require you to point
-- to the specific database you want to list the tables
USE [MSSQL-TEST];
-- (1) Using sys.tables
SELECT * FROM sys.tables;
-- (2) Using sysobjects
SELECT * FROM sysobjects
WHERE type='U';
select *
from sys.objects
where type = 'U' -- User tables
and is_ms_shipped = 0 -- Exclude system tables
The downside of INFORMATION_SCHEMA.TABLES is that it also includes system tables such as dtproperties and the MSpeer_... tables, with no way to tell them apart from your own tables.
I would recommend using sys.objects (the new version of the deprecated sysobjects view), which does support excluding the system tables:
select *
from sys.objects
where type = 'U' -- User tables
and is_ms_shipped = 0 -- Exclude system tables
GO
select * from sys.objects where type_desc='USER_TABLE' order by name
GO
或者
-- For all tables
select * from INFORMATION_SCHEMA.TABLES
GO
--- For user defined tables
select * from INFORMATION_SCHEMA.TABLES where TABLE_TYPE='BASE TABLE'
GO
--- For Views
select * from INFORMATION_SCHEMA.TABLES where TABLE_TYPE='VIEW'
GO
Well you can use sys.objects to get all database objects.
GO
select * from sys.objects where type_desc='USER_TABLE' order by name
GO
OR
-- For all tables
select * from INFORMATION_SCHEMA.TABLES
GO
--- For user defined tables
select * from INFORMATION_SCHEMA.TABLES where TABLE_TYPE='BASE TABLE'
GO
--- For Views
select * from INFORMATION_SCHEMA.TABLES where TABLE_TYPE='VIEW'
GO
SELECT [TABLE_CATALOG] + '.' + [TABLE_SCHEMA] + '.' + [TABLE_NAME]
FROM MyDatabase.INFORMATION_SCHEMA.Tables
WHERE [TABLE_TYPE] = 'BASE TABLE' and [TABLE_NAME] <> 'sysdiagrams'
ORDER BY [TABLE_SCHEMA], [TABLE_NAME]
结果:
MyDatabase.dbo.MyTable1
MyDatabase.dbo.MyTable2
MyDatabase.MySchema.MyTable3
MyDatabase.MySchema.MyTable4
等。
In SSMS, to get all fully qualified table names in a specific database (E.g., "MyDatabase"):
SELECT [TABLE_CATALOG] + '.' + [TABLE_SCHEMA] + '.' + [TABLE_NAME]
FROM MyDatabase.INFORMATION_SCHEMA.Tables
WHERE [TABLE_TYPE] = 'BASE TABLE' and [TABLE_NAME] <> 'sysdiagrams'
ORDER BY [TABLE_SCHEMA], [TABLE_NAME]
SELECT s.NAME SchemaName, t.NAME TableName
FROM [dbname].SYS.tables t
INNER JOIN [dbname].SYS.SCHEMAS s
ON t.SCHEMA_ID = s.SCHEMA_ID
WHERE t.is_ms_shipped=0 and type_desc = 'USER_TABLE'
ORDER BY s.NAME, t.NAME
To remove tables added by replication and any other table Microsoft adds run this:
SELECT s.NAME SchemaName, t.NAME TableName
FROM [dbname].SYS.tables t
INNER JOIN [dbname].SYS.SCHEMAS s
ON t.SCHEMA_ID = s.SCHEMA_ID
WHERE t.is_ms_shipped=0 and type_desc = 'USER_TABLE'
ORDER BY s.NAME, t.NAME
发布评论
评论(20)
SQL Server 2000、2005、2008、2012、2014、2016、2017 或 2019:
仅显示特定数据库中的表
或者,
PS:对于 SQL Server 2000:
SQL Server 2000, 2005, 2008, 2012, 2014, 2016, 2017 or 2019:
To show only tables from a particular database
Or,
PS: For SQL Server 2000:
以下是您还可以搜索的其他对象类型的列表:
Here is a list of other object types you can search for as well:
或者
OR
或者
OR
SQL Server 2012
SQL Server 2012
从 sysobjects 中选择 *,其中 xtype='U'
select * from sysobjects where xtype='U'
(SQL Server 2000 标准;SQL Server 2005 中仍受支持。)
(SQL Server 2000 standard; still supported in SQL Server 2005.)
下面的任何 T-SQL 代码都可以在 SQL Server 2019 中运行:
Any of the T-SQL code below will work in SQL Server 2019:
INFORMATION_SCHEMA.TABLES
的缺点是它还包含系统表,例如dtproperties
和MSpeer_...
表,但无法区分除了你自己的桌子之外。我建议使用 < code>sys.objects (已弃用的新版本 sysobjects 视图),它支持排除系统表:
The downside of
INFORMATION_SCHEMA.TABLES
is that it also includes system tables such asdtproperties
and theMSpeer_...
tables, with no way to tell them apart from your own tables.I would recommend using
sys.objects
(the new version of the deprecated sysobjects view), which does support excluding the system tables:2022 年更新:
您可以列出/显示在Microsoft SQL SERVER中使用此简单查询创建的表。
UPDATE 2022:
You can list/show the tables that you created with this simple query in Microsoft SQL SERVER.
那么您可以使用sys.objects来获取所有数据库对象。
或者
Well you can use sys.objects to get all database objects.
OR
在 SSMS 中,要获取特定数据库中的所有完全限定表名(例如“MyDatabase”):
结果:
In SSMS, to get all fully qualified table names in a specific database (E.g., "MyDatabase"):
Results:
此链接可以提供更多有关此的信息
主题
This link can provide much more information on this
topic
请使用这个。 您将获得表名称和模式名称:
Please use this. You will get table names along with schema names:
感谢 Ray Vega,他的回复给出了数据库中的所有用户表......
sp_helptext 显示基础查询,总结为...
Thanks to Ray Vega, whose response gives all user tables in a database...
sp_helptext shows the underlying query, which summarises to...
使用 SELECT * FROM INFORMATION_SCHEMA.COLUMNS 还会显示所有表和相关列。
Using
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
also shows you all tables and related columns.要删除通过复制添加的表以及 Microsoft 添加的任何其他表,请运行以下命令:
To remove tables added by replication and any other table Microsoft adds run this: