Resources/sp_helptable.sql

这里只显示每个文件的开头 60 行。登录后可以解锁完整代码。

微信扫码关注公众号，代码整理好后第一时间通知你。

IF EXISTS(  SELECT * FROM  sys.procedures WHERE name='sp_helptable')
BEGIN
    DROP PROC sp_helptable
END
go
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('('
                                   +  CASE WHEN DATA_TYPE='decimal' 
										THEN
								  CAST(a.NUMERIC_PRECISION AS VARCHAR)+','+CAST(a.NUMERIC_SCALE AS VARCHAR)
								  WHEN CHARACTER_MAXIMUM_LENGTH<0 THEN 'Max'
								  ELSE CAST(CHARACTER_MAXIMUM_LENGTH AS  VARCHAR) END
                                   + ')', '') + ' '
                        + 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),'')+'**/'