数据库元数据数据字典查询_3_列出给定表的基本信息列出给定表的基本信息

103 阅读 0 评论 68 点赞

我是靠谱客的博主激情糖豆，这篇文章主要介绍数据库元数据数据字典查询_3_列出给定表的基本信息列出给定表的基本信息，现在分享给大家，希望可以做个参考。

列出给定表的基本信息

需求描述

需求：查询出给定的表dept,emp,bonus,salgrade里的表名，字段名、字段类型、字段注释、表注释信息。

解决方法:通过各个数据库里提供的与数据库相关的数据字典进行查询。

注: 数据库数据集SQL脚本详见如下链接地址

员工表结构和数据初始化SQL脚本

SQL代码

复制代码

-- Oracle:
SELECT B.TABLE_NAME,B.COLUMN_NAME,B.DATA_TYPE,A.COMMENTS AS Table_Comm ,C.COMMENTS AS Column_Comm 
from user_tab_comments A
JOIN user_tab_columns B
     ON A.TABLE_NAME = B.TABLE_NAME
JOIN user_col_comments C
ON B.TABLE_NAME = C.TABLE_NAME AND B.COLUMN_NAME = C.COLUMN_NAME
WHERE A.TABLE_NAME IN('DEPT','EMP','BONUS','SALGRADE')

-- Mysql:
SELECT A.TABLE_NAME,A.COLUMN_NAME,A.DATA_TYPE,B.TABLE_COMMENT AS Table_Comm,A.COLUMN_COMMENT AS Column_Comm
FROM INFORMATION_SCHEMA.COLUMNS A
JOIN INFORMATION_SCHEMA.TABLES B
  ON A.TABLE_NAME = B.TABLE_NAME
WHERE A.TABLE_SCHEMA='SHENL' 
AND A.TABLE_NAME IN('DEPT','EMP','BONUS','SALGRADE')

-- Sql Server:

USE Shenl;
GO
SELECT
	A.name AS table_name,
	B.name AS column_name,
	C.name AS data_type,
	D.value AS column_comm
FROM sys.tables A
INNER JOIN 
(
SELECT B.object_id,NULL AS name ,NULL AS user_type_id,0 as column_id FROM sys.tables A
INNER JOIN sys.columns B 
	ON B.object_id = A.object_id 
	WHERE A.name IN ('dept','emp','bonus','salgrade') AND column_id = 1
	
UNION
SELECT object_id,name,user_type_id,column_id FROM sys.columns
)B 
	ON B.object_id = A.object_id
LEFT JOIN sys.systypes C
ON B.user_type_id = C.xtype
INNER JOIN sys.extended_properties D 
	ON D.major_id = B.object_id AND 
		D.minor_id = B.column_id
WHERE A.name  IN ('dept','emp','bonus','salgrade')
ORDER BY A.name

-- 或：
USE Shenl;
GO
SELECT table_name,column_name,data_type,column_comm,F.value as table_comm FROM
(
SELECT A.object_id AS Object_id, 
	A.name AS table_name,
	B.name AS column_name,
	C.name AS data_type,
	D.value AS column_comm

FROM sys.tables A
INNER JOIN sys.columns B 
	ON B.object_id = A.object_id
LEFT JOIN sys.systypes C
ON B.user_type_id = C.xtype
INNER JOIN sys.extended_properties D 
	ON D.major_id = B.object_id AND 
		D.minor_id = B.column_id
WHERE A.name  IN ('dept','emp','bonus','salgrade')
)E
JOIN
(
SELECT major_id,value FROM sys.extended_properties A
JOIN sys.tables B
ON A.major_id = B.object_id
AND B.name IN  ('dept','emp','bonus','salgrade') AND A.minor_id = 0
)F
ON E.object_id = F.major_id

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
-- Oracle:
SELECT B.TABLE_NAME,B.COLUMN_NAME,B.DATA_TYPE,A.COMMENTS AS Table_Comm ,C.COMMENTS AS Column_Comm 
from user_tab_comments A
JOIN user_tab_columns B
     ON A.TABLE_NAME = B.TABLE_NAME
JOIN user_col_comments C
ON B.TABLE_NAME = C.TABLE_NAME AND B.COLUMN_NAME = C.COLUMN_NAME
WHERE A.TABLE_NAME IN('DEPT','EMP','BONUS','SALGRADE')

-- Mysql:
SELECT A.TABLE_NAME,A.COLUMN_NAME,A.DATA_TYPE,B.TABLE_COMMENT AS Table_Comm,A.COLUMN_COMMENT AS Column_Comm
FROM INFORMATION_SCHEMA.COLUMNS A
JOIN INFORMATION_SCHEMA.TABLES B
  ON A.TABLE_NAME = B.TABLE_NAME
WHERE A.TABLE_SCHEMA='SHENL' 
AND A.TABLE_NAME IN('DEPT','EMP','BONUS','SALGRADE')

-- Sql Server:

USE Shenl;
GO
SELECT
	A.name AS table_name,
	B.name AS column_name,
	C.name AS data_type,
	D.value AS column_comm
FROM sys.tables A
INNER JOIN 
(
SELECT B.object_id,NULL AS name ,NULL AS user_type_id,0 as column_id FROM sys.tables A
INNER JOIN sys.columns B 
	ON B.object_id = A.object_id 
	WHERE A.name IN ('dept','emp','bonus','salgrade') AND column_id = 1
	
UNION
SELECT object_id,name,user_type_id,column_id FROM sys.columns
)B 
	ON B.object_id = A.object_id
LEFT JOIN sys.systypes C
ON B.user_type_id = C.xtype
INNER JOIN sys.extended_properties D 
	ON D.major_id = B.object_id AND 
		D.minor_id = B.column_id
WHERE A.name  IN ('dept','emp','bonus','salgrade')
ORDER BY A.name

-- 或：
USE Shenl;
GO
SELECT table_name,column_name,data_type,column_comm,F.value as table_comm FROM
(
SELECT A.object_id AS Object_id, 
	A.name AS table_name,
	B.name AS column_name,
	C.name AS data_type,
	D.value AS column_comm 

FROM sys.tables A
INNER JOIN sys.columns B 
	ON B.object_id = A.object_id
LEFT JOIN sys.systypes C
ON B.user_type_id = C.xtype
INNER JOIN sys.extended_properties D 
	ON D.major_id = B.object_id AND 
		D.minor_id = B.column_id
WHERE A.name  IN ('dept','emp','bonus','salgrade')
)E
JOIN
(
SELECT major_id,value FROM sys.extended_properties A
JOIN sys.tables B
ON A.major_id = B.object_id
AND B.name IN  ('dept','emp','bonus','salgrade') AND A.minor_id = 0
)F
ON E.object_id = F.major_id