• SQL Prompt
• SQL Code Snippets

SQL Prompt Snippets to Drop Columns and Tables and Handle Associated Dependencies

Dealing with object dependencies

Often, when I am working on managing table structures, I need to drop a column or even a table. Sometimes this is doing development work, or very frequently it is when I am building a demonstration of some SQL Server feature. Of course, such simple-sounding tasks as dropping a column or table are usually not, due to the dependencies (references) between objects. In short, you can’t drop an object if another object depends on it. For example, I attempt to drop a column and face a battery of errors informing me that this can’t be allowed, because a CHECK constraint, a DEFAULT constraint, and an INDEX all depend on it.

So, now I must get the names of the dependent objects and craft statements to drop the constraints and indexes (assuming it’s OK to do this; hopefully if this is more than demo code you have your database in version control in case you delete too much). Finally, you drop the dependencies before eventually getting to complete the ‘simple’ job of dropping the column. Similarly, if I need to drop a table, I may first need to deal with references from FOREIGN KEY constraints, or references from schema-bound views or UDFs, and so on.

Having been bitten by these dependency issues one too many times, I decided I needed a pair of Prompt snippets to help me out. My goal with in writing them wasn’t to just make the tables, and any related code, just vanish, regardless of consequences; these dependency warnings are enabled in the DDL for a reason. My aim was simply to save myself some time in investigating and dealing the common dependencies that routinely pop up:

The dropcolumn snippet, will present the caller with the code to drop the dependencies on the column, along some details of the definition of each. The scripts will indicate schema bound coded objects as well but will not provide the code to just drop views or functions, so these I leave to a task to go check yourself.

The droptable snippet, will assist dropping a table, giving you tools to drop FOREIGN KEY constraints that reference the table, warn of schema binding, turn off temporal capabilities and for a temporal table, assist with dropping the temporal history table as well.

The beauty of having snippets to help with these tasks is that they make large batches of code ‘portable’. If SQL Prompt is installed then I have the code and can access it simply by typing in the name of the snippet in a query pane.

A Demo schema with dependencies

To demonstrate the dependency difficulties that the snippets will help you deal with, I will create a Demo schema and a set of objects that are inter-dependent, for various reasons, such as references by keys, indexes and constraints, as well as UDFs or views (the latter in this case) created WITH SCHEMABINDING.

You can explore the dependencies that exist between these tables and views in various ways, such as by using “View Dependencies” in SSMS Object Explorer, or even graphically if you have a tool like SQL Dependency Tracker.

However, if you need all the detailed dependencies (which keys and constraints reference which columns) you’ll need to construct a query that gets the details from the sys.sql_expression_dependencies catalog view and many others, which is what my snippets do for you.

The dropcolumn snippet

As the first example, let’s say we’ve decided that we no longer need the column Name. So, we want to drop it. We type in the basic ALTER TABLE statement:

And, of course, this is the result:

Msg 5074, Level 16, State 1, Line 60
The object 'CHKColumnDependency_NameNotBlank' is dependent on column 'Name'.
Msg 5074, Level 16, State 1, Line 60
The object 'AKCOlumnDependency' is dependent on column 'Name'.
Msg 4922, Level 16, State 9, Line 60
ALTER TABLE DROP COLUMN Name failed because one or more objects access this column.

Therefore, I created the dropcolumn snippet that will both identify these dependencies automatically, and generate the code to drop the referencing constraints, so that I can drop the column.

It is a very long snippet, so I’m not going to show it in full, but you can download the code to create it at the bottom of this article, and it is well documented, with comments on what each block is designed to do:

• Within a TRY block it executes the required ALTER TABLE command, with placeholders for the scheme, table and column names
• If it fails, then within a CATCH block it constructs ALTER TABLE statements that will drop referencing constraints, which it identifies using various system catalog views
• Finally, within a THROW block, it returns the errors that caused the command to fail (as reported above).

Simply open the snippet code in SSMS, highlight it all and use Create Snippet from the right-click context menu. I called the snippet dropcolumn, with a definition of "LD-Drop column, providing code to identify and usually remove dependencies if they exist".

Once it’s saved, I can access any snippet anywhere I have SQL Prompt available and a directory with my snippets. You could easily change this to a SQLCMD method of parameter replacing, as well, for cases where you do not have SQL Prompt handy.

Now, to execute the snippet, I start to type the name of the snippet, dropcolumn, in a query pane, and invoke it from the list.

This outputs the code that, when executed, will identify the dependency errors, and generate the DDL to remove the referencing constraints. First, though, you’ll be prompted to enter values for the three schema, table, and column placeholders (in this case, Demo, then ColumnDependency then Name).

With this done, the script looks like this; an excerpt only as, again, it’s long and longer still formatted for this article. The idea is that the TRY block tries to execute the statement, and if it fails, a query is executed that build statements for any related CHECK, DEFAULT, or UNIQUE constraints, or any INDEX. It also outputs any schema-bound references for you to go check out.

