sql-serversql-server-2008-r2rulesdefaults

How to get names of all SQL Server Defaults and Rules

I'm trying to write a query to get the names of all Rules and Defaults in the database so I can programatically drop all of them from a database without having to know their names.

They don't seem to be contained in sys.objects, though - so where can I find them?

Rules and Defaults

Solution

  • Try this 

    SELECT *
FROM   sys.objects
WHERE  type = 'r' -- to filter rules
        OR ( parent_object_id = 0 -- to restrict default constraints 
             AND type = 'd' ) -- to filter defaults