Jan 3, 2014

Sql query to drop or delete all the tables of a database which table name starts with given characters in sql server


Sql query to drop or delete all the tables of a database which table name starts with given characters in sql server

EXECUTE sp_MSforeachtable  @command1 = N'DROP TABLE ?', @whereand = N'AND o.name LIKE ''%tbl%'''

Above query will drop all the tables which name start with tbl.

Note:
o: It alias of system view sys.objects
syso: It alias of system view sys.sysobjects

Sql query to drop or delete give tables names of a database at a time

EXECUTE sp_MSforeachtable  @command1 = N'DROP TABLE ?', @whereand = N'AND o.name IN(''Student'',''Employee'',''Department'')'

Above query will drop the Student, Employee and Department tables.

No comments:

Post a Comment