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.

Tuesday, September 9, 2014

udfGetColumnList( )

USE Admin

IF OBJECT_ID('dbo.udfGetColumnList') Is Not Null
      DROP FUNCTION dbo.udfGetColumnList
go

CREATE FUNCTION dbo.udfGetColumnList(
      @ObjName sysname,
      @UseBrackets bit
      )
RETURNS varchar(max)
AS
/*    DATE        AUTHOR            REMARKS
      9/9/14            PPaiva                  Initial creation.

      DESCRIPTION
            Returns a comma-separated list of columns based
                  on @ObjName from vwDictCol.

      DEPENDENCY
            vwDictCol

      USAGE
            SELECT dbo.udfGetColumnList('Employee', 1)
            SELECT * FROM vwDictCol where ObjName = 'Employee'

            SELECT dbo.udfGetColumnList(TableName, 1) ColList,
                        *
            FROM vwRowCount


      DEBUG
            SELECT *
            FROM vwDictCol
           

*/
BEGIN
      DECLARE @t TABLE( ColID int,
                                    ColName sysname
                                    )

      DECLARE @MaxColID int,
                  @ColID int = 0,
                  @ColName sysname,
                  @Out varchar(max) = ''
     
     
      INSERT INTO @t
            SELECT ColID, ColName
            FROM vwDictCol
            WHERE ObjName = @ObjName
            ORDER BY 1


      SELECT  @MaxColID = Max(ColID)                 
      FROM @t

      WHILE @ColID < @MaxColID
            BEGIN
                  -- Get next ColID
                  SELECT @ColID = Min(ColID)
                  FROM @t
                  WHERE ColID > @ColID

                  -- Get ColName
                  SELECT @ColName = ColName
                  FROM @t
                  WHERE ColID = @ColID


                  IF @UseBrackets = 1
                        SET @ColName = '[' + @ColName + ']'

                  SET @Out += @ColName

                  IF @ColID <> @MaxColID
                        SET @Out += ', '
                       
            END


      RETURN @Out

END



No comments:

Post a Comment