How to drop tables based on sys.objects? [duplicat

Question

This question already has an answer here:

• How to delete all tables with prefix “bkp” from a given database? 1 answer

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?

Answer 1

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;