SQL Server – Backup Type

Background

Quite familiar with the three types of backup that we have in SQL Server.

Database Backup Types

The database backup types are full, differential, and log.

Metadata

msdb.dbo.backupset

To track backups we can look in the [msdb].[dbo].[backupset] table.

Something Broke?

Today I was trying to do a restore and noticed that that the type column in the [msdb].[dbo].[backupset] table read ‘F‘.

backupset.type ( Legacy )

Here is the traditional mapping of backup operation to type.

Backup Operation Backup Type ( backupset.type)
Database D
Differential I
Transaction L

File Groups Backup

To better support Very Large Databases ( VLDB ), Microsoft added a couple of targeted functionalities.

LAB

Let us play around with targeted file groups backup.

Database Properties

Database Properties – Filegroups

GUI

databaseProperties_GUI_20180625_1047PM.png

Code

sp_helpfilegroup
Sample

use [DBLab]
go

exec sp_helpfilegroup
go

Output

sp_helpfilegroup__20180625__1054PM

sys.filegroups
Sample

use [DBLab]
go

select
        [filegroup]
        = tblSFG.[name]

    , [filegroupID]
        = tblSFG.[data_space_id]

    , [isReadOnly]
        = tblSFG.[is_read_only]

    , [isDefault]
        = tblSFG.is_default

from   sys.filegroups tblSFG

order by
	tblSFG.[name] asc

Output

 sysDOTfilegroups_20180625_1108PM

Explanation
  1. isReadOnly
    • fgAudioFiles
  2. isDefault
    • PRIMARY

Backup

Database Backup – Normal

Sample


use master
go

backup database [DBLab]
	to DISK='NUL:'
	with init
	    , format
		, description='normal' 

Output

database_backup_normal_20180625__1138PM

Explanation

  1. Data files
    • DBLab, fileAudioFile_001, fileVideo_001
  2. Log
    • DBLab_log

Review Backup Type

SQL

declare @dbname sysname

set @dbname = 'DBLab';

select top 1

          backup_set_id

        , [type]

        , [database]
            = tblBS.[database_name]

        , [description]
            = tblBS.[description]

from   [msdb].[dbo].[backupset] tblBS

where tblBS.[database_name] = @dbname

order by
        backup_set_id desc

Output

msdb__dbo__backupset__20180625__1138PM

Database Backup – Read/Write File Groups

Overview

Add read-write_filegroups to implicitly skip read-only file groups.

Sample


use master
go

backup database [DBLab]
    read_write_filegroups
	to DISK='NUL:'
	with init
	, format

Output

readwritefilegroups__20180625__1148PM

Explanation

  1. Included
    • Data files
      • DBLab, fileVideo_001
    • Log
      • DBLab_log
  2. Not Included (as readonly )
    • fileAudioFile__001

Review Backup Type

Output

readwritefilegroups__backupset_20180625__1152PM

Explanation
  1. Type=P
    • Partial

Database Backup – Specific filegroups

Overview

Use the filegroup marker to explicitly tag filegroups you want included.

Sample


use master
go

backup database [DBLab]
	filegroup='PRIMARY'
	to DISK='NUL:'
	with init
	    , format
	    , description='filegroup explicit ( primary)'

Output

filegroupsTag__20180626_1203AM

Explanation

  1. Included
    • Data files
      • DBLab
    • Log
      • DBLab_log
  2. Not Included (as not part of tagged filegroup)
    • fileAudioFile__001
    • fileVideo_001

Review Backup Type

Output

filegroupsTag__20180626_1205AM

Explanation
  1. Type=F
    • File Groups

backupset.type ( Revised )

Here is a revised mapping of backup operations that includes new file groups subtleties.

Backup Operation File Group Backup Type ( backupset.type)
Database D
Differential I
Transaction L
Partial Read Write file groups only / Skip Read Only file groups P
File Groups or Files  Explicitly listed file groups F

References

  1. Microsoft

SQL Server – Database – Permissions

