How to drop a constraint without knowing its table?

Question

I want to drop every user object in a SQL Server database but leave tables and its records intact. But I also want to drop its constraints. So I created this query to get drop declarations that I will paste into SMSS and execute after:

SELECT
    'DROP ' +
    CASE 
        WHEN [sys].[all_objects].type IN ('AF','FN','FS','FT','IF','TF') THEN 'FUNCTION '
        WHEN [sys].[all_objects].type IN ('D','C','F','PK','UQ') THEN 'CONSTRAINT '
        WHEN [sys].[all_objects].type IN ('IT','S','U') THEN 'TABLE '
        WHEN [sys].[all_objects].type IN ('P','PC','RF','X') THEN 'PROCEDURE '
        WHEN [sys].[all_objects].type IN ('TA','TR') THEN 'TRIGGER '
        WHEN [sys].[all_objects].type = 'R' THEN 'RULE '
        WHEN [sys].[all_objects].type = 'SN' THEN 'SYNONYM '
        WHEN [sys].[all_objects].type = 'TT' THEN 'TYPE '
        WHEN [sys].[all_objects].type = 'V' THEN 'VIEW '
    END +
    SCHEMA_NAME(sys.[all_objects].[schema_id]) + '.' + OBJECT_NAME(object_id) + '; ', OBJECT_NAME(object_id), [sys].[all_objects].[type_desc], [sys].[all_objects].[type], SCHEMA_NAME(sys.[all_objects].[schema_id])
FROM 
    sys.[all_objects] WITH (NOLOCK) -- WHERE (OBJECT_DEFINITION(object_id) LIKE '%' + @toDrop + '%') AND ([sys].[all_objects].[name] <> @toDrop);
WHERE
    (SCHEMA_NAME(sys.[all_objects].[schema_id]) <> 'sys') AND
    (SCHEMA_NAME(sys.[all_objects].[schema_id]) <> 'INFORMATION_SCHEMA') AND
    ([sys].[all_objects].[type_desc] <> 'USER_TABLE')
ORDER BY
    [sys].[all_objects].[type_desc], SCHEMA_NAME(sys.[all_objects].[schema_id]), OBJECT_NAME(object_id)

Problem is, in constraint case I have to use ALTER TABLE x DROP CONSTRAINT y

So How do I do to erase all constraints easily?

You'll have to have conditional logic, since the schema prefix won't be used for those statements either (well, it will be in a different place). You can get the parent table name by joining to `sys.tables` on `sys.objects.parent_object_id` (why, why, why are you using `sys.all_objects`?). — Aaron Bertrand, Jan 23 '15 at 17:47
@AaronBertrand Because I want to drop it all except the tables. I want to do a database migration, but all the tools I found are inneficient. — NaN, Jan 23 '15 at 17:48
`sys.all_objects` is basically a union of your objects and system objects. You have filters against `sys.all_objects` that essentially does the same thing. So just use `sys.objects`. Also, think about using aliases for readability, and if you don't want to drop tables, why does your CASE expression explicitly have an option for `DROP TABLE`? And you know you can't drop internal or system tables, right, so why are they there too? — Aaron Bertrand, Jan 23 '15 at 17:50
http://dba.stackexchange.com/questions/90033/how-do-i-drop-all-constraints-from-all-tables — Aaron Bertrand, Jan 23 '15 at 18:09
Just something to look out for: if you have computed columns in your tables that use user-defined functions, then you cannot drop the user-defined functions without breaking the table. — , Jan 23 '15 at 18:14
@hvd Thanks for the tip, It's the first step. Next step is to put back everything that is on the new database which has some differences. — NaN, Jan 23 '15 at 18:23

score 0 · Answer 1 · answered Jan 23 '15 at 18:06

    SELECT
        'ALTER TABLE ' + 
        SCHEMA_NAME(sys.[objects].[schema_id]) + '.' + OBJECT_NAME([sys].[objects].[parent_object_id]) +
        ' DROP CONSTRAINT ' +
        OBJECT_NAME(object_id) + '; ', OBJECT_NAME(object_id), [sys].[objects].[type_desc], [sys].[objects].[type], SCHEMA_NAME(sys.[objects].[schema_id]),
        OBJECT_NAME([sys].[objects].[parent_object_id])
    FROM 
        sys.[objects] WITH (NOLOCK) 
    WHERE
        (SCHEMA_NAME(sys.[objects].[schema_id]) <> 'sys') AND
        ([sys].[objects].[type_desc] <> 'USER_TABLE') AND
        ([sys].[objects].TYPE IN ('D','C','F','PK','UQ')) AND
    ORDER BY
        [sys].[objects].[type_desc], SCHEMA_NAME(sys.[objects].[schema_id]), OBJECT_NAME(object_id)
GO

You seem to have a fondness for the `OBJECT_NAME`/`SCHEMA_NAME` functions. Joins are better IMHO. Please read this: http://blogs.sqlsentry.com/aaronbertrand/bad-habits-metadata-helper-functions/ — Aaron Bertrand, Jan 23 '15 at 18:11

How to drop a constraint without knowing its table?

1 Answers1