当前位置:Gxlcms > mysql > 超级有用的SQL语句(分析SQLSERVER数据库表结构专用)

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

时间:2021-07-01 10:21:17 帮助过:91人阅读

超级 有用 的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

人气教程排行