Object Dependency and Dropped Tables

STAY IN TOUCH

Get New posts delivered straight to your inbox

  • Madeira Team

Object Dependency and Dropped Tables

Here’s what is going to happen today: we’re going to bring a bunch of objects to life, and then we’re going to kill them.

Woah, why all the violence?  In our databases, we have lots of objects.  New ones are created all the time, then changed, and sometimes deleted.  These objects are connected to one another!  What happens when an object is removed from the database, but other objects depend on it?  Let’s see.

A basic object is a table.  Create two to use as a foundation of the experiment. Here, each table is created with a child object: a key.

Transact-SQL

CREATE TABLE dbo.ItLives ( ID INT IDENTITY , Value NVARCHAR(50) CONSTRAINT [PK_ItLives] PRIMARY KEY (ID ASC) ) ; CREATE TABLE dbo.Alive ( ID INT , Value NVARCHAR(50) CONSTRAINT [PK_Alive] PRIMARY KEY (ID ASC) ) ; GO

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

CREATE TABLE dbo.ItLives

(

ID INT IDENTITY ,

Value NVARCHAR(50)

CONSTRAINT [PK_ItLives] PRIMARY KEY

(ID ASC)

) ;

CREATE TABLE dbo.Alive

(

ID INT ,

Value NVARCHAR(50)

CONSTRAINT [PK_Alive] PRIMARY KEY

(ID ASC)

) ;

GO

Next up, create a few more objects that are based on these two tables.

We’ll start with a foreign key to connect the ID columns of both the tables.

Then there’s a trigger to alert a user that maybe it would be best not to change the data in one of our brand-new tables. Finally, a view that displays rows from both the tables together.

Transact-SQL

-- Create a foreign key ALTER TABLE dbo.ItLives ADD CONSTRAINT FK_AliveID_ItLivesID FOREIGN KEY (ID) REFERENCES dbo.Alive (ID) ; GO -- Create a trigger CREATE TRIGGER DoNotTouchThisTable ON dbo.ItLives AFTER INSERT, UPDATE, DELETE AS PRINT 'Please do not touch this table' ; GO -- Create a view CREATE VIEW dbo.ViewTheLiving AS SELECT TOP (10) ItLives.ID FROM dbo.ItLives INNER JOIN dbo.Alive ON ItLives.ID = Alive.ID WHERE ItLives.ID < 10 ; GO

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

-- Create a foreign key

ALTER TABLE dbo.ItLives

ADD CONSTRAINT FK_AliveID_ItLivesID FOREIGN KEY (ID)

REFERENCES dbo.Alive (ID) ;

GO

-- Create a trigger

CREATE TRIGGER DoNotTouchThisTable

ON dbo.ItLives

AFTER INSERT, UPDATE, DELETE

AS PRINT 'Please do not touch this table' ;

GO

-- Create a view

CREATE VIEW dbo.ViewTheLiving

AS

SELECT TOP (10) ItLives.ID

FROM dbo.ItLives

INNER JOIN dbo.Alive

ON ItLives.ID = Alive.ID

WHERE ItLives.ID < 10 ;

GO

To prove that we’re ready to go, that each of these objects really has been created, we’ll query the dynamic management views for each one.  If we get an OBJECT_ID, the object exists.  If the OBJECT_ID is null, then the object does not exist.

Transact-SQL

SELECT OBJECT_ID('PK_ItLives') AS 'ObjectID' UNION ALL SELECT OBJECT_ID('FK_AliveID_ItLivesID') UNION ALL SELECT OBJECT_ID('DoNotTouchThisTable') UNION ALL SELECT OBJECT_ID('ViewTheLiving') UNION ALL SELECT OBJECT_ID('DoesNotExist') ;

1

2

3

4

5

SELECT OBJECT_ID('PK_ItLives') AS 'ObjectID' UNION ALL

SELECT OBJECT_ID('FK_AliveID_ItLivesID') UNION ALL

SELECT OBJECT_ID('DoNotTouchThisTable') UNION ALL

SELECT OBJECT_ID('ViewTheLiving') UNION ALL

SELECT OBJECT_ID('DoesNotExist') ;

The result set from this query is: [table “3” not found /]

The objects we created today – the primary keys, foreign keys, indexes, triggers, and views – all rely on the table ItLives.  What would happen to our innocent objects if this table were to disappear?

Transact-SQL

DROP TABLE dbo.ItLives ;

1

DROP TABLE dbo.ItLives ;

We then run the same query for OBJECT_ID on our objects to see which of them survived the damage.

The result set is now: [table “4” not found /]

The DROP TABLE command implicitly drops from the database any objects that would be meaningless without that table.  However, it does not drop views.  I also tested a view that was composed only of the table that gets dropped, and this view also stays around.  Just in case there’s more than one table in a view, SSMS leaves views alone unless they are dropped explicitly.

Keep this in mind when you’re playing with the life of your objects!

#SSMS

JOIN OUR MAILING LIST

CONTACT US

 3 Rapaport St. Kfar Saba, Israel

 +972-9-7400101

  • Google+ - White Circle
  • Facebook - White Circle
  • Twitter - White Circle
  • LinkedIn - White Circle