SQLServer 查询建表语句

CREATE PROCEDURE sp_show_table_create (@schema VARCHAR(100), @table VARCHAR(100))

AS
    BEGIN
        DECLARE @sql TABLE
            (
              s VARCHAR(1000) ,
              id INT IDENTITY
            );
-- 创建语句
        INSERT  INTO @sql  ( s )  VALUES  ( 'create table [' + @table + '] (' );
 
--获取注释
        SELECT  A.name AS table_name ,
                B.name AS column_name ,
                C.value AS column_description
        INTO    #columnsproperties
        FROM    sys.tables A
                INNER JOIN sys.columns B ON B.object_id = A.object_id
								INNER JOIN sys.schemas S ON A.schema_id = S.schema_id AND S.NAME = @schema
                LEFT JOIN sys.extended_properties C ON C.major_id = B.object_id
                                                       AND C.minor_id = B.column_id
        WHERE   A.name = @table;
 
-- 获取列的列表，拼接语句
        INSERT  INTO @sql  ( s )
                SELECT  '  [' + a.COLUMN_NAME + '] ' + DATA_TYPE
                        + COALESCE('('
                                   + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR)
                                   + ')', '') + ' '
                        + CASE WHEN EXISTS ( SELECT id
                                             FROM   syscolumns
                                             WHERE  OBJECT_NAME(id) = @table
                                                    AND name = a.COLUMN_NAME
                                                    AND COLUMNPROPERTY(id,
                                                              name,
                                                              'IsIdentity') = 1 )
                               THEN 'IDENTITY('
                                    + CAST(IDENT_SEED(@table) AS VARCHAR)
                                    + ','
                                    + CAST(IDENT_INCR(@table) AS VARCHAR)
                                    + ')'
                               ELSE ''
                          END + ' '
                        + ( CASE WHEN IS_NULLABLE = 'NO' THEN 'NOT '
                                 ELSE ''
                            END ) + 'NULL ' + COALESCE('DEFAULT '
                                                       + COLUMN_DEFAULT, '')
                        + CASE WHEN ISNULL(CONVERT(VARCHAR, b.column_description),
                                           '') <> ''
                               THEN '/**'
                                    + ISNULL(CONVERT(VARCHAR, b.column_description),
                                             '') + '**/,'
                               ELSE ','
                          END
                FROM    INFORMATION_SCHEMA.COLUMNS a
                        LEFT JOIN #columnsproperties b ON CONVERT(VARCHAR, a.COLUMN_NAME) = CONVERT(VARCHAR, b.column_name)
                WHERE   a.TABLE_NAME = @table AND a.TABLE_SCHEMA = @schema
                ORDER BY ORDINAL_POSITION;
-- 主键
        DECLARE @pkname VARCHAR(100);
        SELECT  @pkname = CONSTRAINT_NAME
        FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS
        WHERE   TABLE_NAME = @table
                AND CONSTRAINT_TYPE = 'PRIMARY KEY';
        IF ( @pkname IS NOT NULL )
            BEGIN
                INSERT  INTO @sql  ( s ) VALUES  ( '  PRIMARY KEY (' );
                INSERT  INTO @sql  ( s )
                        SELECT  '   [' + COLUMN_NAME + '],'
                        FROM    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                        WHERE   CONSTRAINT_NAME = @pkname
                        ORDER BY ORDINAL_POSITION;
    -- 去除尾部多余的字符
                UPDATE  @sql
                SET     s = LEFT(s, LEN(s) - 1)
                WHERE   id = @@identity;
                INSERT  INTO @sql ( s ) VALUES  ( '  )' );
            END;
        ELSE
            BEGIN
    -- 去除尾部多余的字符
                UPDATE  @sql
                SET     s = LEFT(s, LEN(s) - 1)
                WHERE   id = @@identity;
            END;
-- 继续拼接
        INSERT  INTO @sql ( s ) VALUES  ( ')' );
        
-- 输出结果
        SELECT  s AS CreateTableSQL
        FROM    @sql
        ORDER BY id;
    END;

参考博客：SQL Server 使用SQL获取建表语句 | LeFer

在其基础上添加不同schema下同名表的字段区分

调用示例：

exec sp_show_table_create 'dbo', 'tablename';