SQL GetColumns

I am so tired of redoing this ever couple years when I lose the code that I am going to commit my GetColums store procedure right here and update it as I bring it up to snuff. Hope this helps someone other than me. I am going to provide a variety of outputs.

Update: I have made the cool changes. Now you can pass it a string like ‘DECLARE @[name] [type], ‘ as the second argument and get all the declare statements for variables for the columns of a table. You could also pass it something like ‘[name] = @[name], ‘ for the SET clauses of an update statement.

Update: I think this is more usable as a function so I have changed it to that. 201107240114.

Create function [dbo].[GetColumns] (@Table varchar(100), @OutString varchar(150)) returns varchar(3000) as
Begin
 Declare @Name varchar(100)
 Declare @Type varchar(20)
 Declare @XType int
 Declare @Length int
 Declare @XPrec int
 Declare @XScale int
 Declare @ColOut as varchar(300) = ''
 Declare @Result as varchar(3000) =''
 Declare @LoopCount int
 Declare col cursor READ_ONLY FORWARD_ONLY
 for select c.name, t.name, t.xtype, c.[Length], c.XPrec, c.XScale
 from syscolumns c
 inner join (select * from systypes where status = 0) t on c.xtype = t.xtype
 where c.id = Object_id(@Table) order by colorder
 Open col
 FETCH NEXT FROM col 
 INTO @Name, @Type, @Xtype, @Length, @XPrec, @XScale

 WHILE @@FETCH_STATUS = 0 BEGIN
  set @ColOut = 
  Case @XType 
   When 106 Then
    replace(@OutString, '[type]',@Type + ' (' + cast(@XPrec as varchar(50)) + ',' 
      + cast(@xscale as varchar(50)) + ')')
   When 231 Then
    replace(@Outstring, '[type]', @Type + ' (' + cast(@length as varchar(50)) + ')')
   else
    REPLACE(@OutString, '[type]', @Type)
  End
  set @ColOut = REPLACE(@ColOut, '[name]', @Name)
  set @Result += @ColOut
  FETCH NEXT FROM col 
  INTO @Name, @Type, @Xtype, @Length, @XPrec, @XScale
 End
 close col
 deallocate col
 --set @Result = rtrim(@Result)
 --If len(@Result) > 0 
 -- set @Result = (substring(@Result, 1, LEN(@Result) - 1))
 Return(@Result)
End