在SQL Server Management Studio中识别对象依赖性

最新推荐文章于 2023-07-03 11:46:48 发布

culuo4781

最新推荐文章于 2023-07-03 11:46:48 发布

阅读量268

点赞数

文章标签：数据库 java python mysql sql

原文链接：https://www.sqlshack.com/identifying-object-dependencies-in-sql-server-management-studio/

版权

In relational database systems, objects have different types of relationships with each other. Apart from table relationships (such as one to one, one to many and many to many), objects such as stored procedures, views, custom functions also have dependencies on other objects. It is important to understand object dependencies, particularly if you want to update an object that depends upon other objects.

在关系数据库系统中，对象之间具有不同类型的关系。除了表关系（例如一对一，一对多以及多对多）外，诸如存储过程，视图，自定义函数之类的对象还依赖于其他对象。了解对象依赖性非常重要，特别是如果您要更新依赖于其他对象的对象时，尤其如此。

Consider a scenario where a table has a one to many relationships with another table. Before deleting or modifying the table, you should know which objects are dependent on this table and what impact deleting the table will have on the dependent objects.

考虑一个表与另一个表具有一对多关系的情况。在删除或修改表之前，您应该知道哪些对象依赖于该表以及删除该表对依赖对象有什么影响。

In this article, we will see how SQL Server management studio can be used to Identify dependencies between database objects.

在本文中，我们将看到如何使用SQL Server Management Studio识别数据库对象之间的依赖关系。

准备虚拟数据 (Preparing Dummy Data)

Execute the following script to create a dummy database.

执行以下脚本以创建虚拟数据库。

CREATE DATABASE Library
 
GO 
 
USE Library;
 
CREATE TABLE Author
(
    id INT PRIMARY KEY IDENTITY,
    author_name VARCHAR(50) NOT NULL,
    
 )
 GO
 
CREATE TABLE Book
(
    id INT PRIMARY KEY IDENTITY,
    book_name VARCHAR(50) NOT NULL,
    price INT NOT NULL,
    author_id INT FOREIGN KEY REFERENCES Author(id)
   
 )
 
 GO
 
USE Library;
 
 INSERT INTO Author 
 
VALUES
('Author1'),
('Author2'),
('Author3'),
('Author4'),
('Author5'),
('Author6'),
('Author7')
 
 
INSERT INTO Book 
 
VALUES
('Book1',500, 1),
('Book2', 300 ,2),
('Book3',700, 1),
('Book4',400, 3),
('Book5',650, 5),
('Book6',400, 3)

In the script above, we create a database named “Library”. We then create two tables within the library database, “Author” and “Book”. The Author table has a one to many relationships with the Book table since the book table has a foreign key column author_id which references the id column of the Author table. In short, Book table is dependent upon the Author table.

在上面的脚本中，我们创建一个名为“ Library”的数据库。然后，我们在库数据库中创建两个表“ Author”和“ Book”。 Author表与Book表具有一对多关系，因为book表具有一个外键列author_id，该外键列引用Author表的id列。简而言之，Book表取决于Author表。

We then inserted some dummy records into both tables.

然后，我们将一些虚拟记录插入到两个表中。

Now let’s create two stored procedures: one dependent on the Book table and the other dependent on both the Book and Author tables.

现在，让我们创建两个存储过程：一个存储过程依赖于Book表，另一个存储依赖于Book和Author表。

Execute the following script:

执行以下脚本：

GO
-- Create procedure that depends on Book table
CREATE PROCEDURE sp_GetBooks
AS
BEGIN
	SELECT * FROM Book
END
GO
 
--Create procedure that depends on both book and author table
CREATE PROCEDURE sp_GetBooksAndAuthors
AS
BEGIN 
	SELECT Book.book_name as BookName, Author.author_name as AuthorName
	FROM Book
	JOIN Author
	ON Book.author_id = Author.id
END

In addition, let’s create a view that shows all the records from the Book table.

另外，让我们创建一个视图，该视图显示Book表中的所有记录。

USE Library
GO
CREATE VIEW vwAuthors
AS
	SELECT * FROM Author

