CODEWITHSUNDEEP
sql-serversql-server-2005
user1048261
user1048261

Reputation: 263

How to delete all rows from all tables where id equals some value

I need to remove all records from all tables where some 'user_id' = 'some value'. How can I do it in MS SQL 2005? P.S Cascades is not everywhere.

Upvotes: 0

Views: 1223

Answers (1)

Martin Smith
Martin Smith

Reputation: 453212

If this is a one off task then a quick and dirty way. To delete user_id=999.

Run

SELECT 
        'DELETE FROM ' + quotename(OBJECT_SCHEMA_NAME(object_id)) + '.' 
             + quotename(OBJECT_NAME(object_id)) + ' WHERE user_id=999'
FROM sys.columns 
WHERE OBJECTPROPERTY(object_id, 'IsUserTable') = 1  AND name = 'user_id'

Copy and paste the result into SSMS. Execute repeatedly until it gives you no FK errors.

If you need this for more than a one off task you'll need to parameterise the DELETE statements and order them in the correct order to avoid FK errors.

Upvotes: 3

Related Questions