当前位置：文江博客话题详情

检查表是否存在于数据库中但不存在于模型中

发布于 2024-10-09 03:10:55 字数 90 浏览 4 评论 0原文

我在我的 Asp.net MVC 项目中使用实体数据模型框架。我需要检查该表是否存在于数据库中但不存在于模型中。任何人都可以帮助我如何在控制器级别进行检查以进行测试。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

那伤。 2024-10-16 03:10:55

你没有提到你正在使用什么数据库，但我假设它是 SQL Server。

以下是获取表列表的方法：

USE YourDBName
GO 
SELECT *
FROM sys.Tables

您可以使用 Ado.Net 运行查询，也可以将 Tables 表添加到实体数据模型中。

更新了

这里还有一些其他方法。您可能需要根据您的需要调整它们。

[dbo].[sysobjects] 为数据库中创建的每个对象（例如约束、表、视图、存储过程、函数等）包含一行。要确定对象的类型，您将查询包含对象类型的 [xtype] 列。对于用户表，[xtype] 值为“U”，代表用户表。

SELECT [Name] FROM [dbo].[sysobjects]
WHERE [xtype] = 'U'

第二种方法是通过查询[Information_Schema].[Tables]系统视图。 [Information_Schema].[Tables] 系统视图对于当前用户有权访问的当前数据库中的每个表都包含一行。该视图基于 [dbo].[sysobjects] 系统表。 [Information_Schema].[Tables] 系统视图也将包括列表中的视图。要仅过滤掉用户表，您将仅输出 [Table_Type] 为“BASE TABLE”的记录，如以下查询所示：

SELECT * FROM [Information_Schema].[Tables]
WHERE [Table_Type] = 'BASE TABLE'

列出数据库中用户表的第三种方法是使用 sp_tables系统存储过程。 sp_tables 系统存储过程返回可出现在 FROM 子句中的对象列表。由于您只关心用户表而不关心系统表或视图，因此必须将 @table_type 参数设置为“'TABLE'”，如以下查询所示：

EXEC sp_tables @table_type = "'TABLE'"

You don't mention what database you are using but I assume it's SQL Server.

Here's how you get a list of tables:

USE YourDBName
GO 
SELECT *
FROM sys.Tables

You can use Ado.Net to run the query or you can add the Tables table to your entity data model.

UPDATED

Here are some other ways. You may need to adapt them to your needs.

The [dbo].[sysobjects] contains one row for each object, such as constraint, table, view, stored procedure, function and so on, created within a database. To determine the type of object, you will query the [xtype] column which contains the object type. For user tables the [xtype] value is 'U' which stands for user tables.

SELECT [Name] FROM [dbo].[sysobjects]
WHERE [xtype] = 'U'

The second method is by querying the [Information_Schema].[Tables] system view. The [Information_Schema].[Tables] system view contains one row for each table in the current database for which the current user has permissions. This viw is based on the [dbo].[sysobjects] system table. The [Information_Schema].[Tables] system view will also include views in the list. To filter out just the user tables, you will only output those records where the [Table_Type] is 'BASE TABLE', as can be seen from the following query:

SELECT * FROM [Information_Schema].[Tables]
WHERE [Table_Type] = 'BASE TABLE'

The third method of listing the user tables in a database is by using the sp_tables system stored procedure. The sp_tables system stored procedure returns a list of objects that can appear in a FROM clause. Since you are only concerned with user tables and not system tables or views, you must set the @table_type parameter to "'TABLE'", as can be seen from the following query: