About Me

Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group from January 2009 to present. 517 Lafarge Ave. Louisville, CO 80027 7667 Dyer Road Louisville, CO 80027

Friday, March 7, 2014

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

CREATE FUNCTION dbo.udfGetColumnList(
      @SchemaName sysname,
      @ObjName sysname
/*    DATE        AUTHOR            REMARKS
      3/1/14      PPaiva            Initial creation.

            Returns a list of columns with attributes for
                  a given view or table in the current database.

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



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.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