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
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment