Get Table Column Info

What is attached to this column? This stored procedure answers that completely: given a table and column, it reports every default constraint, check constraint, primary key or unique constraint, foreign key, computed column definition, view, and index that involves the column. Pass @Type to filter by object type (for example 'F' for foreign keys) or leave it as * for everything.

Example: EXEC GetTableColumnInfo @Table = 'Customers', @Column = 'CustomerID'

-- =============================================
-- Author:		Josh Patterson
-- Create date: 8/15/2006
-- Description:	Returns information on a specified tables column. (Defaults, Check Constraints,
--					Primary Key and Unique Constraints, Foreign Key Constraints, Computed Columns, Views, Indexes)
-- =============================================
CREATE PROCEDURE [GetTableColumnInfo]
	@Table	VarChar(250),
	@Column	VarChar(250),
	@Type	VarChar(250) = '*'
AS
BEGIN
	SET NOCOUNT ON;

	WITH Rel_Objs (Obj_Name, Type_Name, Type_Desc, Parent_Object_ID, Parent_Column_ID) AS
	(
		(
			SELECT
				d.Name,
				d.Type,
				d.Type_Desc,
				d.Parent_Object_ID,
				d.Parent_Column_ID
			FROM
				Sys.Default_Constraints AS d -- Defaults
		)
		UNION ALL
		(
			SELECT
				c.Name,
				c.Type,
				c.Type_Desc,
				c.Parent_Object_ID,
				c.Parent_Column_ID
			FROM
				Sys.Check_Constraints AS c -- Check Constraints
		)
		UNION ALL
		(
			SELECT
				k.Name,
				k.Type,
				k.Type_Desc,
				k.Parent_Object_ID,
				kc.Column_ID
			FROM
				Sys.Key_Constraints AS k -- Primary Key and Unique Constraints
				JOIN Sys.Index_Columns AS kc
					ON kc.Object_ID = k.Parent_Object_ID
					AND kc.Index_ID = k.Unique_Index_ID
		)
		UNION ALL
		(
			SELECT
				f.Name,
				f.Type,
				f.Type_Desc,
				f.Parent_Object_ID,
				fc.Parent_Column_ID
			FROM
				Sys.Foreign_Keys AS f -- Foreign Key Constraints
			JOIN Sys.Foreign_Key_Columns AS fc
				ON fc.Constraint_Object_ID = f.Object_ID
				AND fc.Parent_Object_ID = f.Parent_Object_ID
		)
		UNION ALL
		(
			SELECT
				c.Name,
				'CC',
				'COMPUTED_Column',
				d.Referenced_Major_ID,
				d.Referenced_Minor_ID
			FROM
				Sys.sql_dependencies AS d -- computed Columns
			JOIN Sys.Columns AS c
				ON c.Object_ID = d.Object_ID
				AND c.Column_ID = d.Column_ID
			WHERE
				d.Object_ID = d.Referenced_Major_ID
				AND d.Referenced_Minor_ID > 0
				AND d.Column_ID > 0
		)
		UNION ALL
		(
			SELECT
				o.Name,
				o.Type,
				o.Type_Desc,
				d.Referenced_Major_ID,
				d.Referenced_Minor_ID
			FROM
				Sys.sql_dependencies AS d -- views
			JOIN Sys.Objects AS o
				ON o.Object_ID = d.Object_ID
			WHERE
				d.Object_ID <> d.Referenced_Major_ID
				AND d.Referenced_Minor_ID > 0
				AND o.Type = 'V'
		)
		UNION ALL
		(
			SELECT
				i.Name,
				'IX',
				i.Type_Desc,
				i.Object_ID,
				ic.Column_ID
			FROM
				Sys.Indexes AS i -- Indexes
			JOIN Sys.Index_Columns AS ic
				ON ic.Index_ID = i.Index_ID
				AND ic.Object_ID = i.Object_ID
			WHERE
				i.Is_Primary_Key = 0
				AND i.Is_Unique_Constraint = 0
		)
	),

	Rel_Objs_Det(Schema_Name, Table_Name, Column_Name, Rel_Obj_Name, Rel_Type_Name, Rel_Type_Desc) AS
	(
		SELECT
			s.Name, o.Name, c.Name, r.Obj_Name, r.Type_Name, r.Type_Desc
		FROM
			Rel_Objs AS r -- Names for the Table/Column to query for
		JOIN Sys.Objects AS o
			ON r.Parent_Object_ID = o.Object_ID
		JOIN Sys.Columns AS c
			ON c.Object_ID = o.Object_ID
			AND c.Column_ID = r.Parent_Column_ID
		JOIN Sys.Schemas AS s
			ON s.Schema_ID = o.Schema_ID
	)

	SELECT
		r.Schema_Name,
		r.Table_Name,
		r.Column_Name,
		r.Rel_Obj_Name,
		r.Rel_Type_Name,
		r.Rel_Type_Desc
	FROM
		Rel_Objs_Det AS r
	WHERE
		r.Table_Name = @Table
		AND r.Column_Name = @Column
		AND (r.Rel_Type_Name = @Type OR @Type = '' OR @Type = '*')
END
GO