`
ckangtai
  • 浏览: 974 次
  • 性别: Icon_minigender_1
  • 来自: 广州
最近访客 更多访客>>
社区版块
存档分类
最新评论

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

阅读更多
超级有用的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版本的语句



--------------------------------------------------------------------------------


Select Sysobjects.Name As 表名,
       Syscolumns.Name As 列名,
       Systypes.Name As 列类型,
       Syscolumns.Length As 列长度,
       Isnull(sys.extended_properties.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 sys.extended_properties On (Syscolumns.Id = sys.extended_properties.major_id And
                             Syscolumns.Colid = sys.extended_properties.minor_id)
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 'XJY%'
Order By Sysobjects.Name, Syscolumns.Colid
分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics