In previous post, we have discussed how to change schema
of tables(single & multiple). Sometime we have two types of schema in one
database. In this article we will learn how to delete all table of specific schema.
SQL Query to delete table of schema [dbo]:
DECLARE @SqlQuery NVARCHAR(MAX)
SELECT @SqlQuery = COALESCE(@SqlQuery, N'') + N'DROP TABLE [dbo].' +
QUOTENAME(TABLE_NAME) + N';' + CHAR(13)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA =
'dbo' and
TABLE_TYPE = 'BASE
TABLE'
PRINT @SqlQuery
When you execute above query, it will print list of drop
table statements as given below:
DROP TABLE [dbo].[tblStudent];
DROP TABLE [dbo].[tblStaff];
DROP TABLE [dbo].[tblClass];
Now select above statements & execute to delete table
of schema [dbo].
Note: If you are using foreign key relationship then
you cannot delete tables with foreign key references.
