ALTER MULTIPLE TABLES in SQL Server

I have several tables with constraints in SQL Server and I would like to drop all them. Unfortunately, the constraints keep me from using the DROP TABLE; therefore, I have to ALTER each individual table to remove the constraints so I can use DROP TABLE statement and run it as Script.

Is there an easier way to ALTER MULTIPLE TABLES like using wild card (for example: ALTER dbo.app*) in SQL Server? In other words, what is the most efficient way for me to accomplish this?

Thank You

Hi Axel,

thanks for your post - it gave me a good start point and I have discovered that Drop Table dialog can easily generate drop statements for constraints if needed. it’s available only in dev environment for now, but I would be interested to hear other’s opinion about that before it will go out.

Do you guys think Toad should automatically generate drop for constraints at once?
drop_table.png

Hi Valentine,

I’m not totally agree with Axel. I understand the problem, but for me is better :

First drop all constraints that are presents in these tables.
Second drop tables.

In the past, constraints save me to accidentaly drop tables with valid datas.
It’s like use statement - delete from

- if you omit where clause you delete all datas in the tables, and you must restore values/datas from a valid backup.

From my point of view I prefer delete constraints and after drop table.

Best regards.

Sergio Bertolotti