Drop multiple tables with schema having \

This one is based on the following thread:

http://dbaspot.com/forums/sqlserver-faq/228429-problem-alter-schema-dbo-transfer-owrd-julainih-authorlist.html

I need to modify a bit to accommodate my scenario.

 

DECLARE @id varchar(255)
DECLARE @dropCommand varchar(255)

DECLARE tableCursor CURSOR FOR
    select '['+TABLE_SCHEMA+']'+'.'+table_name from INFORMATION_SCHEMA.TABLES
where TABLE_SCHEMA = 'STRATOS\donso'

OPEN tableCursor
FETCH next FROM tableCursor INTO @id

WHILE @@fetch_status=0
BEGIN
    SET @dropcommand = 'drop table ' + @id
    EXECUTE(@dropcommand)
    FETCH next FROM tableCursor INTO @id
    print 'drop table'+@id
END

CLOSE tableCursor
DEALLOCATE tableCursor

Tricky part:

1. Use information_schema to get the list of tables that belong to user STRATOS\donso

2. Since the schema contains ‘ \ ‘, we must use [] to escape. Otherwise, SSMS will keep popping error msgs. The solution is below

    select '['+TABLE_SCHEMA+']'+'.'+table_name from INFORMATION_SCHEMA.TABLES
where TABLE_SCHEMA = 'STRATOS\donso'