SQL Server: Get Foreign Key Name by Table and Column

I ran across a scenario where I was trying to drop a column that was a foreign key.  When I executed the code to drop it it said there was a dependent object on the column being dropped.  The dependent object was the FK constraint.  So, I need a programatic way of getting the FK constraint name so it could run in our migrations.



The script came from this SO thread.

Sql Server 2008: Dropping Columns with Default Objects

Sql Server 2008 (and I think 2005) they use default objects when you create a column with a default, i.e. alter table MY_TABLE ADD MY_COLUMN varchar(20) default 'HEY' will create an object that specifies the default value.

Generally I couldn't care less about how they handle their default values, but when you try running this alter table MY_TABLE drop column MY_COLUMN you'll get an error along the lines of object DF__MY_TABLE___My_COL__4046A860 depends on that column. See here for more information on Defaults.

The names are created dynamically, unless you specify one of course, so if you're trying to drop a column programatically it can be a problem. Luckily, a few geniuses have come before and have left us some code on how to find and drop these constraints without knowing the name. Those can be found here and here.

These are great but if you have a lot of columns to drop it turns into a lot of code, so here is a stored procedure that can be used to drop the default objects. You are then free to drop the columns as you wish.

SQL Server: Some Useful Schema Scripts

Here are some helpful scripts that help me to find out more about the table's I'm working on.

Getting more information about a table.  This stored procedure returns the table's columns, primary key, foregin keys, contrstraints, which tables reference this one, and more.


Getting the SQL for a view (or a trigger or stored procedure). Here is another stored procedure that fetches the create statement for various objects.

Getting the triggers on a table. If you ever need to know what triggers are executing on a table this one is very helpful, especially when also used with the query to get the SQL for a trigger above.

Getting the triggers on a table II. Here is another way to get the triggers which seems to work better.

Getting a table's columns. While this does basically repeat the functionality of the first query, it does introduce INFORMATION_SCHEMA, which contains lots of information about your schema in general.
Getting a table's indexes

And also while I'm listing some useful things for SQL Server, here are a couple links for some useful sql formatters.

• Instant SQL Formatter is the one I use the most
• TSQL Code Formatter is also pretty handy, although the query needs to compile for it to format it (so no parameters)

And here some more more useful SQL Server tools from SO.

SQL Server: Find a Trigger By The Table Name

Given a table name, this is how to find the triggers that are on that table.

select name, object_name(parent_id) as parent from sys.triggers where object_name(parent_id) = 'MY_TABLE_NAME'

And next time you want to use a trigger in your webapp...don't.

SQL Server 2008- Unique Value on One Record

In the database schema I'm working on we need one record to have a unique value (in our case 'Y'), while the other records need a non unique value ('N') i.e.  only one record can have 'Y'.

To do that we can use an index like this: