您可以使用两个游标或一个游标来解决此问题。两个游标将使代码更易读。一个游标将更有效。

两个游标

下面的代码演示如何使用两个游标循环访问表和列。

DECLARE @tblNm VARCHAR(MAX)
DECLARE cTables CURSOR FOR
    SELECT tblNm
    FROM CompositeSchema
    GROUP BY tblNm
    ORDER BY tblNm
OPEN cTables
FETCH cTables INTO @tblNm
WHILE @@FETCH_STATUS=0
    BEGIN
        PRINT 'Processing table '+@tblNm

        -- Start of code to execute for each table

        DECLARE @sqlCreateTable VARCHAR(MAX)
        SET @sqlCreateTable = 'CREATE TABLE ['+@tblNm+'] ('

        DECLARE @colNm VARCHAR(MAX),@colTyp VARCHAR(MAX),@colLen INT,@colReq BIT,@colWarning BIT,@colUni BIT,@colComUni BIT
        DECLARE @isFirst BIT
        SET @isFirst = 1
        DECLARE cCols CURSOR FOR
            SELECT colNm,colTyp,colLen,colReq,colWarning,colUni,colComUni
            FROM CompositeSchema
            WHERE tblNm=@tblNm
            ORDER BY colComUni DESC,colNm ASC
        OPEN cCols
        FETCH cCols INTO @colNm,@colTyp,@colLen,@colReq,@colWarning,@colUni,@colComUni
        WHILE @@FETCH_STATUS=0
            BEGIN
                PRINT 'Processing column ['+@tblNm+'].['+@colNm+']'

                -- Start of code to process each column (simplified!)

                IF @isFirst=0
                    SET @sqlCreateTable = @sqlCreateTable+','
                SET @isFirst = 0

                SET @sqlCreateTable = @sqlCreateTable+'['+@colNm+'] '+@colTyp
                IF NOT @colLen IS NULL
                    SET @sqlCreateTable = @sqlCreateTable+'('+CAST(@colLen AS VARCHAR)+')'

                -- End of code to process each column

                FETCH cCols INTO @colNm,@colTyp,@colLen,@colReq,@colWarning,@colUni,@colComUni
            END
        CLOSE cCols
        DEALLOCATE cCols

        SET @sqlCreateTable = @sqlCreateTable+')'
        PRINT @sqlCreateTable

        -- EXEC(@sqlCreateTable)

        -- End of code to execute for each table

        FETCH cTables INTO @tblNm
    END
CLOSE cTables
DEALLOCATE cTables

一个光标

在本例中，我们只使用一个游标。我们在@currentTblNm变量中跟踪当前正在处理的表。每当变量改变时，我们一次创建所有列。

DECLARE @currentTblNm VARCHAR(MAX),@sqlCreateTable VARCHAR(MAX)
SET @currentTblNm = ''

DECLARE @tblNm VARCHAR(MAX),@colNm VARCHAR(MAX),@colTyp VARCHAR(MAX),@colLen INT,@colReq BIT,@colWarning BIT,@colUni BIT,@colComUni BIT
DECLARE @isFirst BIT
SET @isFirst = 1
DECLARE cCols CURSOR FOR
    SELECT tblNm,colNm,colTyp,colLen,colReq,colWarning,colUni,colComUni
    FROM CompositeSchema
    ORDER BY tblNm ASC,colComUni DESC,colNm ASC
OPEN cCols
FETCH cCols INTO @tblNm,@colNm,@colTyp,@colLen,@colReq,@colWarning,@colUni,@colComUni
WHILE @@FETCH_STATUS=0
    BEGIN
        IF @currentTblNm<>@tblNm
            BEGIN
                IF @sqlCreateTable<>''
                    BEGIN
                        SET @sqlCreateTable = @sqlCreateTable+')'
                        PRINT @sqlCreateTable
                        --EXEC (@sqlCreateTable)
                    END

                SET @isFirst = 1
                SET @sqlCreateTable = 'CREATE TABLE ['+@tblNm+'] ('
                SET @currentTblNm = @tblNm

                PRINT 'Processing table ['+@tblNm+']'
            END

        -- Start of code to process each column (simplified!)

        IF @isFirst=0
            SET @sqlCreateTable = @sqlCreateTable+','
        SET @isFirst = 0

        SET @sqlCreateTable = @sqlCreateTable+'['+@colNm+'] '+@colTyp
        IF NOT @colLen IS NULL
            SET @sqlCreateTable = @sqlCreateTable+'('+CAST(@colLen AS VARCHAR)+')'

        -- End of code to process each column

        FETCH cCols INTO @tblNm,@colNm,@colTyp,@colLen,@colReq,@colWarning,@colUni,@colComUni
    END
CLOSE cCols
DEALLOCATE cCols

IF @sqlCreateTable<>''
    BEGIN
        SET @sqlCreateTable = @sqlCreateTable+')'
        PRINT @sqlCreateTable

        -- EXEC(@sqlCreateTable)
    END

两个游标和一个游标这两段代码都被简化了。正确创建所有约束（如主键、唯一约束、外键等）的逻辑，正确Map列数据类型的逻辑，还有不要忘记，创建一个新表和修改一个现有表之间的区别超出了本文的范围。
值得一提的是，您还可以使用声明式SQL代码和FOR XML来创建表结构。这是可能的，并且能够生成性能更好的CREATE TABLE语句。根据经验，我知道这段代码将更难维护，并且您可能会遇到声明式SQL的限制。