Skip to content

Instantly share code, notes, and snippets.

@wqweto
Created February 7, 2013 10:45
Show Gist options
  • Save wqweto/4730218 to your computer and use it in GitHub Desktop.
Save wqweto/4730218 to your computer and use it in GitHub Desktop.
Find Column Usage in MSSQL 2008
DECLARE @TmpColumns TABLE (
TableName SYSNAME
, ColumnName SYSNAME
, object_id INT
, column_id INT
)
INSERT @TmpColumns
SELECT s.TableName, s.ColumnName, c.object_id, c.column_id
--FROM (
-- SELECT 'v_sto_DocsDetailsInt', 'Lot' UNION ALL
-- SELECT 'v_sto_DocsDetails', 'Lot' UNION ALL
-- SELECT 'sto_DocsDetails', 'Lot'
-- ) s(TableName, ColumnName)
FROM (
SELECT OBJECT_NAME(object_id)
, name
FROM sys.columns
WHERE name = 'Lot'
) s(TableName, ColumnName)
JOIN sys.columns c
ON c.object_id = OBJECT_ID(s.TableName)
AND c.name = s.ColumnName
SELECT DISTINCT OBJECT_NAME(d.object_id) AS ObjectName
, c.TableName
, c.ColumnName
, d.is_updated
, d.is_selected
FROM sys.sql_dependencies d
JOIN @TmpColumns c
ON d.referenced_major_id = c.object_id
AND d.referenced_minor_id = c.column_id
WHERE d.class = 0
ORDER BY 1, 2, 3
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment