MSSQL store procedure to Describe Schema from fields list in XML

Pick Language to Auto Translate:
AR | BG | CA | CS | DA | DE | EL | ES | FI | FR | HI | HR | ID | IT | IW | JA | KO | LT | LV | NL | NO | PL | PT | RO | RU | SK | SR | SL | SV | TL | UK | VI | ZH | ZH-TW

Submitted by chanh on

Below is the actual code so you can just copy and paste to create the store procedure.


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[spDescribeSchema]
(
@FieldList XML = '',
@retbuff varchar(max) output
)
AS
BEGIN
-- use table variable sample and while loop
declare @FieldList1 table(
ID INT,
FieldName varchar(max),
FieldType varchar(20),
FieldLen int
)

declare @Schema varchar(max) = 'ID int, FieldName varchar(max), FieldType varchar(20), FieldLen int'
-- see other post regarding spXML2Table
INSERT INTO @FieldList1 exec [dbo].[spXML2Table] @FieldList, @Schema

Declare @ID varchar(MAX)
Declare @FieldName varchar(MAX)
Declare @FieldType varchar(MAX)
Declare @FieldSize varchar(MAX)
Declare @FieldLen varchar(MAX) = ''

declare @sql varchar(MAX)

Declare @fldList varchar(max)
set @fldList = ''

Declare c cursor for select * from @FieldList1
open c
fetch c into @ID, @FieldName, @FieldType, @FieldSize
while @@fetch_status=0
begin
if @FieldType <> 'int'
set @FieldLen = + '(' + @FieldSize + ')'

select @fldList = @fldList + @FieldName + ' ' + @FieldType + @FieldLen + ', '
fetch next from c into @ID, @FieldName, @FieldType, @FieldSize
end
close c
deallocate c
-- return ID int, InterfaceID int, FldPos nvarchar(20), FldLen nvarchar(20), FldName nvarchar(100), RecType nvarchar(10)
select @retbuff = left(@fldList,len(@fldList)-1)
return

END

Forums: