2023年7月13日发(作者：)

平常心

SQLSERVER如何获取一个数据库中的所有表的名称、一个表中所有字段的名称

1.查询数据库中的所有数据库名：

SELECT Name FROM Master..SysDatabases ORDER BY Name

2.查询某个数据库中所有的表名：

SELECT Name FROM SysObjects Where XType='U' ORDER BY Name

3.查询表结构信息：

1 SELECT (case when er=1 then else null end) 表名,

2 er 字段序号, 字段名,

3 (case when COLUMNPROPERTY( ,,'IsIdentity')=1 then '√'else '' end)

标识,

4 (case when (SELECT count(*) FROM sysobjects

5 WHERE (name in (SELECT name FROM sysindexes

6 WHERE (id = ) AND (indid in

7 (SELECT indid FROM sysindexkeys

8 WHERE (id = ) AND (colid in

9 (SELECT colid FROM syscolumns WHERE (id = ) AND (name = )))))))

10 AND (xtype = 'PK'))>0 then '√' else '' end) 主键, 类型, 占用字节数,

11 COLUMNPROPERTY(,,'PRECISION') as 长度,

12 isnull(COLUMNPROPERTY(,,'Scale'),0) as 小数位数,(case when

able=1 then '√'else '' end) 允许空,

13 isnull(,'') 默认值,isnull(g.[value], ' ') AS [说明]

14 FROM syscolumns a

15 left join systypes b on =ype

16 inner join sysobjects d on = and ='U' and <>'dtproperties'

17 left join syscomments e on lt=

18 left join ed_properties g on =_id AND =_id

19 left join ed_properties f on = and _id=0

20 where is not null

21 --WHERE ='要查询的表' --如果只查询指定表,加上此条件

22 order by ,er

对象类型.可以是下列对象类型之一

AF = Aggregate function (CLR)

C = CHECK constraint : check约束

D = Default or DEFAULT constraint：默认值约束

F = FOREIGN KEY constraint ：外键约束

L = Log ：日志 平常心

FN = Scalar function: 标量函数。

FS = Assembly (CLR) scalar-function：CLR的标量函数（SQL Server 2005以上）

FT = Assembly (CLR) table-valued function：CLR表值函数

IF = In-lined table-function ：内联表函数

IT = Internal table：外部表。

P = Stored procedure ：存储过程。

PC = Assembly (CLR) stored-procedure：clr存储过程。

PK = PRIMARY KEY constraint (type is K) ：主键约束

RF = Replication filter stored procedure

S = System table ：系统表

SN = Synonym

SQ = Service queue：服务队列

TA = Assembly (CLR) DML trigger：clr dml触发器。

TF = Table function ：表函数。

TR = SQL DML Trigger ：SQL DML触发器

TT = Table type：表类型

U = User table ：用户表

UQ = UNIQUE constraint (type is K) ：唯一约束

V = View ：视图

X = Extended stored procedure：扩展存储过程

xtype 类型

34 image

35 text

36 uniqueidentifier

48 tinyint

52 smallint

56 int

58 smalldatetime

59 real

60 money

61 datetime

62 float

98 sql_variant

99 ntext

104 bit

106 decimal 平常心

108 numeric

122 smallmoney

127 bigint

165 varbinary

167 varchar

173 binary

175 char

189 timestamp

231 sysname

231 nvarchar

239 nchar

1.获取所有数据库名:

SELECT Name FROM Master..SysDatabases ORDER BY Name

2.获取所有表名:

SELECT Name FROM DatabaseName..SysObjects Where XType='U' ORDER BY Name

XType='U':表示所有用户表;

XType='S':表示所有系统表;

3.获取所有字段名:

SELECT Name FROM SysColumns WHERE id=Object_Id('TableName')

access :

如果表存在，如何得到这个表？

SELECT * FROM MSysObjects where name='ExamResultTime ' and type=1

and flags=0

//得到该数据库中所有的表名

SELECT name FROM MSysObjects where type=1 and flags=0