Execute the script with the details now filled in, and in the Messages output you’ll see are:

(2 rows affected)
Assistance for dropping this column is output in a result set. The error message may have more details
that you have to work through. This script is not guaranteed to cover all situations and scenarios
and review the code before executing it. No guarantee is provided of the safety of this code.
 
Msg 5074, Level 16, State 1, Line 3
The object 'CHKColumnDependency_NameNotBlank' is dependent on column 'Name'.
Msg 5074, Level 16, State 1, Line 3
The object 'AKCOlumnDependency' is dependent on column 'Name'.
Msg 4922, Level 16, State 9, Line 3
ALTER TABLE DROP COLUMN Name failed because one or more objects access this column.

The Results include the statements required to drop the constraints that reference Name. Note that all of the column names are surrounded by the dreaded square brackets because I used QUOTENAME in the script I created. When you write tools, it is a requirement to do this because not everyone uses columns that can be scripted without square brackets:

Now, execute these statements to drop the constraints, therefore allowing you to drop the Name column.

For another example, try using the snippet to drop the column IndexedColumn in Demo.ColumnDependency. Here are the error messages (truncated for brevity):

Msg 5074, Level 16, State 1, Line 325
The object 'CHKColumnDependency_IndexedColAndRowCreateTime' is dependent on column 'IndexedColumn'.
Msg 5074, Level 16, State 1, Line 325
The index 'IndexedColumn' is dependent on column 'IndexedColumn'.
Msg 4922, Level 16, State 9, Line 325
ALTER TABLE DROP COLUMN IndexedColumn failed because one or more objects access this column.

And here are the results:

Execute the ALTER TABLE and the DROP INDEX statements, and you can now drop the column too.

The droptable snippet

Dropping a table is a great deal easier, because it isn’t necessary to get rid of all the constraints and indexes that reference columns first, but it does have its own set of issues with dependencies on the table itself.

For example, if we attempted to drop the Demo.ColumnDependency table, we’d be thwarted by the schema-bound references from the two views. What makes this even more annoying in a way is that unlike the column example, attempting to drop the Demo.ColumnDependency table will only give you one error message about one dependency, leaving you to keep trying until you removed all of them, one by one.

Similarly, a FOREIGN KEY reference to the Demo.ParentDependency table prevents us dropping it:

This is the error that will be output:

Msg 3726, Level 16, State 1, Line 208
Could not drop object 'Demo.ParentDependency' because it is referenced by a FOREIGN KEY constraint.

To make this process easier, I wrote the droptable snippet that checks for dependencies and gives you the code to remove them where it’s safe to do so. It also warns of schema-binding, and handles dropping temporal tables.

As before, the code is long and well-documented, and you can download it at the bottom of the article. Use it to create the droptable snippet, with a description like, "LD-drop table command, enhanced to help you with foreign keys, schemabound objects, and temporal tables".

Invoke the snippet, fill in the schema and table placeholders (this time with Demo and ParentDependency) and execute the resulting script. The errors reported are:

Msg 3726, Level 16, State 1, Line 438
Could not drop object 'Demo.ParentDependency' because it is referenced by a FOREIGN KEY constraint.

And the result of this batch is:

Just like before, drop the constraint using the ALTER TABLE statement, and then you can drop the table, if you desire.

Finally, let’s define a simple temporal table, then try to drop it. Define the table like this, still in the tempdb database:

Temporal tables are odd, in that they don’t provide you an obvious path to drop them. Try to drop this table using the normal syntax:

The following less than helpful message will be output:

Msg 13552, Level 16, State 1, Line 541
Drop table operation failed on table 'tempdb.Demo.Temporal' because it is not a supported operation on system-versioned temporal tables.

A bit of a head scratcher, because this message just said that dropping this table is not supported. Of course, what it actually means is that we have to disable the temporal extensions before dropping the table, so that is what the snippet does for you.

Use the droptable snippet once again, providing Demo and Temporal as parameters for the schema and table names, and then execute the resulting code. This will be the output in the Messages pane (truncated slightly for brevity):

Execute the ALTER TABLE and DROP TABLE statements that are in the output, and then you can drop the table without issue.

Summary

Dropping columns and tables isn’t a task that one probably does every day, but when the time comes, the two snippets I presented in this article will help you deal quickly with the most common dependencies that you will run into (especially DEFAULT, CHECK and FOREIGN KEY constraints.)

These snippets produce the code for most of the common issues you will encounter, while still showing you the actual error that is output in case we haven’t covered all of the possible reasons why the table could not be dropped (like perhaps if Microsoft adds something new to the product and you are still using the snippet in a later version of SQL Server.)

Hopefully, in addition this can be an inspiration for other types of complex snippets you can build to handle annoying repetitive tasks you semi-regularly encounter.

Useful References

• “It_Depends” dependency detector by Phil Factor
• SQL Prompt code snippet documentation
• Other custom SQL Prompt code snippets

Tools in this post

SQL Prompt

Write, format, and refactor SQL effortlessly

Find out more