About Me

My photo
Colorado
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.

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




No comments:

Post a Comment