Background

As a follow-up to our discussion on SQL Server Instance Security permissions, let us discuss database permissions.

BTW, our discussion on SQL Server Instance Security permission is here.

Delegating Permissions

Permissions can be granted via adding principals to database roles.

Permissions can also be granted via assigning permissions.

Roles

View Available Roles

sp_helprole

To view available roles, please issue sp_helprole.

Syntax


exec sp_helprole

Output

sp_helprole

Explanation

  1. Public
    • All database principals belong to the public role
  2. Pre-defined roles
    • Pre-defined roles are MSFT’s pre-tailored roles
    • The RoleId starts at 16384
    • Roles
      • *deny*
        • db_denydatareader
          • Be careful with db_denydatareader as it means that those members granted membership will not be able to read data
        • db_denydatawriter
          • Also, be careful with db_denydatawriter as it means member-ed will not be able to write data
  3. User Defined Roles
    • Reporting Services
      • RSExecRole

Review Role Members

Outline

  1. sp_helprolemember
  2. sp_helpuser

sp_helprolemember

Overview

We can utilize sp_helprolemember to read role members.

Syntax


exec sp_helprolemember
     @rolename = [principal]

Sample


declare @rolename sysname

set @rolename = 'RSExecRole';

exec sp_helprolemember
       @rolename = @rolename

Output

sp_helprolemember__20180623_0846PM

sp_helpuser

Overview

When applied against a role, the sp_helpuser function returns information about role members.

Syntax


exec sp_helpuser
     @name_in_db = [principal]

Sample


use [ReportServer]
go

declare @rolename sysname

set @rolename = 'RSExecRole';

exec sp_helpuser
	@name_in_db = @rolename

Output

sp_helpuser__20180623_0912PM

Explanation

  1. Role RSExecRole has a single member NT SERVICE\ReportServer

sys.database_role_members

Overview

The sys.database_role_members table list roles and corresponding members.

Sample


use [ReportServer]
go

declare @rolename sysname

set @rolename = 'RSExecRole'

select
		  [role]
			= tblSDPR2.[name]

		, [member]
			= tblSDPR.[name]

from sys.database_role_members tblSDRM

-- cross reference members
inner join sys.database_principals tblSDPR

	on tblSDRM.member_principal_id = tblSDPR.principal_id 

-- cross reference roles
inner join sys.database_principals tblSDPR2
	on tblSDRM.role_principal_id = tblSDPR2.principal_id

where tblSDPR2.[name]
		= isNull
			(
				  @rolename
				, tblSDPR2.[name]
			)

Output

sysDOTdatabase_role_members__20180624_0940AM

Explanation

  1. Role RSExecRole has a single member NT SERVICE\ReportServer

Permissions

View Available Permissions

sys.fn_builtin_permissions

To view available permissions, please query sys.fn_builtin_permissions.

Be sure to pass along ‘database’ for the securable class you are querying for.

Sample


select 

		  tblSFNBP.class_desc
		, tblSFNBP.permission_name
		, tblSFNBP.type
		, tblSFNBP.covering_permission_name

from   sys.fn_builtin_permissions('Database') tblSFNBP

order by
	  tblSFNBP.class_desc
	, tblSFNBP.permission_name
	, tblSFNBP.type
	, tblSFNBP.covering_permission_name

Output

sysDOTfn_builtin_permissions__2018024__1002AM

View Assigned Permissions

Outline

  1. sys.database_permissions
  2. sp_helprotect

sys.database_permissions

To view assigned permissions, please query sys.database_permissions.

Sample


declare @principal sysname

set @principal = 'ivrusr'

