pointers, solutions and toolbox scripts for the SQL DBA
not intended to replace msdn, common sense or oxford commas

Tuesday, February 21, 2012

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
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
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

Friday, February 03, 2012

..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.