Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
    
Self answered questions are fine but can you rephrase your question as an actual question? e.g. "How would I write a function that does x,y,z" where x,y,z is the stuff that your function actually does. See It’s OK to Ask and Answer Your Own Questions for more about this. –  Martin Smith Jan 4 '13 at 15:29
    
Thanks for the link! I should have checked up on those rules myself, but sometimes you just assume you know them all once you've been to enough different forums and boards. :) –  Kahn Jan 4 '13 at 16:15
add comment

2 Answers

up vote 0 down vote accepted

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]
share|improve this answer
1  
For column names etc. there is already a standard datatype to use sysname. Using varchar(256) means that it won't work correctly if used on this table for example CREATE TABLE [☹] ([☺] int,[☻] int) –  Martin Smith Jan 4 '13 at 15:35
add comment

I like the script posted above but the command that I've found very very useful is "Alt + F1" but on a table.

So for example, write any table name in a new query window, highlight the table name and click Alt + F1 and it displays table definition which includes column definitions as well as indexes, where the table is located etc.

share|improve this answer
    
FYI, ALT+F1 is a shortcut for executing sp_help on an object, so you can get the same result by executing sp_help '<object name>'. You can view the shortcuts (and set your own) by opening Tools->Options->Keyboard->Query Shortcuts in SSMS. –  Mike Fal Jan 4 '13 at 18:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.