SQL Server sp depends

In SQL Server, making changes to any existing database objects like deleting a table or changing the name of a table or a table column can affect other database objects like views, procedures, and functions where the updated or deleted database object was referenced. So before updating or deleting a database object, it is best to view the dependencies of the current object being deleted or edited. So we can use the SQL Server system Stored Procedure 'sp_depends' to check for object dependencies. In this article, we will see about 'sp_depends', its syntax, and usage in different scenarios.

Syntax:

sp_depends is a SQL Server system stored procedure and below is the syntax of its usage.

sp_depends [ @objname = ] '<object>' <object> ::= { [ database_name. [ schema_name ] . | schema_name. object_name }

Explanation of the syntax:

• @objectname - is the variable where the dependencies are assigned.
  
• <object> - is the name of the object for which the dependency is being checked.

Arguments:

• database_name: Replace here with the name of the database being used.
  
• schema_name: Replace with the name of the schema to which the object belongs.
  
• object_name: This refers to the database object, to check for dependencies. The object_name can be a table, view, stored procedure, user-defined function, or trigger. object_name data type is nvarchar(776), with no default value.

Example: How the 'sp_depends' is Used

Let us see a few examples of how the 'sp_depends' is used and the information we can view using this command.

The table used in the below examples:

CREATE TABLE [dbo].[Students](
    [Student_ID] [int] NULL,
    [Student_Name] [varchar](50) NOT NULL,
    [Grade] [char](1) NULL,
    [Grade_Level] [varchar](50) NULL,
    [Course] [varchar](80) NULL,
    [Exam_Name] [varchar](50) NULL,
    [Student_Age] [tinyint] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Students]  WITH CHECK ADD  CONSTRAINT [CK_StudentAge] CHECK  (([Student_Age]>=(3)))
GO
ALTER TABLE [dbo].[Students] CHECK CONSTRAINT [CK_StudentAge]
GO

In addition to the table, there are 2 Stored Procedures (spStudentsDemo1 and spStudentsDemo2), View named 'StudentRefView', and a scalar function named 'StudentTotalMarks' are there in the database. These are references to objects used in the examples given below

Example 1

EXEC sp_depends  @objname=N'Students'

Below is the output received:

The output shows the objects which are dependent on the table 'Students', meaning it displays the places where the table 'Students' is used or referenced. So if any change done in the table 'Students' can affect these objects.

In the Output, the column 'name' shows the objects where the table 'Students' is referenced and the type of each object is given under the column 'type'. The types referenced here are check constraint (check cns), stored procedure, view, and scalar function as displayed above.

Example 2

There is another result set displayed when an object is dependent on other objects like the View object 'StudentRefView'as given below:

exec sp_depends  @objname=N'StudentRefView'

In this example, the object 'StudentRefView' is a 'View', used to check the dependencies

Below is the output received with 2 result sets:

In the above output, there are 2 result sets. The first result set has 5 columns and as explained below, shows the objects, referenced by the current object 'StudentRefView' given in this example:

• name – Shows the name of the dependent object

• type – Displays the type of the dependent object (e.g. table)

• updated – This column shows whether the object is updated or not

• selected – object is used in a SELECT statement in the current object

• column – Dependent Column name from the object name specified

The second result set shows the name of the dependent objects and the type of the dependent objects used for creating the object 'StudentRefView'.

Conclusion

The sp_depends is a very useful and efficient tool when we have a large database and have multiple developers working on it so that before making changes to a database object, development team members can check and verify all the dependencies the database objects are not broken.

julietmaria

Follow

Improve

Article Tags :

• Geeks Premier League
• SQL Server
• Databases
• Geeks Premier League 2023