Removing Identical Duplicate Rows

Deleting duplicate rows out of a table can be tricky.  A brute force way to do this is with a TOP 1 and a cursor, but clearly there are set-based ways to accomplish this.  Using the ROW_NUMBER function with an appropriate OVER is fast and simple to understand.


This is a simple lab you can run to test it out.  

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.