T-SQL获取数据库中的Table表结构信息，Column结构信息

weixin_30883777

于 2014-04-11 13:06:00 发布

阅读量164

点赞数

CC 4.0 BY-SA版权

文章标签：数据库 php

原文链接：http://www.cnblogs.com/dukecheng/p/3658500.html

本文提供两种SQL查询方式来获取数据库中所有用户表及其字段信息，包括表名、字段名、数据类型、字段长度等详细属性。

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

Using OBJECT CATALOG VIEWS

SELECT  T.NAME AS [TABLE NAME], C.NAME AS [COLUMN NAME], P.NAME AS [DATA TYPE], P.MAX_LENGTH AS[SIZE],   CAST(P.PRECISION AS VARCHAR) +'/'+ CAST(P.SCALE AS VARCHAR) AS [PRECISION/SCALE]
FROM SYS.OBJECTS AS T
JOIN SYS.COLUMNS AS C
ON T.OBJECT_ID=C.OBJECT_ID
JOIN SYS.TYPES AS P
ON C.SYSTEM_TYPE_ID=P.SYSTEM_TYPE_ID
WHERE T.TYPE_DESC='USER_TABLE';

Using INFORMATION SCHEMA VIEWS

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION,
       COLUMN_DEFAULT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
       NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,
       DATETIME_PRECISION
FROM INFORMATION_SCHEMA.COLUMNS