生成建表脚本(V2.0)

创建表脚本的存储过程代码（该脚本适用于MSSQL2005/2008）：

use  test
Go
If   object_id ( ' up_CreateTable ' )  Is   Not   Null
     Drop   Proc  up_CreateTable
Go
/*  生成建表脚本(V2.0)  OK_008 2009-5-18 */
Create   Proc  up_CreateTable
(
     @objectList   nvarchar ( max ) = null
)
-- With ENCRYPTION
As
/*   参数说明：
    @objectList 对象列表，对象之间使用","隔开
    
    改存储过程生成的建表脚本，包含Column,Constraint,Index
*/
Set  Nocount  On
     Declare   @sql   nvarchar ( max ),
             @objectid   int ,
             @id   int ,
             @Rowcount   int ,
             @ObjectName  sysname,
             @Enter   nvarchar ( 2 ),
             @Tab   nvarchar ( 2 )

     Select       @Enter = Char ( 13 ) + Char ( 10 ),
             @Tab = Char ( 9 )    

     Declare   @Tmp   Table (name sysname)
    
     If   @objectList > ''
     Begin
         Set   @sql = ' Select N ''' + Replace ( @objectList , ' , ' , '''  Union All Select N ''' ) + ''''
         Insert   Into   @Tmp  (name)  Exec ( @sql )

         Set   @sql = null
         Select   @sql = Isnull ( @sql + ' , ' , '' ) + name 
             From   @Tmp   As  a
             Where   Not   Exists ( Select   1   From  sys.objects  Where  type = ' U '   And  name = a.name)

         If   @sql > ''
         Begin
             Set   @sql = ' 发现无效的表名:  ' + @sql
             Raiserror   50001   @sql
             Return ( 1 )
         End
     End

     If   object_id ( ' tempdb..#Objects ' )  Is   Not   Null
         Drop   Table  #Objects
    
     If   object_id ( ' tempdb..#Columns ' )  Is   Not   Null
         Drop   Table  #Columns    
    
     Create   Table  #Objects(id  int   Identity ( 1 , 1 )  Primary   Key , object_id   int ,name sysname)

     
    ; With  t  As  
    (
     Select   Object_id , Convert ( int , 0 )  As  LevelNo,name  As   object_name
             From  sys.objects a 
             Where  Type = ' U '   And  is_ms_shipped = 0   And   Not   Exists ( Select   1   From  sys.foreign_keys  Where  referenced_object_id = a. object_id )
     Union   All
     Select  a.referenced_object_id  As   Object_id ,b.LevelNo + 1   As  LevelNo,c.name  As   object_name
         From  sys.foreign_keys a 
             Inner   Join  t b  On  b. object_id = a.parent_object_id
             Inner   Join  sys.objects c  On  c. object_id = a.referenced_object_id  And  c.is_ms_shipped = 0
    )
     Insert   Into  #Objects( object_id ,name)
         Select  a. object_id , object_name
             From  t a
             Where      Not   Exists ( Select   1   From  t  Where   object_id = a. object_id   And  LevelNo > a.LevelNo)  And
                     Not   Exists ( Select   1   From  sys.extended_properties  Where  major_id = a. object_id   And  minor_id = 0   And  class = 1   And  Name = N ' microsoft_database_tools_support ' )
                     And  ( Exists ( Select   1   From   @Tmp   Where  name = a. object_name )  Or   Not   Exists ( Select   1   From   @Tmp ))
             Group   By   object_id , object_name ,LevelNo
             Order   By  LevelNo  Desc

     Set   @Rowcount = @@Rowcount
     If   @Rowcount = 0
     Begin
         Raiserror   50001  N ' 没有可以生产脚本的表! '
         Return ( 1 )
     End

     -- Column
     Select     a. object_id ,
            a.column_id  As  Seq,
             Cast ( 1   As   tinyint )  As  DefinitionType,
             Quotename (a.name) + Char ( 32 ) +  c.name  +
             Case  
                 When  a.user_type_id  In  ( 231 , 239 )  Then   ' ( ' + Case  a.max_length  When   - 1   Then   ' Max '   Else   Rtrim (a.max_length / 2 )  End   + ' ) '
                 When  a.user_type_id  In  ( 62 , 165 , 167 , 173 , 175 )  Then   ' ( ' + Case  a.max_length  When   - 1   Then   ' Max '   Else   Rtrim (a.max_length)  End + ' ) '
                 When  a.user_type_id  In  ( 106 , 108 )  Then   ' ( ' + Rtrim (a. [ precision ] ) + ' , ' + Rtrim (a.scale) + ' ) '  
                 Else   ''
             End
             +   Char ( 32 ) +
             Case  a.is_rowguidcol  When   1   Then   ' Rowguidcol  '   Else   ''   End   +
             Case  a.is_identity  When   1   Then   ' Identity( ' + Cast (d.seed_value  As   nvarchar ( 10 )) + ' , ' + Cast (d.increment_value  As   nvarchar ( 10 )) + ' )  '   Else   ''   End +  
             Case  a.is_nullable  When   1   Then   ' Null  '   Else   ' Not Null  '   End +
             Isnull ( ' Constraint  ' + Quotename (e.name) + '  Default( ' + e.definition + ' ) ' , '' )  As  definition

             Into  #Columns
         From  sys.columns  As  a
             Inner   Join  #Objects  As  b  On  b. object_id = a. object_id
             Inner   Join  sys.types  As  c  On  c.user_type_id = a.user_type_id
             Left   Outer   Join  sys.identity_columns  As  d  On  d. object_id = a. object_id   And  d.column_id = a.column_id  And  a.is_identity = 1
             Left   Outer   Join  sys.Default_constraints  As  e  On  e. object_id = a.default_object_id  And  e.parent_column_id = a.column_id

         Create   Nonclustered   Index  IX_#Columns_object_id  On  #Columns( object_id   Asc )

         -- Constraint
         Insert   Into  #Columns
    
         Select     a.parent_object_id  As   object_id ,
                Row_number()  Over (Partition  By  a.parent_object_id  Order   By   Case  a.type  When   ' PK '   Then   1   When   ' C '   Then   2   Else   3   End ) As  Seq,
                 2   As  DefinitionType,
                 ' Alter Table  ' + Quotename ( object_name (a.parent_object_id))  + '  Add Constraint  ' + Quotename (a.name) +
                 Case  a.type 
                     When   ' PK '   Then   '  Primary Key  ' + Case   When   Exists ( Select   1   From  sys.indexes  Where   object_id = a.parent_object_id  And  is_primary_key = 1   And  type = 1 )  Then  N ' Clustered  '   Else  N ' Nonclustered  '   End +
                                                 ' ( ' + Stuff (( Select   ' , ' + Quotename (c1.Name) + Case  a1.is_descending_key  When   1   Then   '  Desc '   Else   '  Asc '   End
                                                         From  sys.index_columns  As  a1
                                                             Inner   Join  sys.indexes  As  b1  On  b1. object_id = a1. object_id   And  b1.index_id = a1.index_id  And  b1.is_primary_key = 1
                                                             Inner   Join  sys.columns  As  c1  On  c1. object_id = a1. object_id   And  c1.column_id = a1.column_id
                                                         Where  a1. object_id = a.parent_object_id 
                                                         For  Xml Path( '' )
                                                    ), 1 , 1 , '' ) +
                                                 ' ) '
                     When   ' F '   Then   '  Foreign Key ( ' + Stuff (( Select   ' , ' + Quotename (b1.Name)
                                                         From  sys.foreign_key_columns  As  a1
                                                             Inner   Join  sys.columns  As  b1  On  b1. object_id = a1.parent_object_id  And  b1.column_id = a1.parent_column_id
                                                         Where  a1.constraint_object_id = a. object_id  
                                                         Order   By  a1.constraint_column_id
                                                         For  Xml Path( '' )
                                                    ), 1 , 1 , '' ) +
                                                 ' ) References  ' + ( Select   Quotename ( object_name (referenced_object_id))  From   sys.foreign_keys  Where   object_id = a. object_id ) +
                                                 '  ( '
                                                     + Stuff (( Select   ' , ' + Quotename (b1.Name)
                                                         From  sys.foreign_key_columns  As  a1
                                                             Inner   Join  sys.columns  As  b1  On  b1. object_id = a1.referenced_object_id  And  b1.column_id = a1.referenced_column_id
                                                         Where  a1.constraint_object_id = a. object_id  
                                                         Order   By  a1.constraint_column_id
                                                         For  Xml Path( '' )
                                                    ), 1 , 1 , '' ) +
                                                 ' ) '
                     When   ' UQ '   Then   '  Unique ' + ( Select   Case  a1.type  When   1   Then   '  Clustered '   Else   '  Nonclustered '   End
                                                     From  sys.indexes  As  a1
                                                     Where  a1. object_id = a.parent_object_id 
                                                                 And   Exists ( Select   1   From  sys.key_constraints  Where   object_id = a. object_id   And  parent_object_id = a1. object_id   And  unique_index_id = a1.index_id)
                                               ) +                         
                                                 ' ( ' + Stuff (( Select   ' , ' + Quotename (c1.Name) + Case  a1.is_descending_key  When   1   Then   '  Desc '   Else   '  Asc '   End
                                                         From  sys.index_columns  As  a1
                                                             Inner   Join  sys.indexes  As  b1  On  b1. object_id = a1. object_id   And  b1.index_id = a1.index_id  And  b1.is_unique_constraint = 1
                                                             Inner   Join  sys.columns  As  c1  On  c1. object_id = a1. object_id   And  c1.column_id = a1.column_id
                                                         Where  a1. object_id = a.parent_object_id 
                                                                 And   Exists ( Select   1   From  sys.key_constraints  Where   object_id = a. object_id   And  parent_object_id = a1. object_id   And  unique_index_id = a1.index_id)
                                                         For  Xml Path( '' )
                                                    ), 1 , 1 , '' ) +
                                                 ' ) '
                     When   ' C '   Then   '  Check '   + ( Select  definition  From  sys.check_constraints  Where   object_id = a. object_id )
                     Else   ''
                 End   As  definition

             From  sys.objects  As  a
             Where  a.type  In ( ' PK ' , ' F ' , ' C ' , ' UQ ' )
                     And   Exists ( Select   1    From  #Objects  Where   object_id = a.parent_object_id)

         -- Index
         Insert   Into  #Columns
         Select     a. object_id  ,
                a.index_id  As  Seq,
                 3   As  DefinitionType,
                 ' Create  ' + Case  a.is_unique  When   1   Then   ' Unique  '   Else   ''   End +
                 Case  a.type  When   1   Then   ' Clustered  '   Else   ' Nonclustered  '   End +
                 ' Index  ' + Quotename (a.name) + '  On  ' + Quotename (b.name) +
                                         '  ( ' + Stuff (( Select   ' , ' + Quotename (b1.Name) + Case  a1.is_descending_key  When   1   Then   '  Desc '   Else   '  Asc '   End
                                                         From  sys.index_columns  As  a1
                                                             Inner   Join  sys.columns  As  b1  On  b1. object_id = a1. object_id   And  b1.column_id = a1.column_id
                                                         Where  a1. object_id = a. object_id   And  a.index_id = a1.index_id  And  a1.is_included_column = 0
                                                         For  Xml Path( '' )
                                                    ), 1 , 1 , '' ) +
                                         ' ) ' +
                                         Isnull ( '  Include( ' + Stuff (( Select   ' , ' + Quotename (b1.Name)
                                                         From  sys.index_columns  As  a1
                                                             Inner   Join  sys.columns  As  b1  On  b1. object_id = a1. object_id   And  b1.column_id = a1.column_id
                                                         Where  a1. object_id = a. object_id   And  a.index_id = a1.index_id  And  a1.is_included_column = 1
                                                         For  Xml Path( '' )
                                                    ), 1 , 1 , '' ) +
                                         ' ) ' , '' )
                     As  definition
             From  sys.indexes  As  a
                 Inner   Join  #Objects  As  b  On  b. object_id = a. object_id
             Where  a.type > 0
                     And   Not   Exists ( Select   1   From  sys.key_constraints  Where  parent_object_id = a. object_id   And  unique_index_id = a.index_id)

        

         -- Print
        

         Print   ' Use  ' + Quotename ( db_name ()) + @Enter + ' Go ' + @Enter + ' /* 创建表结构 Andy  ' + Convert ( nvarchar ( 10 ), Getdate (), 120 ) + ' */ ' + @Enter

         Set   @id = 1
         While   @id <= @Rowcount
         Begin
             Select   @objectid = object_id , @ObjectName = name  From  #Objects  Where  id = @id

             Set   @Sql = @Enter + ' --( ' + Rtrim ( @id ) + ' / ' + Rtrim ( @Rowcount ) + ' )  ' + @ObjectName + @Enter + ' If object_id( ''' + Quotename ( @ObjectName ) + ''' ) Is Null ' + @Enter + ' Begin ' + @Enter + @Tab +
                     ' Create Table  ' + Quotename ( @ObjectName ) + @Enter + @Tab + ' ( ' + @Enter
             Select   @Sql = @Sql + @Tab + @Tab + definition + ' , ' + @Enter
                 From  #Columns 
                 Where   object_id = @objectid  
                         And  DefinitionType = 1
                 Group   By  Seq,definition
                 Order   By  Seq
             Set   @sql = Substring ( @sql , 1 , Len ( @sql ) - 3 ) + @Enter + @Tab + ' ) ' + @Enter
             Select   @Sql = @Sql + @Tab + definition + @Enter
                 From  #Columns 
                 Where   object_id = @objectid  
                         And  DefinitionType > 1
                 Group   By  DefinitionType,Seq,definition
                 Order   By  Seq

             Print   Substring ( @sql , 1 , Len ( @sql ) - 2 ) + @Enter + ' End '
             Set   @id = @id + 1
         End

         Print   ' Go '

     Drop   Table  #Columns
     Drop   Table  #Objects
Go

 

调用方法：

 

Use  test
Go
Exec  up_CreateTable  ' SalaryItem,Salary '

 

 生成脚本：

 

Use   [ test ]
Go
/*  创建表结构 Andy 2009-05-18 */

-- (1/2) SalaryItem
If   object_id ( ' [SalaryItem] ' )  Is   Null
Begin
     Create   Table   [ SalaryItem ]
    (
         [ ID ]   smallint   Identity ( 1 , 1 )  Not   Null  ,
         [ ItemType ]   smallint   Not   Null  ,
         [ Name ]   nvarchar ( 50 )  Null  
    )
     Alter   Table   [ SalaryItem ]   Add   Constraint   [ PK_SalaryItem ]   Primary   Key   Clustered  ( [ ID ]   Asc )
     Alter   Table   [ SalaryItem ]   Add   Constraint   [ FK_SalaryItem_SysSalaryItemTypeMTR ]   Foreign   Key  ( [ ItemType ] )  References   [ SysSalaryItemTypeMTR ]  ( [ ID ] )
End

-- (2/2) Salary
If   object_id ( ' [Salary] ' )  Is   Null
Begin
     Create   Table   [ Salary ]
    (
         [ ID ]   int   Identity ( 1 , 1 )  Not   Null  ,
         [ EmployeeID ]   int   Not   Null  ,
         [ SalaryMonth ]   datetime   Null  ,
         [ SalaryItemID ]   smallint   Not   Null  ,
         [ Amount ]   money   Null  
    )
     Alter   Table   [ Salary ]   Add   Constraint   [ PK_Salary ]   Primary   Key   Clustered  ( [ ID ]   Asc )
     Alter   Table   [ Salary ]   Add   Constraint   [ FK_Salary_Employee ]   Foreign   Key  ( [ EmployeeID ] )  References   [ Employee ]  ( [ ID ] )
     Create   Nonclustered   Index   [ IX_Salary_EmployeeID ]   On   [ Salary ]  ( [ EmployeeID ]   Asc )
     Alter   Table   [ Salary ]   Add   Constraint   [ FK_Salary_SalaryItem ]   Foreign   Key  ( [ SalaryItemID ] )  References   [ SalaryItem ]  ( [ ID ] )
     Create   Nonclustered   Index   [ IX_Salary_SalaryMonth_EmployeeID ]   On   [ Salary ]  ( [ SalaryMonth ]   Asc , [ EmployeeID ]   Asc )
End
Go

 

 （完）