取得單個資料庫下所有表的信息

 

Create   table  #TableTemp1( TableName  varchar ( 40 ), 
                          TableDescription  char ( 200 ),
                          TableKey  varchar ( 30 ), 
                          ColumnNo  SmallInt , 
                          ColumnName  varchar ( 30 ),  
                          ColType  varchar ( 30 ), 
                          ColBytes  varchar ( 30 ), 
                          ColLength  varchar ( 30 ), 
                          ColDigital  varchar ( 30 ), 
                          ColDescription  varchar ( 200 ),
                          ColDefaultValue  varchar ( 30 ),
                          ColIdentity  varchar ( 30 ),
                          ColIsNull   varchar ( 30 ) ) 

    Declare   @strTableName   Varchar ( 40 )

   Declare  Cur_TableName  CurSor   For     
      Select  Name TableName
         From  sysObjects
        Where  Status  > 0
          And  Xtype  =   ' U '
          And   Not  (name  Like   ' %LOG ' )

    -- 打開游標
    Open  Cur_TableName
       WHILE   1 = 1
        BEGIN
              FETCH   NEXT   FROM  Cur_TableName  Into
                    @strTableName

              IF  ( NOT   @@FETCH_STATUS   =   0 )
                  BREAK
             
            Insert   Into  #TableTemp1 
                  SELECT   TableName = case  
                                      when  syscolumns.colorder = 1   then  sysobjects.name 
                                      else   ''  
                                     end , 
                         TableDescription = case  
                                             when  syscolumns.colorder = 1   then   Convert ( varchar ( 200 ), isnull (syspropertiesExtr.value, '' )) 
                                             else   ''  
                                            end , 
                         TableKey = case  
                                     when   exists ( SELECT   1  
                                                    FROM  sysobjects 
                                                   Where  xtype = ' PK '   and  name  in  ( SELECT  name 
                                                                                    FROM  sysindexes 
                                                                                   WHERE  indid  in ( SELECT  indid 
                                                                                                    FROM  sysindexkeys 
                                                                                                   WHERE  id  =  syscolumns.id 
                                                                                                   AND  colid = syscolumns.colid)))  then   ' √ '  
                                      else   ''  
                                     end , 
                         ColumnNo = syscolumns.colorder, 
                         ColumnName = syscolumns.name, 
                         ColType = systypes.name, 
                         ColBytes = syscolumns.length, 
                         ColLength = COLUMNPROPERTY (syscolumns.id,syscolumns.name, ' PRECISION ' ), 
                         ColDigital = isnull ( COLUMNPROPERTY (syscolumns.id,syscolumns.name, ' Scale ' ), 0 ), 
                         ColDescription = Convert ( varchar ( 20 ), isnull (sysproperties. [ value ] , '' )) , 
                         ColDefaultValue = ' Default '   +   isnull (syscomments. text , '' ),
                         ColIdentity  = case  
                                         when   COLUMNPROPERTY ( syscolumns.id,syscolumns.name, ' IsIdentity ' ) = 1   then   ' √ '
                                         else   ''  
                                        end ,
                         ColIsNull = case  
                                      when  syscolumns.isnullable = 1   then   ' √ '
                                      else   ''  
                                    end  
                   FROM  syscolumns syscolumns   -- a 
                       Left   Join  systypes systypes  --  b 
                         On  syscolumns.xtype = systypes.xusertype 
                      Inner   Join  sysobjects sysobjects  -- d 
                         On  syscolumns.id = sysobjects.id  
                        And  sysobjects.xtype = ' U '  
                        And  sysobjects.name <> ' dtproperties '  
                       Left   Join  syscomments syscomments  -- e 
                         On  syscolumns.cdefault = syscomments.id 
                       Left   Join  sysproperties sysproperties   -- g 
                         On  syscolumns.id = sysproperties.id 
                        And  syscolumns.colid = sysproperties.smallid   
                       Left   Join  sysproperties syspropertiesExtr  -- f 
                         On  sysobjects.id = syspropertiesExtr.id 
                        And  sysproperties.smallid = 0  
                   Where  sysobjects.name = @strTableName     -- 如果只查詢指定表,加上此條件 
                     Order   By  syscolumns.id
                            ,syscolumns.colorder 

             
        END

     CLOSE  Cur_TableName
     DEALLOCATE  Cur_TableName

     
    
      select   *  
         From  #TableTemp1 
          
       
      Drop   table  #TableTemp1