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