select 

		  [principal]
			= tblSDPR.[name] 

		, [class]
			= tblSDPE.class

		, [classDescription]
			= tblSDPE.class_desc

		, [object]
			= case

				when (tblSDPE.class = 0)
					then
						  quotename
						  (
							db_name
							(
								tblSDPE.major_id
							)
						  )

				when (tblSDPE.class = 1)
					then
						  quotename
						  (
							object_schema_name
							(
								tblSDPE.major_id
							)
						  )
						+ '.'
						+ quotename
							(
								object_name
								(
									tblSDPE.major_id
								)
							)

				when (tblSDPE.class = 3)
					then
						  quotename
						  (
							schema_name
							(
								tblSDPE.major_id
							)
						 )

			  end

		, [permission]
			= tblSDPE.permission_name

		, [state]
			= tblSDPE.state_desc

-- database principals
from sys.database_principals tblSDPR

-- database permissions
inner join sys.database_permissions tblSDPE

		on tblSDPE.[grantee_principal_id] = tblSDPR.principal_id

where tblSDPR.name = isNull(@principal, tblSDPR.name)

/*
	Skip MS Shipped Objects - Begin
*/
and   not exists
	(

		select *

		from   sys.database_permissions tblSDP_I

		where 

			(

					( tblSDP_I.major_id = tblSDPE.major_id )

				and ( tblSDP_I.class = 1 )

				and (
						objectProperty
						(
								tblSDP_I.major_id
							, 'IsMSShipped'
						) = 1
					)
			)

	) -- not exists

/*
	Skip MS Shipped Objects - End
*/

order by

	  tblSDPR.[name]

	, tblSDPE.class

	, tblSDPE.class_desc

Output

sysDOTdatabase_permissions__20180624_1015AM

sp_helprotect

The classic API for determining permission set is sp_helprotect.

Arguments

Argument Usage Explanation Valid Values
name Object Name
username Principal
grantorname Grantor Name
permissionarea Principal Display object permissions (character string o), statement permissions (character string s), or both (os). With a default of os.
type can be any combination of o and s

Sample


declare @principal sysname
declare @permissionarea varchar(10)

set @permissionarea = 'os'
set @principal = 'ivrusr'

exec sp_helprotect
		  @name = null
		, @username = @principal
		, @grantorname = null
		, @permissionarea = @permissionarea

Output

sp_helprotect__20180624__1116AM

 

Reference

  1. Microsoft
    • Security Functions
      • sp_helprole
      • sp_helprotect
      • sys.fn_builtin_permissions
      • sp_helpuser
    • System Catalog Views
      • sys.database_role_members

 

SQL Server – Instance – Permissions

 

Background

Let us review SQL Instance Server Roles and Server Permissions.

Outline

  1. server roles
    • To get a read of available server roles, please issue sp_helpsrvrole
    • To read assigned server roles, please access sys.server_role_members
  2. server permissions
    • To get a read of available server permissions, please issue sys.fn_builtin_permissions
    • To identify server permissions for self, please use sys.fn_my_permissions
    • To gather assigned server permissions, please access sys.server_permissions

Server Roles

List available Server Roles

sp_helpsrvrole

Syntax


 exec sp_helpsrvrole

Output

serverRoles_20180622_0902AM

List Principals and Assigned Server Roles

sys.server_role_members

Syntax


select
          [principal]
            = tblSSP.[name]

/*

        , [principalID]
            = tblSSP.principal_id

        , [principalType]
            = tblSSP.[type_desc]

        , [principalCreateTS]
            = tblSSP.create_date

*/
        , [role]
            = tblSSR.[name]

/*

        , [roleID]
            = tblSSRM.[role_principal_id]
*/
from   sys.server_principals tblSSP

inner join sys.server_role_members tblSSRM

        on tblSSP.[principal_id] = tblSSRM.[member_principal_id]

inner join sys.server_principals tblSSR

        on tblSSRM.role_principal_id = tblSSR.principal_id

order by
      tblSSP.[name] asc
    , tblSSR.[name] asc

Output

sysDOTserver_role_Members_20180622_0932AM

Server Permissions

List Built-In Permissions

sys.fn_builtin_permissions

Syntax


select
		  [class]
			= tblSFBP.[class_desc]

		, [permision]
			= tblSFBP.[permission_name]

		, [type]
			= tblSFBP.[type]

