Find Dependencies

Here's a script from my toolbox I use to find dependencies between SQL objects.  The where clause has a number of sample filters.


--dependencies by dependency
select
ReferencingObjectName = rs.name + '.' + ro.name
, ReferencingObjectType = ro.type_desc
, ReferencedObjectName = s.name + '.' + o.name
, ReferencedObjectType = o.type_desc
from sys.sql_expression_dependencies d
inner join sys.objects o on d.referenced_id = o.object_id or d.referenced_minor_id = o.object_id
inner join sys.schemas s on o.schema_id = s.schema_id
inner join sys.objects ro on d.referencing_id = ro.object_id
inner join sys.schemas rs on ro.schema_id = rs.schema_id
where
ro.is_ms_shipped = 0
and o.is_ms_shipped = 0
--and op.type_desc = 'SQL_STORED_PROCEDURE'
and s.name = 'ObjectSchema'
and o.name = 'ObjectName'
group by rs.name , ro.name, s.name , o.name , ro.type_desc, o.type_desc
order by ro.name, o.name

..This operation must be performed in the master database."

"Cannot alter a server audit from a user database. This operation must be performed in the master database."

If you're trying to enable or disable a Audit or Server Audit Spec in SSMS, even if you're a sysadmin, you'll get the above failure if your default database isn't master.

If you're thinking that's a bug, you're right.

Note that you can create these audit objects just fine, regardless of your default database setting.

Change your login's default database to master to fix this problem, if you can.

Actual Emails: Is "NOLOCK is the epitome of evil?"

Sent: Friday, January 20, 2012 10:30 AM
To: William Assaf
Subject: dba has a question

William,

Can you send me references to support my contention that NOLOCK is the epitome of evil?

That is, that it can return invalid and/or unrelated data.

He is still suggesting that folks use NOLOCK (rather than READPAST).

We have to work around other poorly written queries, so some of these are inevitable.

Thanks,

[my colleague's name redacted to protect the innocent]