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

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

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