from   sys.fn_builtin_permissions
		(
			'server'
		) tblSFBP

order by

	  tblSFBP.[class_desc]
	, tblSFBP.[permission_name]

Output

sysDOTfn_builtin_permissions__20180622_0951AM

List Server Permissions Assigned to Self

sys.fn_my_permissions

Syntax

select 

	  [principal]
		= SYSTEM_USER

	, [entity]
		= tblSFMP.[entity_name]

	, [permission]
		= tblSFMP.[permission_name]

from   sys.fn_my_permissions
			(
				  null
				, 'SERVER'
			) tblSFMP

order by
	  tblSFMP.[entity_name]
	, tblSFMP.[permission_name]

Output

sysDOTfn_my_permissions__20180622_1058AM.jpg

List Assigned Server Permissions

List principals and assigned permissions.

sys.server_permissions

Code


select
      [principal]
        = tblSSP2.[name]

    , [permission]
        = tblSSP.[permission_name]

    , [state]
        = tblSSP.[state_desc]

    --, tblSSP2.is_fixed_role

    --, tblSSP2.type
    -- , tblSSP.*

    , [principalType]
        = tblSSP2.[type_desc]

/*
    , [createTS]
        = tblSSP2.create_date
*/
from   sys.server_permissions tblSSP

INNER JOIN sys.server_principals tblSSP2

    ON tblSSP.grantee_principal_id
         = tblSSP2.principal_id

where
    (

        -- (tblSSP2.type != 'C')
        (
           tblSSP2.[type_desc]
             != 'CERTIFICATE_MAPPED_LOGIN'
        )

    )

and (
        (
            tblSSP.permission_name not in
                (
                    'CONNECT SQL'
                )
        )
    )

and (
        (
            -- class = 105
            tblSSP.class_desc not in
                (
                    'ENDPOINT'
                )
        )	

    )

order by
      tblSSP2.[name] asc
    , tblSSP.[permission_name] asc

Output

sysDOTserver_permissions__20180622_1037AM

 

References

  1. Microsoft
    • Docs / SQL / Relational databases / System catalog views
      • sys.server_permissions (Transact-SQL)
      • sys.server_role_members (Transact-SQL)
    • Docs / SQL / Relational databases / System stored procedures
      • sp_helpsrvrole (Transact-SQL)
    • Docs / SQL / Relational databases / System functions
      • sys.fn_builtin_permissions (Transact-SQL)
    • Docs / SQL / Relational databases / System stored procedures
      • Security Stored Procedures (Transact-SQL)

 

Transact SQL – Schema – Operations

Background

Wanted to document a couple of operations one can take on schemas.

Outline

  1. Check Existence
  2. Create Schema
  3. Change Object’s Schema ( Transfer Object from one schema to another)
  4. Grant Permission

Action

Exist?

Syntax

schema_id()

Sample

set nocount on
go

set XACT_ABORT on
go

declare @schema sysname
declare @schemaID int

set @schema = 'security';

set @schemaID = schema_id(@schema);

if ( @schemaID is null )
begin

	print 'Schema ' + @schema + ' does not exist!'

end
else
begin

	print 'Schema ' + @schema + ' exists'

end

Create

Syntax

create schema [schema] authorization [owner];

Sample

if schema_id('security') is null
begin

	exec('create schema [security] authorization [dbo]');

end

Change Object Schema

Syntax


alter schema [schema-new]
   transfer [scheme-current].[object-current]

Sample


set nocount on;
go

set XACT_ABORT on;
go

declare @object sysname
declare @commit bit

set @object = 'permission';

set @commit = 0

if schema_id('security') is null
begin

    exec('create schema [security] authorization [dbo]');

end

