Goif object_ID(‘[up_CreateTable]‘) is not null	Drop Procedure [up_CreateTable]Go/* 生成建表脚本(V4.0)  Andy 2017-3-28 */Create Proc up_CreateTable(    @objectList nvarchar(max)=null)as--With ENCRYPTION /*  参数说明：    @objectList 对象列表，对象之间使用","隔开        存储过程生成的建表脚本，包含Column,Constraint,Index,extended_properties 	Modify: andy 2017-3-28 增加了扩展属性*/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,-1,-1, @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		where a.referenced_object_id<>a.parent_object_id    )    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‘没有可以生产脚本的表!‘		Raiserror (50001,-1,-1, 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)         		--extended_properties Andy 2017-3-28 添加扩展属性		insert into #Columns 		    select b.object_id,					a.major_id as Seq,					4 as DefinitionType,					case a.minor_id 						when 0 then ‘execute sp_addextendedproperty ‘‘MS_Description‘‘,‘‘‘+convert(nvarchar(max),a.value)+‘‘‘, ‘‘user‘‘, ‘‘dbo‘‘, ‘‘table‘‘, ‘+quotename(b.name,‘‘‘‘)						else ‘execute sp_addextendedproperty ‘‘MS_Description‘‘,‘‘‘+convert(nvarchar(max),a.value)+‘‘‘, ‘‘user‘‘, ‘‘dbo‘‘, ‘‘table‘‘, ‘+quotename(b.name,‘‘‘‘)+‘,‘‘column‘‘,‘+quotename(c.name,‘‘‘‘)						end 				from  sys.extended_properties a 					inner join #Objects b on b.object_id=a.major_id					inner join sys.columns c on c.object_id=b.object_id						and c.column_id=a.minor_id				where a.class=1		 	         --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 */        --Modify Nr:20100510 Start        Declare @MaxRow int                if object_id(‘tempdb..#Print‘) Is Not Null            Drop Table #Print         Create Table #Print(Row int Identity(1,1) Primary Key,Sql nvarchar(4000))                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                                Insert Into #Print(Sql)                Select @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                       Insert Into #Print(Sql)            Select @Tab+@Tab+definition+‘,‘+@Enter                From #Columns                 Where object_id=@objectid                         And DefinitionType=1                Group By Seq,definition                Order By Seq                                   Set @MaxRow=Scope_identity()            Update #Print                Set Sql=Substring(sql,1,Len(sql)-3)+@Enter+@Tab+‘)‘+@Enter                Where Row=@MaxRow                   Insert Into #Print(Sql)            Select @Tab+definition+@Enter                From #Columns                 Where object_id=@objectid                         And DefinitionType>1                Group By DefinitionType,Seq,definition                Order By Seq                        if @@ROWCOUNT >0                 Set @MaxRow=Scope_identity()             Update #Print                Set Sql= Substring(Sql,1,Len(Sql)-2)+@Enter+‘End‘                Where Row=@MaxRow                         Set @id=@id+1        End                Set @id=1        While @id>0        Begin            Set @sql=‘‘            Select @sql=sql From #Print Where row=@id                        If @sql>‘‘            Begin                Print @sql                Set @id=@id+1            end            Else                Set @id=0        End                --Modify Nr:20100510 End                        Print ‘Go‘     Drop Table #Columns    Drop Table #Objects  Go