Finally, we will create a table-valued function that returns book name and author name joined together using INNER JOIN.

最后，我们将创建一个表值函数，该函数返回使用INNER JOIN连接在一起的书名和作者名。

Execute the following script:

执行以下脚本：

USE Library
GO
CREATE FUNCTION fnGetBooksAndAuthors()
RETURNS TABLE
AS 
RETURN
	SELECT Book.book_name as BookName, Author.author_name as AuthorName
	FROM Book
	JOIN Author
	ON Book.author_id = Author.id

The summary of objects in the Library database, along with their type and dependencies is as follows:

Library数据库中的对象及其类型和依赖关系的摘要如下：

Object Name	Object Type	Dependencies
Author	Table	Nil
Book	Table	Author Table
sp_GetBooks	Stored Procedure	Book Table
sp_GetBooksAndAuthors	Stored Procedure	Book and Author Table
vwAuthors	View	Author Table
fnGetBooksAndAuthors	Table Valued Function	Books and Author Table

对象名称	对象类型	依存关系
作者	表	零
书	表	作者表
sp_GetBooks	储存程序	书桌
sp_GetBooksAndAuthors	储存程序	书和作者表
vwAuthors	视图	作者表
fnGetBooksAndAuthors	表值函数	书籍和作者表

We can see these dependencies because we created the database and the objects within the database. If the database is designed by someone else, or there are huge number of objects in a database, it is not so easy to keep track of all the dependencies. This is where SQL Server Management Studio can help you to identify object dependencies.

我们可以看到这些依赖性，因为我们创建了数据库以及数据库中的对象。如果数据库是由其他人设计的，或者数据库中有大量对象，那么跟踪所有依赖关系并不是那么容易。 SQL Server Management Studio可以在这里帮助您识别对象依赖性。

使用SQL Server Management Studio识别对象依赖性 (Using SQL Server Management Studio for Identifying Object Dependencies)

Let’s see how SQL Server management studio can be used to identify object identifies.

让我们看看如何使用SQL Server Management Studio来标识对象标识。

Go to SQL Server Object Explorer -> Databases -> Library -> Tables. Right click on dbo.Author table and select Delete. This is shown in the figure below:

转到SQL Server对象资源管理器->数据库->库->表。右键单击dbo.Author表，然后选择“删除”。如下图所示：

A “Delete Object” dialogue box will appear. Click on the OK button. An error will occur. Click the information in the Message Column to see the error in detail.

出现“删除对象”对话框。单击确定按钮。将会发生错误。单击“消息”列中的信息以详细查看错误。

The detailed error will look like this:

详细错误如下所示：

The error message clearly explains the reason for the error. It says that the dbo.Author object could not be dropped because it is referenced by a FOREIGN KEY constraint. Hence it cannot be deleted.

错误消息清楚地说明了错误原因。它说dbo.Author对象无法删除，因为它是由FOREIGN KEY约束引用的。因此，无法将其删除。

In this case, when we tried to delete a table that the other tables depend upon, SQL Server gives an error. However, this is not the case with all the dependencies.

在这种情况下，当我们尝试删除其他表所依赖的表时，SQL Server会出现错误。但是，并非所有依赖项都如此。

For instance, although both the stored procedures sp_GetBooks and sp_GetBooksAndAuthors are depending upon the Book table, we can delete the Book table without an error. This is because no other table is depending upon the Book table.

例如，尽管存储过程sp_GetBooks和sp_GetBooksAndAuthors都取决于Book表，但是我们可以删除Book表而不会出现错误。这是因为没有其他表依赖于Book表。

Try to delete the Book table by going to SQL Server Object Explorer -> Databases -> Library -> Tables. You will see that the table will be deleted successfully.

尝试通过转到SQL Server对象资源管理器->数据库->库->表来删除Book表。您将看到该表将被成功删除。

Now try to execute the sp_GetBooks and sp_GetBooksAndAuthors stored procedures that are dependent on the Book table. Execute the following script:

现在尝试执行依赖于Book表的sp_GetBooks和sp_GetBooksAndAuthors存储过程。执行以下脚本：

