Transact SQL Finding Unique Constraints

I know it has been quite a while since I posted to the blog, a topic for another time, but I wanted to put this out here as I have used it three times this week to answer a couple different questions.

The system view sys.key_constraints replaces functionality found in sysconstraints in SQL Server 2000 and earlier versions.  While sysconstraints still exists in SQL Server 2008 it is slated for removal in a future version of SQL Server.  The view stores details for primary and unique keys.

What we are after today is the name, parent schema, parent table, and (most importantly) the column list making up our keys.

Here is the code:

 

SELECT  kc.name AS KeyName
       ,SCHEMA_NAME(kc.[schema_id]) AS ParentSchemaName
       ,OBJECT_NAME(kc.[parent_object_id]) AS ParentObjectName
       ,type_desc AS KeyType
       ,create_date AS KeyCreationDate
       ,STUFF((
                SELECT  ', ' + c.name
                FROM    sys.index_columns AS ic
                        INNER JOIN sys.columns AS c On c.column_id = ic.column_id
                                                       AND c.[object_id] = ic.[object_id]
                WHERE   ic.index_id = kc.unique_index_id
                        AND ic.[object_id] = kc.[parent_object_id]
                ORDER BY ic.key_ordinal
              FOR
                XML PATH('')
              ), 1, 1, '') AS ColumnDefinition
FROM    sys.key_constraints AS kc
WHERE   type = 'UQ'
ORDER BY ParentObjectName ;

As referenced above, I have some changes coming in my focus.  Expect to see more code and less talk out of this blog in the near future.  Enjoy!


Posted by: whitneyw
Posted on: 1/21/2010 at 2:56 PM
Actions: E-mail | Kick it! | DZone it! | del.icio.us
Post Information: Permalink | Comments (0) | Post RSSRSS comment feed

SQL Azure and DMVs

I was in a conversation this morning with a couple other SQL guys (Twitter again) and the subject of Dynamic Management Views (DMVs) came up in regards to SQL Azure.  Running through the MSDN details it did not seem that they were supported.  After a couple emails with the SQL Azure team I have been given assurances that they will be added in a few months. 

The DMVs that will be available are only those which have database scope.  This is another great step in making SQL Azure a very viable alternative to local hardware.


Posted by: whitneyw
Posted on: 12/21/2009 at 4:23 PM
Tags: , ,
Categories: SQL Azure | SQL Server
Actions: E-mail | Kick it! | DZone it! | del.icio.us
Post Information: Permalink | Comments (0) | Post RSSRSS comment feed

OT: Not All Tweeting Is Unproductive

Just had an awesome exchange with two members of the SQL Server community over Twitter.  While many people scratch their heads at what exactly to do with this tool (other than let everyone know they are eating, sitting, or worse.) the SQL community has exploded as of late.

All it took to get the conversation started was a question with a "#sql" tag.

I really enjoy a setting where a skinny geek in Atlanta can be talking with someone in Seattle and another in Slovenia about internals of a product we all make our livelihood from.  Good times indeed...


Posted by: whitneyw
Posted on: 12/3/2009 at 4:06 PM
Tags:
Categories: Other Stuff
Actions: E-mail | Kick it! | DZone it! | del.icio.us
Post Information: Permalink | Comments (0) | Post RSSRSS comment feed

Rocky Lhotka Is Coming To Town!

Sing this to the Bruce Springsteen version replacing Santa Claus...

Rocky will be in the ATL for a number of meetings, starting Tuesday with a Geek Dinner at 5 Season's Brewery in Alpharetta. 

On Wednesday Rocky will be joining us at ALEMUG to talk about Oslo.  This will be a very interesting discussion based on some of the recent developments in trajectory with Oslo.

If you are unfamiliar with Rocky he is the Principal Technology Evangelist at Magenic, a well known author, and is responsible for the CSLA framework that helps people build business objects.

Do yourself a favor and swing by for one (or both) of these events.  Rocky is a great guy and always has an interesting viewpoint of technology.


Posted by: whitneyw
Posted on: 11/30/2009 at 10:52 AM
Tags:
Categories: Oslo | User Groups
Actions: E-mail | Kick it! | DZone it! | del.icio.us
Post Information: Permalink | Comments (0) | Post RSSRSS comment feed

More 64 bit Goodness For Us

