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

本文介绍了如何使用SQL查询数据库中的所有数据库名称、某个数据库内的所有表名以及详细的表结构信息。通过这些查询语句，可以帮助开发者更好地理解和操作数据库。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.查询数据库中的所有数据库名：
SELECT Name FROM Master..SysDatabases ORDER BY Name
2.查询某个数据库中所有的表名：
SELECT Name FROM SysObjects Where XType='U' ORDER BY Name
3.查询表结构信息：

SELECT d.name AS TableName,  
    A.ColOrder,
    A.Name AS ColName,
    (CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity')=1 THEN 1 ELSE 0 END) IsIdentity, 
    (CASE WHEN (SELECT COUNT(1) FROM SYSOBJECTS WHERE (name IN (SELECT name FROM SYSINDEXES WHERE (id = a.id) AND (indid IN  (SELECT indid FROM SYSINDEXKEYS WHERE (id = a.id) AND (colid in (SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name))))))) AND (xtype = 'PK'))>0 THEN 1 ELSE 0 END) IsKey,
    B.Name AS ColDataType,                                          -- 数据类型
    A.Length AS Bytes,                                              -- 占用存储空间
    COLUMNPROPERTY(a.id,a.name,'PRECISION') AS Length,              -- 字符串长度
    ISNULL(COLUMNPROPERTY(a.id, a.name,'Scale'),0) AS ScaleSize,    -- 小数位数
    A.IsNullable,                                                   -- 允许空
    ISNULL(e.text,'') AS DefaultValue,
    ISNULL(g.[value], ' ') AS Remark
FROM  SYSCOLUMNS A 
LEFT JOIN SYSTYPES B ON a.xtype=b.xusertype  
INNER JOIN SYSOBJECTS D ON a.id=d.id and d.xtype='U' and d.name<>'dtproperties' 
LEFT JOIN SYSCOMMENTS E ON a.cdefault=e.id  
LEFT JOIN SYS.EXTENDED_PROPERTIES G ON a.id=g.major_id AND a.colid=g.minor_id
LEFT JOIN SYS.EXTENDED_PROPERTIES F ON d.id=f.class and f.minor_id=0
WHERE B.NAME IS NOT NULL
--WHERE d.name='要查询的表' --如果只查询指定表,加上此条件
ORDER BY A.id, a.ColOrder