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

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,'PRECISION') AS Length,-- 字符串长度 ISNULL(COLUMNPROPERTY(a.id,'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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!