begin tran

    --exec sp_help '[dbo].[permission]'

    select
              [schema] = tblSS.[name]
            , [object] = tblSO.[name]

    from   sys.objects tblSO

    inner join sys.schemas tblSS

            on tblSO.schema_id = tblSS.schema_id

    where  tblSO.[type] = 'U'

    and    tblSO.[name] = @object

    if object_id('[dbo].[permission]') is not null
    begin

        alter schema [security]
            transfer [dbo].[permission]

    end	

    -- exec sp_help '[security].[permission]'

    select
              [schema] = tblSS.[name]
            , [object] = tblSO.[name]

    from   sys.objects tblSO

    inner join sys.schemas tblSS

            on tblSO.schema_id = tblSS.schema_id

    where  tblSO.[type] = 'U'

    and    tblSO.[name] = @object

while (@@trancount > 0)
begin

    if (@commit = 1)
    begin

        print 'commit  tran'
        commit tran;

    end
    else
    begin

        print 'rollback tran'
        rollback tran;

    end

end

Grant Permissions

Syntax


    grant [permission] on [schema]::[schema-name] to [database-principal];

Sample


set nocount on
go

set XACT_ABORT on
go

declare @schema sysname
declare @schemaID int
declare @commit   bit

set @commit = 0

set @schema = 'security';

set @schemaID = schema_id(@schema);

begin tran

	if ( @schemaID is null )
	begin

		print 'Schema ' + @schema + ' does not exist!'

	end
	else
	begin

		print 'Schema ' + @schema + ' grant permission'

		grant select on schema::[security] to [public];

	end

while (@@trancount > 0)
begin

    if (@commit = 1)
    begin

        print 'commit  tran'
        commit tran;

    end
    else
    begin

        print 'rollback tran'
        rollback tran;

    end

end

Transact SQL – No Join Predicate – “Unioned” CTE

Background

In our last post, we laid the foundation for this post.

Foundation Post

In that previous post we covered two very simple examples that triggers the “No Join Predicate” warning.

Here is that post:

Transact SQL – Warning – “No Join Predicate”
Link

 

Common Table Expression – Union

Overview

In this post we will try to trigger same warning by using a Union within a common table expression.

Diagram

diagram_school_20180619_1102AM.png

Data

Base Table

data_baseTable_20180619_0945AM.png

Trip Participant

data_tripParticipant_20180619_1003AM.png

Stored Procedure

school.usp_TripParticipants_List

Code


use [DBLab]
go

if object_id('school.usp_TripParticipants_List') is null
begin

    exec('create procedure [school].[usp_TripParticipants_List] as ')

end
go

alter procedure [school].[usp_TripParticipants_List]
(
    @tripID bigint
)
as

begin

    set nocount on

    set XACT_ABORT on


    ; with cteSchoolBody
    (
          id
        , identifier
        , firstname
        , lastname
        , [poolID]
        , [pool]
    )
    as
    (

        select 
                 tblS.id
               , tblS.identifier
               , tblS.firstname
               , tblS.lastname
               , [poolID] = 1
               , [pool] = 'Student'

        from   [school].[student] tblS

        union all

        select 
                 tblF.id
               , tblF.identifier
               , tblF.firstname
               , tblF.lastname
               , [poolID] = 2
               , [pool] = 'Faculty'

        from   [school].[faculty] tblF

    )
    select 
              [tripID] = tblT.[id]

            , [tripName] = tblT.[name]

            , cteSB.[pool]

            , cteSB.lastname

            , cteSB.firstname

            , cteSB.identifier

    from   [school].[trip] tblT

    --inner join [school].[tripParticipant] tblTP
    inner join [tripParticipant] tblTP

        on tblT.id = tblTP.tripID

    inner join cteSchoolBody cteSB
        on  tblTP.participantID = cteSB.id
        /*
            intentionally left of participantTypeID
            and tblTP.[participantTypeID] 
				= cteSB.[poolID]
        */ 
    where (

            ( 
                tblT.[id] 
                    = isNull(@tripID, tblT.[id])
            )

          )

    order by
              [tripName]

            , [pool]

            , cteSB.lastname

            , cteSB.firstname

    
end
go




 

Output