My list of items in Program Files (x86) keeps shrinking and thank goodness for that.  The continued drive to 64 bit is joined by SSMS and is available here.  This build also supports SQL Azure if you need additional sales pitch.


Posted by: whitneyw
Posted on: 11/17/2009 at 1:35 PM
Tags:
Categories: SQL Server | SQL Azure
Actions: E-mail | Kick it! | DZone it! | del.icio.us
Post Information: Permalink | Comments (0) | Post RSSRSS comment feed

An OSLO Curveball For Us All

The OSLO story just continues to amaze and perplex me.  I am a bit late with the reference but Doug Purdy posted on the 10th about the next transformation of OSLO, now being called SQL Server Modeling.  What should not be missed are the roughly 60 comments on this post - many of those taking an adversarial feel.

While a part of me enjoys the existence of SQL Server continuing I was really hoping to see some sort of transcendent technology coming our way.  I suppose the PDC conference next week could still have some big "wow" moment but that now seems less and less likely.

I would love to hear from you, dear reader...what do you think about this change in name (and likely direction)?


Posted by: whitneyw
Posted on: 11/13/2009 at 1:52 AM
Tags:
Categories: Oslo | SQL Server
Actions: E-mail | Kick it! | DZone it! | del.icio.us
Post Information: Permalink | Comments (0) | Post RSSRSS comment feed

SQL Azure Gets A Refresh

The SQL Azure Team Blog has an update on some changes in the CTP2.  First and foremost, the product is now feature complete.  Second, if you have been using the October CTP you have now been provisioned over to the go-live production clusters.

Additional Transact-SQL enhancements have been made as well, with Table Value Parameters being the most interesting to me.

PDC 2009 will see lots of discussion about SQL Azure so if you are making the trip to Los Angeles be sure to catch a session.

One more note on SQL Azure, if you are using Visual Studio 2010 beta check out the SQL Azure Explorer on CodePlex.  The add-in gives you the ability to navigate your instance as you would a normal SQL Server instance.  Good stuff!


Posted by: whitneyw
Posted on: 10/15/2009 at 3:10 PM
Tags:
Categories: SQL Server | SQL Azure
Actions: E-mail | Kick it! | DZone it! | del.icio.us
Post Information: Permalink | Comments (0) | Post RSSRSS comment feed

ALEMUG First Meeting

The first meeting of the Atlanta Leading Edge Microsoft User Group (ALEMUG) was a great success.  We had a decent turn out considering the rainy weather (not sure why people don't go to user groups when it rains in ATL, but that is another matter).

We had a couple quick presentations on covariance versus contravariance and two talks on new C# 4.0 features.

A giant thank you to everyone that came out tonight and made the evening such a great time!

HTC Phone Pics 019

Sergey Barskiy rocking the dynamic types

HTC Phone Pics 020

Everyone listening intently

HTC Phone Pics 023

Mike Strobel wows the crowd with code contracts

HTC Phone Pics 022

Jeff Ammons (left) shows off his Red Gate swag winnings while I mug for the camera


Posted by: whitneyw
Posted on: 10/14/2009 at 11:12 PM
Tags:
Categories: User Groups
Actions: E-mail | Kick it! | DZone it! | del.icio.us
Post Information: Permalink | Comments (0) | Post RSSRSS comment feed

I'm back! Sort of...

It has been a little dark here at the blog site due to the arrival of our second daughter.  Things are great and we are starting to get to know the sweet little kid that joined our household.

While I get my data legs back under me check out the new whitepaper written by the highly esteemed Paul Randall on High Availability strategies in SQL Server 2008.

Also, make sure you check our user group at ALEMUG for the coming October meeting.  Should be a great time.


Posted by: whitneyw
Posted on: 9/30/2009 at 4:42 PM
Categories: User Groups | Other Stuff
Actions: E-mail | Kick it! | DZone it! | del.icio.us
Post Information: Permalink | Comments (0) | Post RSSRSS comment feed

Declarative Database Development

If you are doing any project work where you are responsible for moving data objects through multiple environments you will want to check out the post today on Gert Drapers blog.  You can get the slides from his recent presentation at the New England SQL Server Users Group.  Very interesting reading.


Posted by: whitneyw
Posted on: 9/15/2009 at 9:28 AM
Tags:
Categories: SQL Server
Actions: E-mail | Kick it! | DZone it! | del.icio.us
Post Information: Permalink | Comments (0) | Post RSSRSS comment feed