Run the following script to create the function:
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'getColDef')
DROP FUNCTION dbo.getColDef;
GO
CREATE FUNCTION getColDef (@TAB_AND_COL VARCHAR(256))
RETURNS @RESULT TABLE
([Table] varchar(256),
[Column] varchar(256),
[Type] varchar(64),
[Length] int,
[IsNullable] varchar(5),
[FK_Name] varchar(256),
[Index_Name] varchar(256),
[Trigger_Name] varchar(256))
AS
BEGIN
DECLARE @TNAME VARCHAR(256), @COL VARCHAR(256), @CHARIX INT
SET @CHARIX = CHARINDEX('.', @TAB_AND_COL)
SET @TNAME = LEFT(@TAB_AND_COL, CHARINDEX('.',@TAB_AND_COL)-1)
SET @COL = SUBSTRING(@TAB_AND_COL, @CHARIX + 1, LEN(@TAB_AND_COL) - LEN(@TNAME))
INSERT INTO @RESULT
SELECT DISTINCT OBJECT_NAME(c.OBJECT_ID) [Table],c.name [Column],t.name AS [Type],c.max_length AS [Length]
, CASE C.is_nullable WHEN 1 THEN 'Yes' ELSE 'No' END AS [IsNullable]
, FK.FK_NAME as FK_Name
, IDX.IdxName as Index_Name
, trig.name as Trigger_Name
FROM sys.columns c
JOIN sys.types t ON c.user_type_id=t.user_type_id
LEFT JOIN
(SELECT f.name AS FK_NAME
, OBJECT_NAME(f.parent_object_id) AS FK_TABNAME
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
WHERE OBJECT_NAME(f.parent_object_id) = @TNAME
AND COL_NAME(fc.parent_object_id,fc.parent_column_id) = @COL) FK
ON FK.FK_TABNAME = OBJECT_NAME(c.OBJECT_ID)
LEFT JOIN
(SELECT i.Name AS IdxName, OBJECT_NAME(i.object_ID) AS TName, c.Name AS CName
FROM sys.indexes i
JOIN sys.index_columns ic ON i.index_id = ic.index_id AND i.object_id = ic.object_id
JOIN sys.columns c ON ic.column_id = c.column_id AND ic.object_id = c.object_id
WHERE c.name = @COL
AND OBJECT_NAME(c.object_id) = @TNAME) IDX
ON IDX.TName = OBJECT_NAME(c.object_id)
LEFT JOIN sys.triggers trig ON trig.parent_id = c.object_id
WHERE C.object_id = object_id(N''+ @TNAME +'') AND c.name = @COL
RETURN
END
To execute the function, simply run the following script:
SELECT * FROM getColDef('TABLENAME.COLUMN_NAME')
Similarly, you can get the column definitions for an entire table by executing the following script:
DECLARE @TABLENAME VARCHAR(256)
SET @TABLENAME = 'TABLENAME' -- Put the name of the table here
DECLARE @RESULTS TABLE([Table] varchar(256),[Column] varchar(256),[Type] varchar(64),[Length] int,[IsNullable] varchar(5)
,[FK_Name] varchar(256),[Index_Name] varchar(256),[Trigger_Name] varchar(256))
DECLARE @ROW_NOW INT, @ROW_MAX INT, @COLNAME VARCHAR(256)
SELECT @ROW_NOW = MIN(ROWNUM), @ROW_MAX = MAX(ROWNUM)
FROM (SELECT ROW_NUMBER() OVER (ORDER BY name) ROWNUM, name FROM sys.columns WHERE OBJECT_NAME(object_id) = @TABLENAME) SRC
WHILE @ROW_NOW <= @ROW_MAX
BEGIN
SELECT @COLNAME = name FROM
(SELECT ROW_NUMBER() OVER (ORDER BY name) ROWNUM, name FROM sys.columns WHERE OBJECT_NAME(object_id) = @TABLENAME) SRC
WHERE ROWNUM = @ROW_NOW
INSERT INTO @RESULTS
SELECT * FROM getColDef(@TABLENAME+'.'+@COLNAME)
SET @ROW_NOW = @ROW_NOW + 1
END
SELECT * FROM @RESULTS ORDER BY [Column]
