Show Column References

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