[sql server] 擷取指定表的建立指令碼,包括表和欄位的屬性、外鍵(注釋掉的)

USE   [master]  
  GO
  SET   ANSI_NULLS   ON  
  GO  
  SET   QUOTED_IDENTIFIER   ON  
  GO  
  /*|<PRE>  
  ****************************************************************************  
    軟體名稱:   May   Flower   Erp  
    著作權:   (C)   2005-2006   May   Flower   ERP   開發組  
    對象名稱:   sp_HelpTable  
    對象作者:   lvqy  
    建立日期:   2005-12-04  
    修改記錄:  
    功能描述:   擷取指定表的建立指令碼,包括表和欄位的屬性、外鍵(注釋掉的)  
  ----------------------------------------------------------------------------  
    參數列表:    
                  1:   @TableName   需要建立指令碼的表的名稱  
  ****************************************************************************  
  |</PRE>*/  
  CREATE   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  
   
      --除去最後一個,號  
      UPDATE   @TableScript    
          SET   ScriptLine   =   LEFT(ScriptLine,   LEN(ScriptLine)   -   1)  
      WHERE   Iden   =   (SELECT   MAX(Iden)   FROM   @TableScript)  
      INSERT   INTO   @TableScript(ScriptLine)   VALUES(N')')  
      INSERT   INTO   @TableScript(ScriptLine)   VALUES(N'GO')  
      INSERT   INTO   @TableScript(ScriptLine)   VALUES(N'')  
    --擷取表備忘  
      DECLARE   @PropScript   nvarchar(4000)  
      INSERT   INTO   @TableScript(ScriptLine)  
          SELECT   N'EXEC   sys.sp_addextendedproperty   @name=N'''   +   a.Name   +   N''',   @value=N'''   +   CONVERT(nvarchar,   a.Value)   +   N'''   ,@level0type=N''SCHEMA'',   @level0name=N'''   +  
                        USER_NAME(OBJECTPROPERTY(a.major_Id,   N'OwnerId'))   +   N''',   @level1type=N''TABLE'',   @level1name=N'''   +   b.Name   +   N''''  
              FROM   sys.extended_properties   a  
                  LEFT   JOIN   sys.objects   b   ON   b.[Object_ID]   =   a.major_Id  
          WHERE   a.major_Id   =   @ObjectID  
              AND   Minor_ID   =   0  
      INSERT   INTO   @TableScript(ScriptLine)   VALUES('GO')  
   
      DECLARE   PropCursor   CURSOR   FOR    
          SELECT   N'EXEC   sys.sp_addextendedproperty   @name=N'''   +   a.Name   +   N''',   @value=N'''   +   CONVERT(nvarchar,   a.Value)   +   N'''   ,@level0type=N''SCHEMA'',   @level0name=N'''   +  
                        USER_NAME(OBJECTPROPERTY(a.major_Id,   N'OwnerId'))   +   N''',   @level1type=N''TABLE'',   @level1name=N'''   +   b.Name   +   N''''   +  
                        N',   @level2type=N''COLUMN'',   @level2name=N'''   +   c.[Name]   +   ''''  
              FROM   sys.extended_properties   a  
                  LEFT   JOIN   sys.objects   b   ON   b.[Object_ID]   =   a.major_Id  
                  LEFT   JOIN   sys.syscolumns   c   ON   c.[ID]   =   a.major_Id   AND   c.ColID   =   a.Minor_ID  
          WHERE   a.major_Id   =   @ObjectID  
              AND   Minor_ID   <>   0  
      OPEN   PropCursor  
      FETCH   NEXT   FROM   PropCursor   INTO   @PropScript  
      WHILE   @@FETCH_STATUS   =   0  
      BEGIN  
          INSERT   INTO   @TableScript(ScriptLine)   VALUES(@PropScript)  
          INSERT   INTO   @TableScript(ScriptLine)   VALUES(N'GO')  
   
          FETCH   NEXT   FROM   PropCursor   INTO   @PropScript  
      END  
      CLOSE   PropCursor  
      DEALLOCATE   PropCursor  
      INSERT   INTO   @TableScript(ScriptLine)   VALUES('')  
   
      --擷取表外鍵  
      DECLARE   @ConstID   int,   @i   tinyint,   @keyCnt   tinyint,   @TempletSQL   nvarchar(400),   @SQLScript   nvarchar(500),  
                      @FColName   sysname,   @RColName   sysname,  
                      @ForeignLine   nvarchar(4000),   @ReferencesLine   nvarchar(4000),   @ReferencesAction   nvarchar(4000)  
      DECLARE   @ConstIDTable   table(ConstID   int)  
      SELECT  
          @FColName   =   '',  
          @RColName   =   '',  
          @TempletSQL   =    
              N'SELECT   @eFColName   =   ''[''   +   col_name(FkeyID,   Fkey%d)   +   '']'',   @eRColName   =   ''[''   +   col_name(RkeyID,   Rkey%d)   +   '']''   FROM   sys.sysreferences   WHERE   ConstID   =   @ConstID'  
   
      INSERT   INTO   @ConstIDTable  
          SELECT   ConstID   FROM   sys.sysreferences   WHERE   FKeyID   =   @ObjectID   OR   RKeyID   =   @ObjectID   ORDER   BY   FKeyID  
   
      WHILE   EXISTS(SELECT   *   FROM   @ConstIDTable)  
      BEGIN  
          SELECT   TOP   1   @ConstID   =   ConstID   FROM   @ConstIDTable  
          DELETE   FROM   @ConstIDTable   WHERE   ConstID   =   @ConstID  
   
          INSERT   INTO   @TableScript  
              SELECT   N'--ALTER   TABLE   [dbo].['   +   object_name(FKeyID)   +   ']   WITH   CHECK'  
                  FROM   sys.sysreferences  
              WHERE   ConstID   =   @ConstID  
          INSERT   INTO   @TableScript(ScriptLine)   VALUES('--     ADD'   +   CHAR(13)   +   CHAR(10))  
   
          SELECT   @ForeignLine   =   N'--         CONSTRAINT   ['   +   object_name(ConstID)   +   ']   FOREIGN   KEY(',    
                        @ReferencesLine   =   N'REFERENCES   [dbo].['   +   object_name(RKeyID)   +   ']   (',    
                        @ReferencesAction   =   CASE   b.Delete_Referential_Action   WHEN   0   THEN   N''    
                                                                                                                                  WHEN   1   THEN   N'ON   DELETE   Cascade'  
                                                                                                                                  WHEN   2   THEN   N'ON   DELETE   SET   NULL'  
                                                                                                                                  WHEN   3   THEN   N'ON   DELETE   SET   DEFAULT'  
                                                                END   +   '   '   +  
                                                                CASE   b.Delete_Referential_Action   WHEN   0   THEN   N''    
                                                                                                                                  WHEN   1   THEN   N'ON   UPDATE   Cascade'  
                                                                                                                                  WHEN   2   THEN   N'ON   UPDATE   SET   NULL'  
                                                                                                                                  WHEN   3   THEN   N'ON   UPDATE   SET   DEFAULT'  
                                                                END,  
                        @keyCnt   =   KeyCnt    
              FROM   sys.sysreferences   a  
                  LEFT   JOIN   sys.foreign_keys   b   ON   a.ConstID   =   b.Object_ID  
          WHERE   a.ConstID   =   @ConstID  
           
          --取欄位  
          SET   @i   =   1  
          WHILE   @i   <=   @keyCnt  
          BEGIN  
              SET   @SQLScript   =   REPLACE(@TempletSQL,   '%d',   CONVERT(nvarchar,   @i))  
              EXEC   sp_executesql   @stmt   =   @SQLScript,   @params=   N'@eFColName   sysname   output,   @eRColName   sysname   output,   @ConstID   int',    
                                                    @eFColName   =   @FColName   output,   @eRColName   =   @RColName   output,   @ConstID   =   @ConstID  
              print   @SQLScript  
              SET   @ForeignLine   =   @ForeignLine   +   CASE   WHEN   @i   >   1   THEN   ',   '   ELSE   ''   END   +   @FColName  
              SET   @ReferencesLine   =   @ReferencesLine   +   CASE   WHEN   @i   >   1   THEN   ',   '   ELSE   ''   END   +   @RColName  
              SET   @i   =   @i   +   1  
          END  
   
          INSERT   INTO   @TableScript(ScriptLine)   VALUES(@ForeignLine   +   N')   '   +   @ReferencesLine   +   N')')  
          IF   @ReferencesAction   <>   ''  
              INSERT   INTO   @TableScript(ScriptLine)   VALUES(@ReferencesAction)  
          INSERT   INTO   @TableScript(ScriptLine)   VALUES(N'GO')  
      END  
      --返回表的建立指令碼  
      SELECT   ScriptLine   FROM   @TableScript  
      SET   NOCOUNT   OFF