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:

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 '%'