SQL Server – Help with Recursing Self-Referencing Table

One way to get this done is probably something you have already done, and that is to replace your line:

if @DebugMode=1 print @SQL

with

if @DebugMode=1 print @SQL + ' ' + convert(nvarchar(max), @Foobar)

And you would have to do it this way for all your variables, you will need to convert them manually to avoid conversion errors.

You could also use RAISERROR in a similar fashion:

if @DebugMode=1 RAISERROR (N'We used a value of %d for @Foobar', 10, 1, @Foobar)

HTH

You can use for xml path instead of for xml raw. Build your hierarchy in correlated sub-queries in the field list. You can also make use of order by in the sub-queries to have tables ordered alphabetically and columns to order by ORDINAL_POSITION.

I tried to translate what you have and came up with this.

select T1.TABLE_CATALOG as "@TABLE_CATALOG",
       (
       select T2.TABLE_SCHEMA as "@TABLE_SCHEMA",
              (
              select T3.TABLE_NAME as "@TABLE_NAME",
                     (
                     select P.name as "@TableExtPropName",
                            P.value as "@TableExtPropValue"
                     from sys.extended_properties as P
                     where P.major_id = object_id(T2.TABLE_SCHEMA+'.'+T3.TABLE_NAME) and
                           P.minor_id = 0
                     for xml path('_TableExtProp'), type
                     ),
                     (
                     select C.COLUMN_NAME as "@COLUMN_NAME",
                            (
                            select P.name as "@ColumnExtPropName",
                                   P.value as "@ColumnExtPropValue"
                            from sys.extended_properties as P
                            where P.major_id = object_id(T2.TABLE_SCHEMA+'.'+ T3.TABLE_NAME) and 
                                  P.minor_id = C.ORDINAL_POSITION
                            for xml path('_ColumnExtProp'), type
                            )
                     from INFORMATION_SCHEMA.COLUMNS as C
                     where C.TABLE_NAME = T3.TABLE_NAME and
                           C.TABLE_SCHEMA = T2.TABLE_SCHEMA
                     order by C.ORDINAL_POSITION
                     for xml path('_Col'), type
                     )
              from INFORMATION_SCHEMA.TABLES as T3
              where T3.TABLE_SCHEMA = T2.TABLE_SCHEMA and
                    T3.TABLE_CATALOG = T1.TABLE_CATALOG
              order by T3.TABLE_NAME
              for xml path('_Table'), type
              )
       from INFORMATION_SCHEMA.TABLES as T2
       where T1.TABLE_CATALOG = T2.TABLE_CATALOG
       group by T2.TABLE_SCHEMA
       order by T2.TABLE_SCHEMA
       for xml path('_Schema'), type 
       )
from INFORMATION_SCHEMA.TABLES as T1
group by T1.TABLE_CATALOG
for xml path('_Catalog')

With this table in a database DBName.

CREATE TABLE [dbo].[TableName](
    [ID] [int] NOT NULL,
    [Name] [nchar](10) NULL
) ON [PRIMARY]

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Column description' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TableName', @level2type=N'COLUMN',@level2name=N'ID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Table description' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TableName'
GO

The output is.

<_Catalog TABLE_CATALOG="DBName">
  <_Schema TABLE_SCHEMA="dbo">
    <_Table TABLE_NAME="TableName">
      <_TableExtProp TableExtPropName="MS_Description" TableExtPropValue="Table description" />
      <_Col COLUMN_NAME="ID">
        <_ColumnExtProp ColumnExtPropName="MS_Description" ColumnExtPropValue="Column description" />
      </_Col>
      <_Col COLUMN_NAME="Name" />
    </_Table>
  </_Schema>
</_Catalog>