超级有用的SQL语句(分析SQL SERVER 数据库表结构专用)

超级 有用 的SQL 语句 （用于SQL SERVER 服务器） 超级 有用 的SQL 语句 ，执行后返回的列分别是：表名、列名、列类型、列长度、列描述、是否主键， 语句 如下： ( 分析 SQL SERVER 数据库 表 结构 专用 ) 以下是SQLSERVER2000 Select Sysobjects.Name As 表

超级有用的SQL语句 （用于SQL SERVER 服务器）
超级有用的SQL语句 ，执行后返回的列分别是：表名、列名、列类型、列长度、列描述、是否主键，语句如下：

(分析SQL SERVER 数据库表结构专用)

 

以下是SQLSERVER2000

---

Select Sysobjects.Name As 表名,
       Syscolumns.Name As 列名,
       Systypes.Name As 列类型,
       Syscolumns.Length As 列长度,
       Isnull(Sysproperties.Value, Syscolumns.Name) As 列说明,
       Case
         When Syscolumns.Name In
              (Select 主键 = A.Name
                 From Syscolumns A
                Inner Join Sysobjects B On A.Id = B.Id
                                       And B.Xtype = 'U'
                                       And B.Name <> 'Dtproperties'
                Where Exists (Select 1
                         From Sysobjects
                        Where Xtype = 'Pk'
                          And Name In
                              (Select Name
                                 From Sysindexes
                                Where Indid In
                                      (Select Indid
                                         From Sysindexkeys
                                        Where Id = A.Id
                                          And Colid = A.Colid)))
                  And B.Name = Sysobjects.Name) Then
          1
         Else
          0
       End As 是否主键
  From Sysobjects, Systypes, Syscolumns
  Left Join Sysproperties On (Syscolumns.Id = Sysproperties.Id And
                             Syscolumns.Colid = Sysproperties.Smallid)
 Where (Sysobjects.Xtype = 'U' Or Sysobjects.Xtype = 'V')
   And Sysobjects.Id = Syscolumns.Id
   And Systypes.Xtype = Syscolumns.Xtype
   And Systypes.Name <> 'Sysname'
   And Sysobjects.Name Like '%'
 Order By Sysobjects.Name, Syscolumns.Colid

 

---

以下是SQLSERVER 2005版本的语句

---

选择 Sysobjects.Name 作为表名，
       Syscolumns.Name 作为列名，
       Systypes.Name 作为列类型，
       Syscolumns.Length 作为列长度，
       Isnull(sys.extended_properties. Value, Syscolumns.Name) As 列说明,
       Case
         When Syscolumns.Name In
              (选择主键 = A.Name
                 来自 Syscolumns A
                 在 A.Id 上内连接 Sysobjects B = B.Id
                                       和 B.Xtype = 'U'
                                       和 B.Name 'Dtproperties'
                存在的地方（选择 1
                         来自 Sysobjects
                        其中 Xtype = 'Pk'
                         并输入名称
                            （选择名称
                                 来自系统索引
                                Indid 在哪里
                                   （选择 Indid
                                         来自 Sysindexkeys
                                        其中 Id = A.Id
                                           且 Colid = A .Colid)))
                 且 B.Name = Sysobjects.Name) 然后
          1
         Else
          0
       End As 是否主键
  来自 Sysobject s、系统类型、系统列
  左加入 sys.extended_properties On (Syscolumns.Id = sys.extended_properties.major_id And
                            Syscolumns.Colid = sys.extended_properties.minor_id)
 其中 (Sysobjects.Xtype = 'U' 或 Sysobjects.Xtype =“V”）
   且 Sysobjects.Id = Syscolumns.Id
   且 Systypes.Xtype = Syscolumns.Xtype
   且 Systypes.Name 'Sysname'
   和 Sysobjects.Name 类似 'XJY%'
 按 Sysobjects.Name、Syscolumns.Colid

排序