USE Library
EXEC sp_GetBooks

SQL Server management studio throws an error which looks like this:

SQL Server管理工作室会引发如下错误：

Since the Book object has been deleted, if we try to execute the stored procedure dependent on Book object, an error is thrown which says that “Invalid object name ‘Book’”.

由于Book对象已被删除，因此，如果我们尝试执行依赖Book对象的存储过程，则会引发错误，提示“对象名称'Book'无效”。

A similar error will be thrown if you try to execute the sp_GetBooksAndAuthors stored procedure. This shows how important it is to first identify object dependencies before deleting or modifying an object. Otherwise, you risk breaking the script.

如果尝试执行sp_GetBooksAndAuthors存储过程，将引发类似的错误。这表明在删除或修改对象之前首先确定对象的依赖性非常重要。否则，您可能会破坏脚本。

Let’s recreate the Book table and insert some dummy records into it.

让我们重新创建Book表并将一些虚拟记录插入其中。

USE Library;
CREATE TABLE Book
(
    id INT PRIMARY KEY IDENTITY,
    book_name VARCHAR(50) NOT NULL,
    price INT NOT NULL,
    author_id INT FOREIGN KEY REFERENCES Author(id)
   
 )
 
INSERT INTO Book 
 
VALUES
('Book1',500, 1),
('Book2', 300 ,2),
('Book3',700, 1),
('Book4',400, 3),
('Book5',650, 5),
('Book6',400, 3)

It is very easy to verify object dependencies in SQL Server. For instance, if you want to check what objects depend upon the Book table, go to SQL Server Object Explorer -> Databases -> Library -> Tables. Right click on “dbo.Book” and select “View Dependencies” from the context menu as shown in the following figure:

在SQL Server中验证对象依赖性非常容易。例如，如果要检查哪些对象取决于Book表，请转到SQL Server对象浏览器->数据库->库->表。右键单击“ dbo.Book”，然后从上下文菜单中选择“查看依赖项”，如下图所示：

Object Dependencies window will appear which looks like this:

将显示“对象相关性”窗口，如下所示：

By default, all the objects that depend upon the Book object are displayed. You can see fnGetBooksAndAuthors, sp_GetBooks and sp_GetBooksAndAuthors under the object dependencies. You can also check the object on which the Book object depends by selecting the second radio button with the title “Objects on which [Book] depends” as shown in the following screenshot.

默认情况下，将显示所有依赖Book对象的对象。您可以在对象依赖项下看到fnGetBooksAndAuthors，sp_GetBooks和sp_GetBooksAndAuthors。您还可以通过选择标题为“ [Book]依赖的对象”的第二个单选按钮来检查Book对象所依赖的对象，如以下屏幕截图所示。

You can see “Author” object in the list since this is the only object on which the Book table depends.

您可以在列表中看到“作者”对象，因为这是Book表所依赖的唯一对象。

You can identify object dependencies for stored procedures in the same way. So if you want to find the objects that the sp_GetBooksAndAuthors stored procedure depends upon, you can simply go to SQL Server Object Explorer -> Databases -> Library -> Programmability -> Stored Procedures and right click the dbo.sp_GetBooksAndAuthors stored procedure. From the context menu, select View Dependency and then select Objects on which [sp_GetBooksAndAuthors] depends upon. This is shown in the following figure:

您可以用相同的方式为存储过程标识对象依赖性。因此，如果要查找sp_GetBooksAndAuthors存储过程所依赖的对象，只需转到SQL Server对象资源管理器->数据库->库->可编程性->存储过程，然后右键单击dbo.sp_GetBooksAndAuthors存储过程。从上下文菜单中，选择“视图依赖”，然后选择[sp_GetBooksAndAuthors]所依赖的对象。如下图所示：

You can see Book and Author table in the object dependencies window. These are the two objects that the sp_GetBooksAndAuthors stored procedure depends upon.

您可以在对象依赖项窗口中看到Book and Author表。这是sp_GetBooksAndAuthors存储过程所依赖的两个对象。