As humans we try to find a way to work faster and efficient. As data professionals, typing less probably is another thing we want. My SQL Server tip today is about dropping multiple objects (tables, views, stored procedures, and even databases) in single drop statement. This is powerful but can be dangerous in production so please use caution. Here is a script that creates couple of tables and then drops both tables in single drop statement.

Sincerely,

Bulent

-- CREATE TABLES

USE tempdb

GO

CREATE TABLE dbo.TableT1 (t1c1 TINYINT)

CREATE TABLE dbo.TableT2 (t2c1 TINYINT)

GO

INSERT INTO dbo.TableT1 VALUES(1)

INSERT INTO dbo.TableT2 VALUES(2)

GO

-- CHECK THE TABLES CREATED

SELECT *

FROM sys.tables

WHERE name LIKE 'TableT_'

GO

-- DROP BOTH TABLES IN SINGLE DROP STATEMENT

DROP TABLE dbo.TableT1, dbo.TableT2

GO

-- CHECK THE TABLES DROPPED

SELECT *

FROM sys.tables

WHERE name LIKE 'TableT_'

GO

Published under:  ·  ·  ·  ·