DataWriter

April 12, 2011

T-SQL Tuesday #17–APPLY

Filed under: SQLServerPedia Syndication,T-SQL — Joe Casella @ 9:30 pm
Tags: ,

TsqlLogoI use APPLY with XML to pivot data into a single field to solve a range of problems. One of my favorites is providing single-row column lists from the various “columns” DMVs. In two earlier posts, I showed how to query DMVs to produce a list of the columns in an index or table statistics.

This month’s T-SQL Tuesday, focused on the APPLY operator, is a great time to extend the series with two more queries: listing Primary Keys/Unique Constraints and Foreign Keys.

The list of Primary Keys and Unique Constraints can be built from the previous query of sys.indexes by adding a WHERE clause that limits it to keys and constraints:

  1. SELECT Table_Name = SCHEMA_NAME(st.schema_id) + '.' + OBJECT_NAME(st.Object_id)
  2.     , Constraint_Name = si.name
  3.     , Constraint_Columns = LEFT(ixColumns, LEN(ixColumns) 1)
  4.     , Constraint_Type = CASE WHEN si.is_primary_key = 1 then 'PK'
  5.                              WHEN si.is_unique_constraint = 1 THEN 'UQ'
  6.                              ELSE '' END
  7.   FROM sys.tables st
  8.   JOIN sys.indexes si
  9.     ON st.object_id = si.object_id
  10. CROSS APPLY (
  11.             SELECT sc.Name + ', '
  12.               FROM sys.index_columns ic
  13.               JOIN sys.columns sc
  14.                 ON ic.object_id = sc.object_id
  15.                AND ic.column_id = sc.column_id
  16.              WHERE si.object_id = ic.object_id
  17.                AND si.index_id = ic.index_id
  18.              ORDER BY ic.Key_Ordinal
  19.               FOR XML PATH('') ) ix (ixColumns)
  20. WHERE si.is_primary_key = 1 or si.is_unique_constraint = 1

Here’s a partial result of executing the query against AdventureWorks2008r2

image

Listing Foreign Key Constraints columns involves building a column list from both tables:

  1. SELECT Constraint_Name = fk.name
  2.      , Parent_Table = SCHEMA_NAME(st1.schema_id) +'.' + st1.name
  3.      , Referenced_Table = SCHEMA_NAME(st2.schema_id) +'.' + st2.name
  4.      , Parent_Columns  = LEFT(ParentColumns, LEN(ParentColumns) 1)
  5.      , Referenced_Columns  = LEFT(ReferenceColumns, LEN(ReferenceColumns) 1)
  6.      , On_Delete = fk.delete_referential_action_desc
  7.      , On_Update = fk.update_referential_action_desc
  8.   FROM sys.foreign_keys fk
  9.   JOIN sys.tables st1
  10.     ON fk.parent_object_id = st1.object_id
  11.   JOIN sys.tables st2
  12.     ON fk.referenced_object_id = st2.object_id  
  13. CROSS APPLY (
  14.             SELECT sc.Name + ', '
  15.               FROM sys.foreign_key_columns kc
  16.               JOIN sys.columns sc
  17.                 on kc.parent_object_id = sc.object_id
  18.                AND kc.parent_column_id = sc.column_id
  19.              WHERE fk.object_id = kc.constraint_object_id
  20.              ORDER BY kc.constraint_column_id
  21.               FOR XML PATH('') ) pc (ParentColumns)
  22. CROSS APPLY (
  23.             SELECT s2.Name + ', '
  24.               FROM sys.foreign_key_columns k2
  25.               JOIN sys.columns s2
  26.                 on k2.referenced_object_id = s2.object_id
  27.                AND k2.referenced_column_id = s2.column_id
  28.              WHERE fk.object_id = k2.constraint_object_id
  29.              ORDER BY k2.constraint_column_id
  30.               FOR XML PATH('') ) rc (ReferenceColumns)

Sample results, also from AdventureWorks2008r2:

image

To really capture the spirit of this month’s topic, any these column scripts could be changed into table-valued functions and APPLY-ed to a query of system objects. But there’s less than an hour until this month’s posting deadline so I must leave that, as they say, “as an exercise for the reader.”

Advertisements

1 Comment »

  1. […] Joe Casella ( Blog ) shows how to list Primary Keys/Unique Constraints and Foreign Keys using APPLY and XML. […]

    Pingback by T-SQL Tuesday #17 Roundup - APPLY Knowledge | Matt Velic — April 18, 2011 @ 1:06 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: