SQL Server 使用SQL获取建表语句

SQL Server 没有提供函数或存储过程直接查看建表语句，当然它的DBMS客户端提供了这个功能 但是太麻烦 ，下面介绍3种方式获取 建表语句。

举例表SYS_USER

                                         

1. 数据库→任务→生成脚本→选择特定表（存储过程也可）→设置高级设置中类型→保存到新建查询窗口 即可。

2. 找到目标表右键→编写表脚本→create到新查询编辑窗口 即可

3.自建 存储过程 帮助生成建表语句 后调用

(此部分参考 https://www.lefer.cn/posts/25533/  )

i) Create Proceduce

CREATE PROCEDURE sp_helptable ( @table VARCHAR(100) )
-- exec sp_helptable tablename
-- SQL Server 没有提供函数或存储过程直接查看建表语句（），此存储过程可实现
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
                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
                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;



 

ii)调用

 EXEC sp_helptable sys_user

iii)

最后  还有一种 SQL 直接建 表的 sql 如下

SELECT a.*,'' 其他新增列 into new_table    from sys_user a WHERE 1=2
--要求 new_table不存在

SELECT a.*,'' 其他新增列 into new_table    from sys_user a WHERE 1=2  之所以加条件 是因为 这里只需要表结构， 无需数据， 与SQL insert into new_table2 SELECT * from sys_user 的区别在于 后者 要求new_table2 存在