How to drop tables based on sys.objects?

Question

I am trying to drop tables from each server/DB.

I ran the query to get the list of the tables in each database from different server.

SELECT * 
FROM sys.objects 
WHERE type = 'u' AND name LIKE '%JSK%'

I want to drop those tables.

I need query how to do it?

Which dbms and waht about dependances you can't drop customers before orders, if orders has a foreign key reference for CustomerID for instance. — Tony Hopkinson, Mar 06 '13 at 19:06
those are user tables and not doing business with particular client name JSK. So we are trying to drop those USER Tables. We already checked data in all those table which only contain info from JSK only. — ETLUser, Mar 06 '13 at 19:09

score 5 · Answer 1 · answered Mar 06 '13 at 19:12

Assuming no foreign key relationships make order of dropping important:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
DROP TABLE '
  + QUOTENAME(SCHEMA_NAME([schema_id]))
  + '.' + QUOTENAME(name) + ';'
FROM sys.tables
WHERE name LIKE '%JSK%';

PRINT @sql;
-- EXEC sp_executesql @sql;

How to drop tables based on sys.objects?

1 Answers1

Linked