- Published on
Drop all tables in a specific schema
- Authors
- Name
- Jeevan Wijerathna
- @iamjeevanvj
Find Tables by column names
While I was refactroing a legacy SQL server database I wanted to drop all the tables under a schema. Below is the query I used.
DECLARE @sql NVARCHAR(max)='' SELECT @sql += ' Drop table ' + QUOTENAME(TABLE_SCHEMA) + '.'+ QUOTENAME(TABLE_NAME) + '; ' FROM INFORMATION_SCHEMA.TABLES A WHERE TABLE_TYPE = 'BASE TABLE' and A.TABLE_SCHEMA = 'tmp' Exec Sp_executesql @sql