select name from sysobjects where xtype='U'
下面的语句执行后返回的列分别是:表名、列名、列类型、列长度、列描述、是否主键,语句如下:
Select
Sysobjects.Name
As
Tb_name, Syscolumns.Name
As
Col_name
, Systypes.Name
As
Col_type, Syscolumns.Length
As
Col_len,
Isnull
(Sysproperties.Value,Syscolumns.Name)
As
Col_memo,
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 Is_key
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
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 Is_key
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