能把表的 create 结构生成出来

最新推荐文章于 2022-06-10 22:17:49 发布
fwacky
最新推荐文章于 2022-06-10 22:17:49 发布
阅读量359
点赞数
文章标签： constraints object insert join null table
本文链接：https://blog.csdn.net/fwacky/article/details/4793442
版权
SQL code
  
  
   
   
   
   

   
   ALTER
   
    
   
   PROCEDURE
   
    
   
   [
   
   dbo
   
   ]
   
   .
   
   [
   
   sp_HelpTable
   
   ]
   
   (
   
   @TableName
   
    sysname)

   
   AS
   
   
  
   
   SET
   
    NOCOUNT 
   
   ON
   
   
  
   
   DECLARE
   
    
   
   @ObjectID
   
    
   
   int
   
   
  
   
   DECLARE
   
    
   
   @TableScript
   
    
   
   table
   
   (Iden 
   
   int
   
    
   
   IDENTITY
   
   (
   
   1
   
   , 
   
   1
   
   ), ScriptLine 
   
   nvarchar
   
   (
   
   4000
   
   ))
  
   
   SET
   
    
   
   @ObjectID
   
    
   
   =
   
    
   
   object_id
   
   (
   
   @TableName
   
   )
  
   
   IF
   
    
   
   @ObjectID
   
    
   
   IS
   
    
   
   NULL
   
    
   
   OR
   
    
   
   OBJECTPROPERTY
   
   (
   
   @ObjectID
   
   , 
   
   '
   
   IsTable
   
   '
   
   ) 
   
   =
   
    
   
   0
   
   
  
   
   BEGIN
   
   
    
   
   RAISERROR
   
   (
   
   '
   
   指定的对象不是表对象
   
   '
   
   , 
   
   16
   
   , 
   
   1
   
   )
    
   
   RETURN
   
   
  
   
   END
   
    
  
   
   --
   
   获取表的创建脚本
   
   

   
     
   
   --
   
   插入表头
   
   

   
     
   
   INSERT
   
    
   
   INTO
   
    
   
   @TableScript
   
   (ScriptLine)
    
   
   SELECT
   
    N
   
   '
   
   CREATE TABLE [
   
   '
   
    
   
   +
   
    
   
   USER_NAME
   
   (
   
   OBJECTPROPERTY
   
   (
   
   @ObjectID
   
   , N
   
   '
   
   OwnerId
   
   '
   
   )) 
   
   +
   
    N
   
   '
   
   ].[
   
   '
   
    
   
   +
   
    
   
   object_name
   
   (
   
   @ObjectID
   
   ) 
   
   +
   
    N
   
   '
   
   ](
   
   '
   
   

  
   
   --
   
   插入字段
   
   

   
     
   
   INSERT
   
    
   
   INTO
   
    
   
   @TableScript
   
   (ScriptLine)
    
   
   SELECT
   
    N
   
   '
   
     [
   
   '
   
    
   
   +
   
    a.Name 
   
   +
   
    N
   
   '
   
   ] [
   
   '
   
    
   
   +
   
    b.name 
   
   +
   
    N
   
   '
   
   ]
   
   '
   
    
   
   +
   
    
           
   
   CASE
   
    
   
   WHEN
   
    c.
   
   Object_id
   
    
   
   IS
   
    
   
   NOT
   
    
   
   NULL
   
    
   
   THEN
   
    N
   
   '
   
    IDENTITY(
   
   '
   
    
   
   +
   
    
   
   CONVERT
   
   (
   
   nvarchar
   
   , c.seed_value) 
   
   +
   
    N
   
   '
   
   , 
   
   '
   
    
   
   +
   
    
   
   CONVERT
   
   (
   
   nvarchar
   
   , c.increment_value) 
   
   +
   
    N
   
   '
   
   )
   
   '
   
    
                
   
   ELSE
   
    
   
   ''
   
    
   
   END
   
    
   
   +
   
   
           
   
   CASE
   
    
   
   WHEN
   
    b.xusertype 
   
   IN
   
    (
   
   167
   
   , 
   
   175
   
   , 
   
   231
   
   , 
   
   239
   
   ) 
   
   THEN
   
    N
   
   '
   
   (
   
   '
   
   +
   
   CONVERT
   
   (
   
   nvarchar
   
   , a.prec)  
   
   +
   
    N
   
   '
   
   )
   
   '
   
   
                
   
   WHEN
   
    b.xusertype 
   
   in
   
    (
   
   106
   
   , 
   
   108
   
   )           
   
   THEN
   
    N
   
   '
   
   (
   
   '
   
   +
   
   CONVERT
   
   (
   
   nvarchar
   
   , a.xprec) 
   
   +
   
    N
   
   '
   
   , 
   
   '
   
    
   
   +
   
    
   
   CONVERT
   
   (
   
   nvarchar
   
   , a.xscale) 
   
   +
   
    N
   
   '
   
   )
   
   '
   
   
                
   
   ELSE
   
    
   
   ''
   
    
   
   END
   
    
   
   +
   
   
           
   
   CASE
   
    a.isnullable 
   
   WHEN
   
    
   
   1
   
    
   
   THEN
   
    N
   
   ''
   
    
   
   ELSE
   
    N
   
   '
   
    NOT
   
   '
   
    
   
   END
   
    
   
   +
   
    N
   
   '
   
    NULL
   
   '
   
    
   
   +
   
   
           
   
   CASE
   
    
   
   WHEN
   
    d.Name 
   
   IS
   
    
   
   NOT
   
    
   
   NULL
   
    
   
   THEN
   
    N
   
   '
   
    DEFAULT 
   
   '
   
    
   
   +
   
    d.Definition 
   
   ELSE
   
    N
   
   ''
   
    
   
   END
   
    
   
   +
   
   
           N
   
   '
   
   ,
   
   '
   
    
      
   
   FROM
   
    sys.syscolumns a
        
   
   LEFT
   
    
   
   JOIN
   
    sys.systypes b 
   
   ON
   
    a.xusertype 
   
   =
   
    b.xusertype
        
   
   LEFT
   
    
   
   JOIN
   
    sys.identity_columns c 
   
   ON
   
    c.
   
   Object_id
   
    
   
   =
   
    a.ID 
   
   AND
   
    c.Column_ID 
   
   =
   
    a.ColID
        
   
   LEFT
   
    
   
   JOIN
   
    sys.default_constraints d 
   
   ON
   
    d.Parent_Object_ID 
   
   =
   
    a.ID 
   
   AND
   
    d.Parent_column_ID 
   
   =
   
    a.ColID
    
   
   WHERE
   
    a.
   
   [
   
   ID
   
   ]
   
    
   
   =
   
    
   
   @ObjectID
   
   
    
   
   ORDER
   
    
   
   BY
   
    a.ColOrder

  
   
   --
   
   插入主键和索引
   
   

   
     
   
   DECLARE
   
    
   
   @IndexID
   
    
   
   int
   
   , 
   
   @IndexScript
   
    
   
   nvarchar
   
   (
   
   4000
   
   )
  
   
   DECLARE
   
    IndexCursor 
   
   CURSOR
   
    
   
   FOR
   
    
    
   
   SELECT
   
    b.Index_ID, N
   
   '
   
     CONSTRAINT [
   
   '
   
    
   
   +
   
    a.Name 
   
   +
   
    N
   
   '
   
   ] 
   
   '
   
    
   
   +
   
    
           
   
   CASE
   
    a.Type 
   
   WHEN
   
    
   
   '
   
   PK
   
   '
   
    
   
   THEN
   
    N
   
   '
   
   PRIMARY KEY 
   
   '
   
    
   
   WHEN
   
    
   
   '
   
   UQ
   
   '
   
    
   
   THEN
   
    N
   
   '
   
   UNIQUE 
   
   '
   
    
   
   END
   
    
   
   +
   
    
           
   
   CASE
   
    b.Type 
   
   WHEN
   
    
   
   1
   
       
   
   THEN
   
    N
   
   '
   
   CLUSTERED
   
   '
   
      
   
   WHEN
   
    
   
   2
   
        
   
   THEN
   
    N
   
   '
   
   NONCLUSTERED 
   
   '
   
    
   
   END
   
    
   
   +
   
    N
   
   '
   
   (
   
   '
   
   
      
   
   FROM
   
    sys.key_constraints a
        
   
   LEFT
   
    
   
   JOIN
   
    sys.indexes b 
   
   ON
   
    b.
   
   Object_ID
   
    
   
   =
   
    a.Parent_Object_ID 
   
   AND
   
    b.index_id 
   
   =
   
    a.unique_index_id
    
   
   WHERE
   
    a.Parent_Object_ID 
   
   =
   
    
   
   @ObjectID
   
   
  
   
   OPEN
   
    IndexCursor
  
   
   FETCH
   
    
   
   NEXT
   
    
   
   FROM
   
    IndexCursor 
   
   INTO
   
    
   
   @IndexID
   
   , 
   
   @IndexScript
   
   
  
   
   WHILE
   
    
   
   @@FETCH_STATUS
   
    
   
   =
   
    
   
   0
   
   
  
   
   BEGIN
   
   
    
   
   SELECT
   
    
   
   @IndexScript
   
    
   
   =
   
    
   
   @IndexScript
   
    
   
   +
   
    N
   
   '
   
   [
   
   '
   
    
   
   +
   
    
   
   INDEX_COL
   
   (
   
   object_name
   
   (
   
   @ObjectID
   
   ), 
   
   2
   
    , 
   
   1
   
   ) 
   
   +
   
    N
   
   '
   
   ],
   
   '
   
   
      
   
   FROM
   
    sys.index_columns 
    
   
   WHERE
   
    
   
   Object_ID
   
    
   
   =
   
    
   
   @ObjectID
   
   
      
   
   AND
   
    Index_ID 
   
   =
   
    
   
   2
   
   
    
    
   
   SET
   
    
   
   @IndexScript
   
    
   
   =
   
    
   
   LEFT
   
   (
   
   @IndexScript
   
   , 
   
   LEN
   
   (
   
   @IndexScript
   
   ) 
   
   -
   
   1
   
   ) 
   
   +
   
    N
   
   '
   
   ),
   
   '
   
   
    
   
   INSERT
   
    
   
   INTO
   
    
   
   @TableScript
   
   (ScriptLine) 
   
   VALUES
   
   (
   
   @IndexScript
   
   )
    
   
   FETCH
   
    
   
   NEXT
   
    
   
   FROM
   
    IndexCursor 
   
   INTO
   
    
   
   @IndexID
   
   , 
   
   @IndexScript
   
   
  
   
   END
   
   
  
   
   Close
   
    IndexCursor
  
   
   DEALLOCATE
   
    IndexCursor

  
   
   insert
   
    
   
   into
   
    
   
   @TableScript
   
   (ScriptLine) 
   
   VALUES
   
   (
   
   '
   
   )
   
   '
   
   )
  
   
   select
   
    
   
   *
   
    
   
   from
   
    
   
   @TableScript