Declarative Referential Integrity - Show Column References

Applicability:

                 SQL Server 2000:        Not Supported
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:     William Talada
Date:        27 Sep 2010

Description

This is an impressive piece of code that lists one or all tables in a database with all columns in a grid.
Each column is annotated with its usage in PK, FK and AK constraints plus INDEX usage.
Rather than try and explain the output, a sample is provided in the table below which should hopefully suffice:

TableName

ColumnName

Constraints

Indexes

Foreign Keys

DimCustomer

 

 

 

 

 

CustomerKey

 pk1

 

 

 

GeographyKey

 

 idx3

 fk1

 

CustomerAlternateKey

 ak2

 

 

 

Title

 

 

 

FactInternetSalesReason

 

 

 

 

 

SalesOrderNumber

 ak2.1

 

 fk2.1

 

SalesOrderLineNumber

 ak2.2

 

 fk2.2

 

SalesReasonKey

 ak2.3

 

 fk1


The code copes well with multi-key constraints.
The constraint numbering is the order in which it is stored in the system tables.
With indexes, IDX1 is always the clustered index, with all other index numbers being non-clustered indexes.

Originally this code was just a TSQL query, but I have parameterised for ease of use.  

NOTE:  The output of this SP is really intended for reference and to aid in gaining an understanding of a DB structure.
There is a Table Valued Function (udf_GetForeignKeyColumns) that provides a slightly more detailed output for FK references and can be readily consumed by dynamic SQL.

Code

Stored Procedure:


DROP PROCEDURE [dbo].[usp_ShowColumnUsage]
GO


CREATE PROCEDURE [dbo].[usp_ShowColumnUsage] @TableNameLike VARCHAR(128) = NULL
AS

/*****************************************************************
Purpose:   To print a quick indeted report for one or many tables
           (table name can be supplied)
           that indicates which columns are used in PKs, FKs, AKs or INDEXES          
          
           A fairly unusual way of presenting the data, but very effective
           when exmainng a structure for the first time
          
               PK = Primary Key
               FK = Foreign Key
               AK = Alternate Key
               IDX = Non-Unique Index      -- any index #1 is clustered
               UDX = Unique Index          -- any index #1 is clustered

Author:    William Talada

History:   27 Sep 2010 - Intial Issue
           3 Jul 2012 - ChillyDBA - Converted to an SP and changed
               to ensure correct handling and display of schema names

******************************************************************/


IF @TableNameLike IS NULL
BEGIN
   PRINT
'This stored procedure shows which columns of a table participate'
  
PRINT 'in primary key constraints, unique constraints (alternate keys),'
  
PRINT 'unique indexes, regular indexes, and foreign keys.'
  
PRINT 'Any constraint or index numbered 1 is clustered.'
  
PRINT '  exec ShowColumnUsage ''Ac%'''
  
RETURN 0
END

SET NOCOUNT ON

-- List all tables and columns with their constraint columns pk, aks, fks, idxs
DECLARE
  
@loop       INT,
  
@loopmax    INT

-- get list of tables
DECLARE @tables TABLE
  
(
      
TableSchema     VARCHAR(100),
      
TableName       VARCHAR(100),
      
TableId         INT
  
)

DECLARE @Cols TABLE
  
(
      
TableId         INT,
      
ColumnId        INT,
      
ColumnName      VARCHAR(100),
      
Constraints     VARCHAR(100),
      
Indexes         VARCHAR(100),
      
ForeignKeys     VARCHAR(100))

DECLARE @fks TABLE
  
(
      
TableId         INT,
      
FkId            INT,
      
FkNbr           INT,
      
FkColCnt        INT
  
)

DECLARE @pks TABLE
  
(
      
TableId         INT,
      
PkId            INT,
      
PkNbr           INT,
      
PkColCnt        INT
  
)

DECLARE @aks TABLE
  
(
      
TableId         INT,
      
AkId            INT,
      
AkNbr           INT,
      
AkColCnt        INT
  
)
  
DECLARE @udxs TABLE
  
(
      
TableId         INT,
      
UdxId           INT,
      
UdxNbr          INT,
      
UdxColCnt       INT
  
)
  
DECLARE @idxs TABLE
  
(
      
TableId         INT,
      
IdxId           INT,
      
IdxNbr          INT,
      
IdxColCnt       INT
  
)


INSERT INTO @tables
SELECT
  
SCHEMA_NAME(t.schema_id),
  
t.name,
  
t.OBJECT_ID
FROM
  
sys.tables t
WHERE
  
t.name LIKE @TableNameLike


-- get list of cols
INSERT INTO @Cols
SELECT
  
t.TableId,
  
