Discovering Composite Keys

John Morehouse shares some good information on composite keys, including a few scripts:

As I started to work on this, my first thought was that it would be helpful to know how many tables had a composite primary key.  This would give me an idea on how many tables I was dealing with.  Thankfully, SQL Server has this information by using system DMVs (dynamic management views) along with the COL_NAME function.

Note: the COL_NAME function will only work with SQL Server 2008 and newer.  

All of this time, I’d never known about COL_NAME.

Related Posts

Changing Constraints in Near-Zero Downtime Situations

I have part six of my interminable series on near-zero downtime deployments: The locking story is not the same as with the primary and unique key constraints. First, there’s one extra piece: the transition will block access to dbo.LookupTable as well as the table we create the constraint on. That’s to keep us from deleting rows in […]

Read More

Implicit Parent Reference On Foreign Keys

Deborah Melkin shows us an interesting way of creating foreign keys: No matter how long you work with something, you can always find something that you never knew before. I found one about foreign keys this week. I was reviewing SQL scripts for coworkers and I noticed that the foreign keys were written without referencing […]

Read More

Categories

February 2018
MTWTFSS
« Jan Mar »
 1234
567891011
12131415161718
19202122232425
262728