udfGetColumnList for a given object

This is a handy table function that returns a column list for a table, view, or function.

Use Admin

IF Object_ID('dbo.udfGetColumnList') Is Not Null

      DROP FUNCTION dbo.udfGetColumnList

go

CREATE FUNCTION dbo.udfGetColumnList(

      @SchemaName sysname,

      @ObjName sysname

      )

RETURNS TABLE

AS

/*    DATE        AUTHOR            REMARKS

      3/1/14      PPaiva            Initial creation.

      DESCRIPTION

            Returns a list of columns with attributes for

                  a given view or table in the current database.

      USAGE

            SELECT * FROM udfGetColumnList('sys', 'syssqlguides')

            SELECT * FROM udfGetColumnList('dbo', 'DelimitedSplit8K')

            SELECT * FROM udfGetColumnList('dbo', 'udfGetColumnList')

           

*/

RETURN

SELECT  o.Type ObjTypeAbbrev,

            CASE  WHEN o.Type = 'U' THEN 'Table'

                        WHEN o.Type = 'V' THEN 'View'

                        WHEN o.Type = 'TF' THEN 'FunctionTableTVF'

                        WHEN o.Type = 'IF' THEN 'FunctionInline'

                        ELSE 'Undefined' END ObjType,

            c.column_id ColID,

            s.name COLLATE SQL_Latin1_General_CP1_CI_AS SchemaName,

            o.Name COLLATE SQL_Latin1_General_CP1_CI_AS ObjName,

            c.Name COLLATE SQL_Latin1_General_CP1_CI_AS ColName,

            t.Name COLLATE SQL_Latin1_General_CP1_CI_AS Datatype,

            c.max_length Length,

            CASE WHEN t.Name COLLATE SQL_Latin1_General_CP1_CI_AS In ('char', 'varchar', 'nchar', 'nvarchar')

                        THEN t.Name COLLATE SQL_Latin1_General_CP1_CI_AS + '(' +

                              CASE WHEN c.max_Length = -1 THEN 'max' ELSE Convert(varchar, c.max_length) END

                              + ')'

                   ELSE t.Name COLLATE SQL_Latin1_General_CP1_CI_AS

                  END   CodeDatatype,

            c.Is_Computed IsCalc,

            c.Is_Identity IsIdentity,

            c.is_nullable IsNullable,

            o.object_id,

            o.create_date ObjCreateDate,

            o.modify_date ObjModifyDate

FROM sys.objects o

LEFT JOIN sys.columns c

      ON c.object_id = o.object_id

JOIN sys.schemas s

      ON s.schema_id = o.schema_id

LEFT JOIN sys.types t

      ON t.user_type_id = c.system_type_id

WHERE o.name = @ObjName

  AND s.name = @SchemaName