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  
/*  參數(shù)說(shuō)明：  
    @objectList 對(duì)象列表，對(duì)象之間使用","隔開(kāi)  
      
    改存儲(chǔ)過(guò)程生成的建表腳本，包含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='發(fā)現(xiàn)無(wú)效的表名: '+@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'沒(méi)有可以生產(chǎ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 aa1.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 aa1.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 aa1.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 aa1.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 aa1.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 aa1.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 aa1.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+'/* 創(chuàng)建表結(jié)構(gòu) 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 

原文標(biāo)題：數(shù)據(jù)庫(kù)表的基本信息，你知道嗎？

鏈接：http://www.cnblogs.com/kerrycode/archive/2010/09/03/1816611.html