在之前的，之所以发生”。。。打印@Sql的时候由于字段过多，截断了@sql“的问题，是由于Print打印Unicode 字符串，支持最大长度为4000个字符，对非Unicode 字符串，最大为8000个字符。
针对这一Bug,我修正中的Print部分。
代码：
use Lgdinnr Go If object_id ( ' up_CreateTable ' ) Is Not Null Drop Proc up_CreateTable Go /* 生成建表脚本(V3.0) OK_008 2010-5-10 */ 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 */ -- 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
注：本脚本可以在SQL2005/SQL2008环境上执行。