Wednesday, April 15, 2009

Get All Columns of a Table - MSSQL

As You can tell an iteration of INFORMATION_SCHEMA.COLUMNS is required, and I felt this should be created as an object of type function for best extensibility. Infact the post after this uses the same to achieve a more complex objective.

CREATE FUNCTION GetColumns (@tbl varchar(100))
RETURNS varchar(1000)
AS
BEGIN

declare @i int; select @i=min(ordinal_position) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @tbl
declare @MAX int; select @MAX = count(ordinal_position) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @tbl
declare @col varchar(100);declare @data varchar(100);
declare @TEMP varchar(1000); set @TEMP = ''
WHILE @i <= @MAX
BEGIN
select @col=Column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @tbl and ordinal_position= @i
select @data=data_type from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @tbl and ordinal_position= @i
if @i =1
set @TEMP = @col
else
set @TEMP = @TEMP + ',' + @col
set @i = @i +1

END
set @TEMP = @TEMP + ' '
return @TEMP
END

No comments: