В каких объектах используются колонки таблицы

-- =============================================================================
-- Title: SQL Server 2005 Column Usage
-- Author: Bret Stateham
-- bret@pingit.biz
-- Created: 08/01/07
-- Description: Sample script to show table column usage by server objects
-- =============================================================================
SET NOCOUNT ON

IF OBJECT_ID('TempDB..#Dependencies') IS NOT NULL
DROP TABLE #Dependencies

CREATE TABLE #Dependencies
(
ReferencingObject nvarchar(256),
ReferencingColumn nvarchar(256),
ReferencedObject nvarchar(256),
ReferencedColumn nvarchar(256),
Usage nchar(256)
)

INSERT INTO #Dependencies (ReferencingObject,ReferencingColumn,ReferencedObject,ReferencedColumn,Usage)
SELECT
object_name(object_id) AS ReferencingObject
,IsNull
(
(
SELECT name
FROM sys.columns AS c
WHERE c.object_id = d.object_id
AND c.column_id = d.column_id
)
,''
) AS ReferencingColumn
,OBJECT_NAME(referenced_major_id) AS ReferencedObject
,ISNULL
(
(
SELECT name
FROM sys.columns AS c
WHERE c.object_id = d.referenced_major_id
AND c.column_id = d.referenced_minor_id
)
,''
) AS ReferencedColumn
,CASE
WHEN is_selected = 1 and is_updated = 1 THEN 'SU'
WHEN is_selected = 1 and is_updated = 0 THEN 'S'
WHEN is_selected = 0 and is_updated = 1 THEN 'U'
WHEN is_selected = 0 and is_updated = 0 THEN ''
END AS Usage
FROM sys.sql_dependencies AS d

DECLARE @PivotStatement nvarchar(max)
DECLARE @PivotColumns nvarchar(max)
DECLARE @SelectColumns nvarchar(max)

SET @PivotStatement = ''
SET @PivotColumns = NULL
SET @SelectColumns = ''

SELECT @PivotColumns =
COALESCE(@PivotColumns + ',[' + Referencing + ']','[' + Referencing + ']')
FROM
(
SELECT DISTINCT
CASE
WHEN ReferencingColumn <> '' THEN
ReferencingObject + '.' + ReferencingColumn
ELSE
ReferencingObject
END AS Referencing
FROM #Dependencies
) AS DistinctReferencing

SELECT
@SelectColumns = ISNULL(@SelectColumns,'') +
' ,ISNULL([' + Referencing + '],'''') AS [' + Referencing + ']' + CHAR(13) + CHAR(10)
FROM
(
SELECT DISTINCT
CASE
WHEN ReferencingColumn <> '' THEN
ReferencingObject + '.' + ReferencingColumn
ELSE
ReferencingObject
END AS Referencing
FROM #Dependencies
) AS DistinctReferencing

SET @PivotStatement = '
SELECT
ReferencedObject
,ReferencedColumn
' + @SelectColumns + '
FROM
(
SELECT
ReferencedObject
,ReferencedColumn
,CASE
WHEN ReferencingColumn <> '''' THEN
ReferencingObject + ''.'' + ReferencingColumn
ELSE
ReferencingObject
END AS Referencing
,Usage
FROM #Dependencies
) AS d
PIVOT
(
MAX(Usage)
FOR Referencing IN (' + @PivotColumns + ')
) AS PivotedDependencies
ORDER BY ReferencedObject, ReferencedColumn
'

EXEC (@PivotStatement)

DROP TABLE #Dependencies
SET NOCOUNT OFF


Ссылка