sql数据库中对象名无效
The present article deals with one of the bottlenecks in the work of database developers and administrators, namely the occurrence of invalid objects and the ways of finding them in the SQL Server database.
本文讨论了数据库开发人员和管理员的工作瓶颈之一，即无效对象的出现以及在SQL Server数据库中查找无效对象的方法。
In the previous articles we covered all the way from the idea to the implementation of a database for a recruitment service:
在前面的文章中，我们涵盖了从构思到实施招聘服务数据库的所有过程：
examined the database basic design rules
检查了数据库的基本设计规则
figured out how to fill a database with test data to provide a growth and performance forecast
找出如何用测试数据填充数据库以提供增长和性能预测
examined data export and import
检查数据的导出和导入
looked at the navigation through the created database, which involves searching for objects and data in a database
查看了在创建的数据库中的导航，其中涉及在数据库中搜索对象和数据
Let’s review the search for invalid objects with the help of the SQL Complete tool. Suppose we defined a new table with the employees’ addresses and named it Address:
让我们借助SQL Complete工具查看对无效对象的搜索。 假设我们用雇员的地址定义了一个新表，并将其命名为Address：
USE [JobEmpl] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Address]( [AddressID] [int] IDENTITY(1,1) NOT NULL, [Address] [nvarchar](1024) NOT NULL, CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED ( [AddressID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO And then defined a linking table between the tables Employee and Address and called it AddressEmployee: USE [JobEmpl] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[AddressEmpoyee]( [AddressID] [int] NOT NULL, [EmpoyeeID] [int] NOT NULL, CONSTRAINT [PK_AddressEmpoyee] PRIMARY KEY CLUSTERED ( [AddressID] ASC, [EmpoyeeID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[AddressEmpoyee] WITH CHECK ADD CONSTRAINT [FK_AddressEmpoyee_Address] FOREIGN KEY([AddressID]) REFERENCES [dbo].[Address] ([AddressID]) GO ALTER TABLE [dbo].[AddressEmpoyee] CHECK CONSTRAINT [FK_AddressEmpoyee_Address] GO ALTER TABLE [dbo].[AddressEmpoyee] WITH CHECK ADD CONSTRAINT [FK_AddressEmpoyee_Employee] FOREIGN KEY([EmpoyeeID]) REFERENCES [dbo].[Employee] ([EmployeeID]) GO ALTER TABLE [dbo].[AddressEmpoyee] CHECK CONSTRAINT [FK_AddressEmpoyee_Employee] GOThe employees may have different addresses. At the same time, a few employees can have the same address. Hence, the relationship between these entities is many-to-many:
员工的地址可能不同。 同时，几个员工可以拥有相同的地址。 因此，这些实体之间的关系是多对多的：
Img.1. The relationship between Employee and Address 图1。 员工与地址之间的关系We do not consider the case of residence registration when each employee has only one specified address. We are interested in all addresses, including those that specify where an employee tends to spend his time (where he lives and sleeps). There may be a few such places.
当每个雇员只有一个指定的地址时，我们不考虑居住登记的情况。 我们对所有地址都感兴趣，包括那些指定员工倾向于在哪里度过(他的生活和睡眠时间)的地址。 可能有一些这样的地方。
And now, let’s create a vEmployeeAddress view that will show the employee’s data and his addresses the following way:
现在，让我们创建一个vEmployeeAddress视图，该视图将通过以下方式显示员工的数据及其地址：
CREATE VIEW vAddressEmpoyee AS SELECT emp.[EmployeeID] ,emp.[FirstName] ,emp.[LastName] ,adr.[AddressID] ,adr.[Address] FROM [JobEmpl].[dbo].[Employee] AS emp LEFT OUTER JOIN [dbo].[AddressEmpoyee] AS aep ON emp.[EmployeeID]=aep.[EmpoyeeID] LEFT OUTER JOIN [dbo].[Address] AS adr ON aep.[AddressID] =adr.[AddressID]; So now we can easily extract the employees and their addresses. Supposing with time we decided that addresses are excessive, and it is enough to store the residence registration in the very Employee table: ALTER TABLE [dbo].[Employee] ADD [Address] NVARCHAR(1024) NULL; Since this address is unique for each employee. We drop the two tables AddressEmpoyee and Address: USE [JobEmpl] GO DROP TABLE [dbo].[AddressEmpoyee]; DROP TABLE [dbo].[Address]; GOThe thing is we forgot to change the vAddressEmployee view, which now refers to the non-existing tables.
问题是我们忘记了更改vAddressEmployee视图，该视图现在引用了不存在的表。
Before long, either a user or if we are lucky, a tester discovers a problem, where a part of system functionality crashes whenever it calls the vAddressEmployee view.
不久之后，无论是用户还是我们都很幸运，测试人员都会发现问题，每当调用vAddressEmployee视图时，系统功能就会崩溃。
To avoid this, every time the changes are introduced into the database, we need to check it for the existence of invalid objects.
为了避免这种情况，每次将更改引入数据库时，我们都需要检查其是否存在无效对象。
For this purpose, select the database you need and in the menu bar of SSMS, select the SQL Complete\Find Invalid Objects command:
为此，选择所需的数据库，然后在SSMS的菜单栏中，选择SQL Complete \ Find Invalid Objects命令：
Img.2. Selecting the “Find Invalid Objects” command in SQL Complete 图2。 在SQL Complete中选择“查找无效对象”命令In the window that appears, click on the “Analyze” button in the upper left corner or the middle of the window:
在出现的窗口中，单击窗口左上角或中间的“分析”按钮：
Img.3. Running the search for invalid objects 图3。 运行搜索无效对象Note that you can select multiple databases at once on the “Databases” panel:
请注意，您可以在“数据库”面板上一次选择多个数据库：
Img.4. Selecting multiple databases 图4。 选择多个数据库After the search for invalid objects is complete, we can see the result that displays our vAddressEmployee view, which refers to the non-existing tables AddressEmployee and Address:
搜索无效对象之后，我们可以看到显示vAddressEmployee视图的结果，该视图引用了不存在的表AddressEmployee和Address：
Img.5. The result of the search for invalid objects 图5。 搜索无效对象的结果It will be enough to rewrite the vAddressEmpoyee view, taking into account that the address is in the very Employee table, as follows:
考虑到地址位于Employee表中，只需重写vAddressEmpoyee视图就足够了，如下所示：
ALTER VIEW [dbo].[vAddressEmpoyee] ASSELECT emp.[EmployeeID] ,emp.[FirstName] ,emp.[LastName] ,emp.[EmployeeID] AS [AddressID] ,emp.[Address] FROM [JobEmpl].[dbo].[Employee] AS empGOOnce done, when you run the search for invalid objects for the second time, they are not found:
一旦完成，当您第二次运行搜索无效对象时，将找不到它们：
Img.6. None of the invalid objects were found 图6。 找不到无效的对象Note that the AddressID column should not have been shown in the vAddressEmployee view at all. However, if the system uses it, we need to determine the course of changes in two ways:
请注意，AddressID列根本不应该显示在vAddressEmployee视图中。 但是，如果系统使用它，我们需要以两种方式确定更改的过程：
Whether it is possible to substitute AddressID with the value from EmployeeID if the field is used just for information and not for the search for identical addresses. 如果该字段仅用于信息而不是用于搜索相同的地址，是否可以用EmployeeID中的值替换AddressID。 Whether it is possible not to show AddressID at all. 是否有可能根本不显示AddressID。If performing point 1 fails, we will have to introduce changes in the very logic of the application and perform the second point at the same time.
如果执行第1点失败，我们将不得不在应用程序的逻辑上进行更改，并同时执行第二个点。
Nevertheless, in case the first point is doable, this will be a quick solution to the problem, and you can later perform the second point with a hotfix or a next update.
但是，如果第一点可行，这将是对该问题的快速解决方案，以后您可以通过修复程序或下一次更新执行第二点。
In a nutshell, we have considered the importance of finding invalid objects and fixing them.
简而言之，我们已经考虑了找到无效对象并对其进行修复的重要性。
To sum up, we have looked into the entire process of creating a database for a recruitment service, starting from the idea and finishing with its implementation into production with further changes brought to the schema.
总而言之，我们研究了为招聘服务创建数据库的整个过程，从构想开始，一直到将其实现付诸生产，再对架构进行进一步更改。
The given database allows us to perform a quick search and to aggregate data according to the following metrics:
给定的数据库使我们能够根据以下指标进行快速搜索并汇总数据：
Employing companies. 用人单位。 Positions. 职位。 Projects. 项目。 Skills. 技能It stands to mention that this schema provided the foundation for the IWU TEAM startup.
值得一提的是，该模式为IWU TEAM启动提供了基础。
Originally published at https://blog.devart.com on August 11, 2020.
最初于 2020年8月11日 发布在 https://blog.devart.com 上。
翻译自: https://towardsdatascience.com/searching-for-invalid-objects-in-the-sql-server-database-a90fab49a66a
sql数据库中对象名无效
相关资源：微信小程序源码-合集6.rar