c.column_id,
  
c.name,
  
'',
  
'',
  
''
FROM
  
@tables t
INNER JOIN sys.columns c
  
ON t.Tableid=c.OBJECT_ID

-- get list of fk tables
INSERT INTO @fks
SELECT
  
parent_object_id,
  
OBJECT_ID,
  
0,
   (
      
SELECT MAX(constraint_column_id)
      
FROM sys.foreign_key_columns fkc
      
WHERE fk.OBJECT_ID = fkc.constraint_object_id
  
)
FROM
  
sys.foreign_keys fk
INNER JOIN @tables c
  
ON fk.parent_object_id = c.TableId


-- number the fks
SET @loop = 0
WHILE @@ROWCOUNT > 0
BEGIN
   SET
@loop = @loop + 1

  
UPDATE fks
      
SET FkNbr = @loop
  
FROM @fks fks
  
WHERE fks.FkNbr = 0
  
AND fks.FkId IN
      
(
          
SELECT  MIN(FkId)
          
FROM @fks
          
WHERE FkNbr = 0
          
GROUP BY TableId
      
)
END


-- get pks
INSERT INTO @pks
SELECT
  
i.OBJECT_ID,
  
i.index_id,
  
i.index_id,
   (
      
SELECT MAX(key_ordinal)
      
FROM sys.index_columns ic
      
WHERE i.OBJECT_ID = ic.OBJECT_ID
      
AND i.index_id = ic.index_id
  
)
FROM sys.indexes i
INNER JOIN @tables c
  
ON i.OBJECT_ID = c.TableId
WHERE i.is_primary_key = 1


-- get aks
INSERT INTO @aks
SELECT
  
i.OBJECT_ID,
  
i.index_id,
  
i.index_id,
   (
      
SELECT MAX(key_ordinal)
      
FROM sys.index_columns ic
      
WHERE i.OBJECT_ID = ic.OBJECT_ID
      
AND i.index_id = ic.index_id
  
)
FROM sys.indexes i
INNER JOIN @tables c
  
ON i.OBJECT_ID = c.TableId
WHERE i.is_unique_constraint = 1


-- get udxs
INSERT INTO  @udxs
SELECT
  
i.OBJECT_ID,
  
i.inddex_id,
  
i.index_id,
   (
      
SELECT MAX(key_ordinal)
      
FROM sys.index_columns ic
      
WHERE i.OBJECT_ID = ic.OBJECT_ID
      
AND i.index_id = ic.index_id
  
)
FROM sys.indexes i
INNER JOIN @tables c
  
ON i.OBJECT_ID = c.TableId
WHERE i.is_unique_constraint = 0
AND i.is_primary_key = 0
AND i.is_unique = 1


-- get idxs
INSERT INTO @idxs
SELECT
  
i.OBJECT_ID,
  
i.index_id,
  
i.index_id,
   (
      
SELECT MAX(index_column_id)
      
FROM sys.index_columns ic
      
WHERE i.OBJECT_ID = ic.OBJECT_ID
      
AND i.index_id = ic.index_id
  
)
FROM sys.indexes i
INNER JOIN @tables c
  
ON i.OBJECT_ID = c.TableId
WHERE i.is_unique_constraint = 0
AND i.is_primary_key = 0
AND i.is_unique = 0


-- annotate the PKs in the master table
SELECT
  
@loopmax = MAX(PkNbr)
FROM @pks

SET @loop=0

WHILE @loop <= @loopmax
BEGIN
   UPDATE
c
  
SET
      
Constraints = Constraints
      
+ ' pk' + CASE p.PkColCnt
                      
WHEN 1 THEN CAST(p.PkNbr AS VARCHAR(10))
                      
ELSE CAST(p.PkNbr AS VARCHAR(10))+'.'+CAST(ic.index_column_id AS VARCHAR(10))
                  
END
   FROM
@cols c
  
INNER JOIN @pks p
      
ON c.TableId = p.TableId
  
INNER JOIN sys.index_columns ic
      
ON p.TableId = ic.OBJECT_ID
      
AND p.PkId = ic.index_id
      
AND c.ColumnId = ic.column_id
  
WHERE  p.PkNbr = @loop

  
SET @loop = @loop + 1
END



-- annotate the AKs in the master table
SELECT @loopmax = MAX(AkNbr)
FROM @aks

SET @loop=0

WHILE @loop <= @loopmax
BEGIN
   UPDATE
c
  
SET
      
Constraints = Constraints
      
+ ' ak' + CASE p.AkColCnt
                      
WHEN 1 THEN CAST(p.AkNbr AS VARCHAR(10))
                      