tripParticipant_20180619_1012AM

Explanation
  1. Noticed duplicate records

 

Query Plan

qp_20180619_1015AM

Explanation
  1. Thought that missing the participantTypeID will trigger “No Join Predicate” warning.

Source Code

GitHub

DanielAdeniji/SQLServer.SampleDB.School
Link

Listening

Paul Simon – Take me to the Mardi Gras
Link

Transact SQL – Warning – “No Join Predicate”

 

Background

A couple of weeks ago, I noticed a warning in a query plan.

The warning read “No Join Predicate“.

Query Plan

Warning – No Join Predicate

Sample Queries

Conventional Join

Code

use [DBLab]
go

if object_id('school.usp_TripParticipants_List_NJP_Legacy') is null
begin

    exec('create procedure school.usp_TripParticipants_List_NJP_Legacy as ')

end
go

alter procedure [school].[usp_TripParticipants_List_NJP_Legacy]
as

begin

    ; with cteParticipant
    as
    (
        select *

        from   [school].[student]

        union 

        select *

        from   [school].[faculty]

    )
    select 

              [trip]
				 = tblT.[name]
            , [tripDate]
				 = [tblT].tripDate

            , [participant]
				=
				  tblP.firstname
				+ ' '
				+ tblP.lastname

    from
          [school].[tripParticipant] tblTP
        , [school].[trip] tblT
        , cteParticipant tblP

    where tblTP.tripID = tblT.id

    -- and   tblTP.participantID = tblP.id

end

go

Query Plan

school__usp_TripParticipants_List_NJ__20180618__1145A

Explanation
  1. In this example we are using the old classic join
  2. We have three tables, but only joined the first two tables

 

ANSI Join

Code

use [DBLab]
go

if object_id('school.usp_TripParticipants_List_NJP') is null
begin

    exec('create procedure school.usp_TripParticipants_List_NJP as ')

end
go

alter procedure [school].[usp_TripParticipants_List_NJP]
as

begin

    ; with cteParticipant
    as
    (
        select *

        from   [school].[student]

        union 

        select *

        from   [school].[faculty]

    )
    select 

              [trip] = tblT.[name]
            , [tripDate] = [tblT].tripDate
            , tblP.firstname
            , tblP.lastname

    from   [school].[tripParticipant] tblTP

    inner join [school].[trip] tblT

        on tblTP.tripID
            = tblT.id

    inner join cteParticipant tblP
        /*
            on tblTP.participantID
                = tblP.id
        */
        on tblTP.tripID
             = tblT.id

end

go

Query Plan

school__usp_TripParticipants_List_NJ__20180618__1150AM

Explanation
  1. In this example we are using ANSI join
  2. We have three tables and two join clauses
  3. Unfortunately the second join does not actually join the two tables that it is meant to join
    • We intentionally cut and pasted the early join.
    • But, did not modify the pasted clause and reference the actual tables we are trying to join

 

Summary

The samples listed above are simple and easy to correct.

The one I actually ran into is a bit more difficult to address.

 

 

Transact SQL :- Get Object Dependency Using sys.sql_expression_dependencies

 

Background

As a database Administrator, one has to quickly look at SQL and try to identify which objects it touches.

API

There are a couple of pathways to identify objects that a programmable object references.

Inclusive are:

  1. sp_depends
  2. sys.sql_expression_dependencies

Code

Sample

Procedure

SP – [school].[usp_TripParticipants_List]

Here is a SP that list those going on a trip…..


use [DBLab]
go

if object_id('school.usp_TripParticipants_List') is null
begin

    exec('create procedure [school].[usp_TripParticipants_List] as ')

end
go

alter procedure [school].[usp_TripParticipants_List]
(
    @tripID bigint
)
as

