SQL Server 2008 - How did I solve the need to check the column definitions quickly? Another tool I developed for myself, with regards to making the creation of various DML operations easier in a large database of hundreds of tables, is a nifty user defined function to easily show almost all relevant information about a column. Running the function will display the type, length, potential key constraints (FK or PK), potential indexes as well as potential triggers tied to the column.

Code posted in the answer below. There may be situations where the function doesn't work, depending on some database settings or features that don't exist in our project. But it should be relatively dynamic.

Again, if you have any similar useful tips or advice, don't hesitate to share!
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 &#61; '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 &#61; MIN(ROWNUM), @ROW_MAX &#61; MAX(ROWNUM) 
FROM (SELECT ROW_NUMBER() OVER (ORDER BY name) ROWNUM, name FROM sys.columns WHERE OBJECT_NAME(object_id) &#61; @TABLENAME) SRC

WHILE @ROW_NOW &#60;&#61; @ROW_MAX 
BEGIN
    SELECT @COLNAME &#61; name FROM 
    (SELECT ROW_NUMBER() OVER (ORDER BY name) ROWNUM, name FROM sys.columns WHERE OBJECT_NAME(object_id) &#61; @TABLENAME) SRC
    WHERE ROWNUM &#61; @ROW_NOW

    INSERT INTO @RESULTS
    SELECT * FROM getColDef(@TABLENAME+'.'+@COLNAME)

    SET @ROW_NOW &#61; @ROW_NOW + 1
END

SELECT * FROM @RESULTS ORDER BY [Column]