Oracle,PostgreSQL,MySql,SqlServer各数据库查元信息的SQL
发布时间
阅读量：
阅读量
Oracle
查询表字段信息
SELECT
a.COLUMN_NAME AS B_NAME, -- 字段名称
a.DATA_TYPE, -- 字段数据类型
CASE
WHEN a.COLUMN_NAME IN (
SELECT cols.column_name
FROM all_constraints cons, all_cons_columns cols
WHERE cons.constraint_type = 'P' -- 主键约束
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
AND cols.COLUMN_NAME = a.COLUMN_NAME
AND cols.TABLE_NAME = 'TB_CIS_CONSULT_DETAIL'
AND cons.OWNER = 'GZFY'
) THEN 'PRI' -- 如果字段是主键，则标记为 'PRI'
ELSE NULL -- 否则为 NULL
END COLUMN_KEY,
b.COMMENTS AS remark -- 字段备注
FROM all_tab_cols a
LEFT JOIN all_col_comments b
ON a.TABLE_NAME = b.TABLE_NAME
AND a.COLUMN_NAME = b.COLUMN_NAME
AND a.OWNER = b.OWNER
WHERE a.TABLE_NAME = 'TB_CIS_CONSULT_DETAIL' -- 目标表名称
AND HIDDEN_COLUMN = 'NO' -- 排除隐藏字段
AND a.OWNER = 'GZFY'; -- 表的所有者
sql
查询表的键字段信息
SELECT
column_name -- 键字段的名称
FROM all_ind_columns
WHERE table_owner = 'GZFY' -- 表的所有者
AND table_name = 'TB_CIS_CONSULT_DETAIL'; -- 目标表名称
sql查询表名或视图名
SELECT DISTINCT
view_name AS table_name -- 视图名称
FROM all_views
WHERE OWNER = 'GZFY' -- 所有者
UNION
SELECT DISTINCT
table_name -- 表名称
FROM all_tables
WHERE OWNER = 'GZFY'; -- 所有者
ORDER BY table_name; -- 按名称排序
sql
预览表数据
SELECT
"TB_CIS_EMR_FEE"."UPDATE_DATE",
"TB_CIS_EMR_FEE"."COMMENTS",
"TB_CIS_EMR_FEE"."ID",
-- 其他字段...
FROM "GZFY"."TB_CIS_EMR_FEE"
WHERE ROWNUM <= 10; -- 限制返回结果为10行
sqlPostgreSQL
查询表字段信息
SELECT
col.COLUMN_NAME, -- 字段名称
col.data_type, -- 数据类型
CASE
WHEN col.COLUMN_NAME IN (
SELECT conname
FROM pg_catalog.pg_constraint
JOIN pg_catalog.pg_namespace
ON pg_catalog.pg_constraint.connamespace = pg_catalog.pg_namespace.oid
JOIN pg_catalog.pg_class
ON pg_catalog.pg_constraint.conrelid = pg_catalog.pg_class.oid
WHERE pg_catalog.pg_constraint.contype = 'p' -- 主键约束
AND pg_catalog.pg_namespace.nspname = 'public'
AND pg_catalog.pg_class.relname = 'salaries'
) THEN 'PRI'
ELSE NULL
END PRI, -- 标记是否为主键
(
SELECT descr.description
FROM pg_class AS cls
INNER JOIN pg_attribute AS attr ON cls.oid = attr.attrelid
LEFT JOIN pg_description AS descr ON (descr.objoid = cls.oid AND descr.objsubid = attr.attnum)
WHERE cls.relkind IN ('r', 'v')
AND cls.relname = 'salaries'
AND attr.attname = col.COLUMN_NAME
) AS DESCRIPTION -- 字段描述
FROM information_schema.COLUMNS col
WHERE table_schema = 'public'
AND table_catalog = 'employees'
AND table_name = 'salaries';
sql
预览表数据
SELECT
"salaries"."emp_no",
"salaries"."salary",
to_char("salaries"."from_date", 'YYYY-MM-DD HH24:MI:SS'),
to_char("salaries"."to_date", 'YYYY-MM-DD HH24:MI:SS')
FROM "employees"."public"."salaries"
LIMIT 10; -- 限制返回10行
sql查询表名或视图名
SELECT table_name
FROM information_schema.tables
WHERE table_catalog = 'employees'
AND table_schema = 'public'
AND table_type NOT IN ('FOREIGN TABLE'); -- 排除外部表
sqlMySQL
查询表名或视图名
SELECT DISTINCT TABLE_NAME,
TABLE_TYPE != 'BASE TABLE' AS IS_VIEW -- 判断是否为视图
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = "test"; -- 目标数据库
sql查询表字段信息
SELECT
COLUMN_NAME, -- 字段名称
DATA_TYPE, -- 数据类型
COLUMN_KEY, -- 键类型
COLUMN_COMMENT -- 字段备注
FROM information_schema.columns
WHERE TABLE_SCHEMA = 'test'
AND TABLE_NAME = 'tb_cis_patient_info';
sql查询表的键字段信息
SELECT DISTINCT
column_name -- 键字段的名称
FROM information_schema.STATISTICS
WHERE table_schema = 'test'
AND table_name = 'tb_cis_patient_info';
sql预览表数据
SELECT
card_number,
card_type,
medical_institut_code,
-- 其他字段...
FROM test.tb_cis_patient_info
LIMIT 10; -- 限制返回10行
sqlSQL Server
查询表字段信息
SELECT
remarks.column_name, -- 字段名称
columns.DATA_TYPE, -- 数据类型
columns.PRI, -- 键标记
remarks.remark -- 字段备注
FROM (
SELECT
sc.name AS column_name,
sep.value AS remark -- 字段备注
FROM sys.tables st
INNER JOIN sys.columns sc ON st.object_id = sc.object_id
LEFT JOIN sys.extended_properties sep ON st.object_id = sep.major_id
AND sc.column_id = sep.minor_id
AND sep.name = 'MS_Description'
WHERE st.name = 'CB_COST_ITEM' -- 表名称
) remarks
LEFT JOIN (
SELECT DISTINCT
T1.COLUMN_NAME,
T1.DATA_TYPE,
T2.PRI
FROM (
SELECT
COLUMN_NAME,
DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'CB_COST_ITEM'
) T1
LEFT JOIN (
SELECT
COLUMN_NAME,
'PRI' AS PRI
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = 'CB_COST_ITEM' AND CONSTRAINT_NAME LIKE 'PK%'
) T2 ON T2.COLUMN_NAME = T1.COLUMN_NAME
) columns ON columns.COLUMN_NAME = remarks.column_name;
sql
查询表的键字段信息
SELECT
col.name AS ColumnName -- 键字段的名称
FROM sys.indexes ind
INNER JOIN sys.index_columns ic ON ind.object_id = ic.object_id AND ind.index_id = ic.index_id
INNER JOIN sys.columns col ON ic.object_id = col.object_id AND ic.column_id = col.column_id
INNER JOIN sys.tables t ON ind.object_id = t.object_id
WHERE ind.is_primary_key = 0
AND ind.is_unique = 0
AND ind.is_unique_constraint = 0
AND t.is_ms_shipped = 0
AND t.name = 'CB_COST_ITEM'; -- 表名称
sql
查询表名或视图名
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
ORDER BY TABLE_NAME; -- 按表名排序
sql预览表数据
SELECT TOP 10
[PK_ID],
[ITEM_CODE],
[ITEM_NAME],
-- 其他字段...
FROM [medicare_ZhuHaiJinWan].[dbo].[CB_COST_ITEM];
sql全部评论 (0)
还没有任何评论哟~