begin

    set nocount on

    set XACT_ABORT on

    ; with cteSchoolBody
    as
    (
        select
                 tblS.id
               , tblS.identifier
               , tblS.firstname
               , tblS.lastname
               , [pool] = 'Student'

        from   [school].[student] tblS

        union all

        select
                 tblF.id
               , tblF.identifier
               , tblF.firstname
               , tblF.lastname
               , [pool] = 'Faculty'

        from   [school].[faculty] tblF

    )
    select
              [tripID] = tblT.[id]

            , [tripName] = tblT.[name]

            , cteSB.[pool]

            , cteSB.lastname

            , cteSB.firstname

            , cteSB.identifier

    from   [school].[trip] tblT

    --inner join [school].[tripParticipant] tblTP
    inner join [tripParticipant] tblTP

        on tblT.id = tblTP.tripID

    inner join cteSchoolBody cteSB
        on tblTP.participantID = cteSB.id

    where (

            (
                tblT.[id]
                    = isNull(@tripID, tblT.[id])
            )

          )

    order by
              [tripName]

            , [pool]

            , cteSB.lastname

            , cteSB.firstname

end
go

metadata

Procedure

SP – [master].[dbo].[sp_ObjectDependants]

Here is a SP that we use to seek out dependants….


use [master]
go

if object_id('[dbo].[sp_ObjectDependants]') is null
begin

    exec('create procedure [dbo].[sp_ObjectDependants] as ')

end
go

alter procedure [dbo].[sp_ObjectDependants]
(
    @object sysname
)
as

begin

    ;with cteObjectDependencies
    as
    (

        -- find dependency
        -- exec sp_help 'sys.sql_expression_dependencies'
        SELECT
            [referencingObjectID]
            = referencing_id

        , [referencingObject]
            =   quoteName
                (
                    OBJECT_SCHEMA_NAME ( referencing_id )
                )
                + '.'
                + quoteName
                    (
                        OBJECT_NAME(referencing_id)
                    )	

        , [referencingObjectType]
            = obj.[type_desc]

        , [referecedObjectClass]
            = sed.referenced_class_desc

        , [referencedObjectID]
            = sed.referenced_id

        , [referencedObject]
            =
                isNull
                (
                    quoteName
                    (
                        sed.referenced_schema_name
                    )
                    + '.'
                , ''
                )
                + quoteName
                    (
                        sed.referenced_entity_name
                    )

            , [referencedObjectType]
                = objRef.[type_desc]

            , [isCallerDependent]
                = case
                    when (sed.is_caller_dependent=0) then 'N'
                    else 'Y'
                  end	

            , [isAmbiguous]
                = case
                    when (sed.is_ambiguous=0) then 'N'
                    else 'Y'
                  end	

        FROM sys.sql_expression_dependencies AS sed

        INNER JOIN sys.objects AS obj
            ON sed.referencing_id = obj.object_id

        LEFT OUTER JOIN sys.objects AS objRef
            ON sed.referenced_id = objRef.object_id	

        WHERE sed.referencing_id = object_id(@object)

    )
    select

           [referencedObject]

         , [referencedObjectType]

         , [referecedObjectClass]

         , [isCallerDependent]

         , [isAmbiguous]

         , [referencedObjectID]

    from   cteObjectDependencies cteOD

    order by

           [referencedObject]

         , [referencedObjectType]

end
go

exec sp_MS_marksystemobject '[dbo].[sp_ObjectDependants]'
go

Demo

sp_depends

Sample


use [DBLab]
go

declare @object sysname

set @object = '[school].[usp_TripParticipants_List]'

exec sp_depends @object

Output

sp_depends_20180615_1215PM

[dbo].[sp_ObjectDependants]

Sample


use [DBLab]
go

declare @object sysname

set @object = '[school].[usp_TripParticipants_List]'

exec [dbo].[sp_ObjectDependants]
	@object = @object

Output

objectDependency_20180615_1217PM

Source Code Control

GitHub

DanielAdeniji/TransactSQL.ObjectDependency
Link

Summary

sp_depends tells us the action invoked on dependent objects.

It lets us know whether updates were made or just reads.

Whereas sys.sql_expression_dependencies does not appear to make that distinction.