Things to be Aware of When You Drop a Table

 

Other Things to be Aware of When You Drop a Table

Some other functionalities to be aware of when a table is dropped are:

• Views
• Stored Procedures
• Functions
• Triggers
• Indexes
• Statistics

If a table is dropped and there are associated views, stored procedures or functions that were created without schema binding, then stored procedures, functions, and views will still exist but will no longer work. But, what about triggers, indexes, and statistics?

To demonstrate, we'll:

1. Create and populate the employees table again
2. Create an index, function and trigger on it
3. Create a stored procedure, function, and view that query it

-- create table
CREATE TABLE [dbo].[employees] (
[LastName] [varchar](100) NOT NULL,
[FirstName] [varchar](100) NOT NULL,
[Title] [varchar](50) NULL,
[HireDate] [date] NULL,
[TerminationDate] [date] NULL,
[RehireDate] [date] NULL,
[Address] [varchar](100) NULL,
[City] [varchar](100) NULL,
[Region] [varchar](100) NULL,
[PostalCode] [varchar](100) NULL);
GO
 
-- populate table
INSERT INTO [dbo].[Employees]
VALUES('Jones', 'Robert', 'Accountant', '2010-07-01', NULL, NULL, '1 Main St', 'Anywhere', 'MA', '12345'), 
      ('Forth', 'Sally', 'Director', '2010-07-15', NULL, NULL, '27 Elm St', 'Somewhere', 'NH', '23456');
GO
 
-- create index
CREATE CLUSTERED INDEX [idx_Title] ON [dbo].[employees]
(
   [Title] 
);
GO
 
-- create statistic
CREATE STATISTICS [MyStat] ON [dbo].[employees]([LastName])
GO
 
-- create trigger
CREATE TRIGGER [dbo].tr_employees 
   ON  [dbo].[employees]
   AFTER INSERT
AS 
BEGIN
   SELECT * FROM inserted
END
GO
 
-- create view  
CREATE VIEW [dbo].[vEmployees] AS(
SELECT [FirstName],[LastName],[Title]
FROM [dbo].[Employees]
);
GO
 
-- create procedure
CREATE PROCEDURE [dbo].[uspNumOfEmp] AS
BEGIN
   SELECT COUNT(*) FROM [dbo].[employees]
END;
GO
 
-- create function
CREATE FUNCTION dbo.NumOfEmp()
RETURNS INT
AS 
BEGIN
   RETURN (SELECT COUNT(*) FROM [dbo].[employees])
END
GO

Here, querying the related system views, we see our view, stored procedure, and function.

SELECT name AS [View]      FROM sys.views      WHERE name LIKE 'vemployees'
SELECT name AS [Procedure] FROM sys.procedures WHERE name LIKE 'uspNumOfEmp'
SELECT name AS [Function]  FROM sys.objects    WHERE name LIKE 'NumOfEmp'

And here, we see our trigger, index, and statistic.

SELECT name AS [Trigger]   FROM sys.triggers   WHERE name LIKE 'tr_employees'
SELECT name AS [Index]     FROM sys.indexes    WHERE name LIKE 'idx_Title'
SELECT name AS [Statistic] FROM sys.stats      WHERE name LIKE 'MyStat'  

Now, let's drop the table, then see how attempting to query the table, view, or function behaves.

DROP TABLE [dbo].[employees];
GO
 
SELECT * FROM [dbo].[employees]; 
SELECT * FROM [dbo].[vEmployees]; 
EXECUTE [dbo].[uspNumOfEmp]; 
SELECT dbo.NumOfEmp();
GO 

We see they all fail.

Recreate and populate the table again.

-- create table
CREATE TABLE [dbo].[employees] (
[LastName] [varchar](100) NOT NULL,
[FirstName] [varchar](100) NOT NULL,
[Title] [varchar](50) NULL,
[HireDate] [date] NULL,
[TerminationDate] [date] NULL,
[RehireDate] [date] NULL,
[Address] [varchar](100) NULL,
[City] [varchar](100) NULL,
[Region] [varchar](100) NULL,
[PostalCode] [varchar](100) NULL);
GO
 
-- populate table
INSERT INTO [dbo].[Employees]
VALUES('Jones', 'Robert', 'Accountant', '2010-07-01', NULL, NULL, '1 Main St', 'Anywhere', 'MA', '12345'), 
      ('Forth', 'Sally', 'Director', '2010-07-15', NULL, NULL, '27 Elm St', 'Somewhere', 'NH', '23456');
GO

SELECT * FROM [dbo].[employees]; 
SELECT * FROM [dbo].[vEmployees]; 
EXECUTE [dbo].[uspNumOfEmp]; 
SELECT dbo.NumOfEmp();
GO

And querying the table, view, or function behaves normally again.

But here we'll see the trigger, index, and statistic are no longer there and have to be recreated.

SELECT name AS [Trigger]   FROM sys.triggers   WHERE name LIKE 'tr_employees'
SELECT name AS [Index]     FROM sys.indexes    WHERE name LIKE 'idx_Title'
SELECT name AS [Statistic] FROM sys.stats      WHERE name LIKE 'MyStat'  

-- create trigger
CREATE TRIGGER [dbo].tr_employees 
   ON  [dbo].[employees]
   AFTER INSERT
AS 
BEGIN
   SELECT * FROM inserted
END
GO
-- create index
CREATE CLUSTERED INDEX [idx_Title] ON [dbo].[employees]
(
   [Title] 
);
GO
-- create statistic
CREATE STATISTICS [MyStat] ON [dbo].[employees]([LastName])
GO
 
SELECT name AS [Trigger]     FROM sys.triggers WHERE name LIKE 'tr_employees';
SELECT name AS [Index]     FROM sys.indexes    WHERE name LIKE 'idx_Title';
SELECT name AS [Statistic] FROM sys.stats      WHERE name LIKE 'MyStat';
GO 

Above we can see they are now here again.

To summarize, the view, stored procedure and function that query the table, but are not directly tied to it remain but stop functioning after the table is dropped. And the trigger, index, and statistic that are built on the table are dropped along with the table.