ELSE CAST(p.AkNbr AS VARCHAR(10))+'.'+CAST(ic.index_column_id AS VARCHAR(10))
                  
END
   FROM
@cols c
  
INNER JOIN @aks p
      
ON c.TableId = p.TableId
  
INNER JOIN sys.index_columns ic
      
ON p.TableId = ic.OBJECT_ID
      
AND p.AkId = ic.index_id
      
AND c.ColumnId = ic.column_id
  
WHERE p.AkNbr = @loop

  
SET @loop = @loop + 1
END


-- annotate the UDXs in the master table

SELECT @loopmax = MAX(UdxNbr)
FROM @udxs

SET @loop=0

WHILE @loop <= @loopmax
BEGIN
   UPDATE
c
  
SET
      
Indexes = Indexes
      
+ ' udx' +    CASE p.UdxColCnt
                      
WHEN 1 THEN CAST(p.UdxNbr AS VARCHAR(10))
                      
ELSE CAST(p.UdxNbr AS VARCHAR(10))+'.'+CAST(ic.index_column_id AS VARCHAR(10))
                  
END
   FROM
@cols c
  
INNER JOIN @udxs p
      
ON c.TableId = p.TableId
  
INNER JOIN sys.index_columns ic
          
ON p.TableId = ic.OBJECT_ID
          
AND p.UdxId = ic.index_id
          
AND c.ColumnId = ic.column_id
  
WHERE p.UdxNbr = @loop

  
SET @loop = @loop + 1
END


-- annotate the IDXs in the master table
SELECT @loopmax = MAX(IdxNbr)
FROM @idxs

SET @loop=0

WHILE @loop <= @loopmax
BEGIN
   UPDATE
c
  
SET
      
Indexes = Indexes
      
+ ' idx' +    CASE p.IdxColCnt
                      
WHEN 1 THEN CAST(p.IdxNbr AS VARCHAR(10))
                      
ELSE CAST(p.IdxNbr AS VARCHAR(10))+'.'+CAST(ic.index_column_id AS VARCHAR(10))
                  
END
      
+ CASE ic.is_included_column
          
WHEN 1 THEN '+'
          
ELSE ''
        
END
   FROM
@cols c
  
INNER JOIN @idxs p
      
ON c.TableId = p.TableId
  
INNER JOIN sys.index_columns ic
      
ON p.TableId = ic.OBJECT_ID
      
AND p.IdxId = ic.index_id
      
AND c.ColumnId = ic.column_id
  
WHERE p.IdxNbr = @loop

  
SET @loop = @loop + 1
END


-- annotate the FKs in the master table
SELECT @loopmax = MAX(FkNbr)
FROM @fks

SET @loop=0

WHILE @loop <= @loopmax
BEGIN
   UPDATE
c
  
SET
      
ForeignKeys = ForeignKeys
      
+ ' fk' + CASE p.FkColCnt
                      
WHEN 1 THEN CAST(p.FkNbr AS VARCHAR(10))
                      
ELSE CAST(p.FkNbr AS VARCHAR(10))+'.'+CAST(ic.constraint_column_id AS VARCHAR(10))
                  
END
   FROM
@cols c
  
INNER JOIN @fks p
      
ON c.TableId = p.TableId
  
INNER JOIN sys.foreign_key_columns ic
      
ON p.FkId = ic.constraint_object_id
      
AND p.TableId = c.TableId
      
AND c.ColumnId = ic.parent_column_id
  
WHERE p.FkNbr = @loop

  
SET @loop = @loop + 1
END


-- now return the final results
SELECT
  
x.Heading,
  
x.ColumnName,
  
x.Constraints,
  
x.Indexes,
  
x.ForeignKeys
FROM
  
(
      
SELECT
          
'' AS Heading,
          
t.TableName,
          
c.ColumnId,
          
c.ColumnName,
          
c.Constraints,
          
c.Indexes,
          
c.ForeignKeys
      
FROM @Tables t
      
INNER JOIN @Cols c
          
ON t.TableId = c.TableId

      
UNION

       SELECT
          
t.tableName,
          
t.tableName,
          
0,
          
'',
          
'',
          
'',
          
''
      
FROM @Tables t
      
INNER JOIN @Cols c
          
ON t.TableId = c.TableId
  
) AS x
ORDER BY
  
x.TableName,
  
x.ColumnId

RETURN 0
GO


Test Code:

 

USE AdventureWorksDW

EXEC ShowColumnUsage 'DimAccount%'

EXEC ShowColumnUsage '%'


ċ
usp_ShowColumnUsage.sql
(8k)
Andy Hughes,
Jul 4, 2012, 6:04 AM
Comments