SAP HANA数据库常用SQL
👉 本文共约1098个字，系统预计阅读时间或需5分钟。
1、查找表的索引及主键信息
1 2 3 4 5 | SELECT IFNULL(CONSTRAINT,'NUNIQUE'), INDEX_NAME,COLUMN_NAME FROM INDEX_COLUMNS WHERE SCHEMA_NAME = '8EE9F72C2CE44899850DBE1986F5683B' AND TABLE_NAME = 'ML_SHARED_ITEM' ORDER BY INDEX_OID,POSITION; |
查询表ML_SHARED_ITEM中的索引以及主键信息：
1 2 3 4 5 | | |INDEX_NAME |COLUMN_NAME| |-----------|---------------------------|-----------| |PRIMARY KEY|_SYS_TREE_RS_#321809_#0_#P0|INSTANCE_ID| |PRIMARY KEY|_SYS_TREE_RS_#321809_#0_#P0|REQUEST_ID | |PRIMARY KEY|_SYS_TREE_RS_#321809_#0_#P0|CHANGE_ID | |
2、查询当前使用的SCHEMA
1 2 3 4 | SELECT CURRENT_SCHEMA FROM dummy; |CURRENT_SCHEMA | |-------------------------------------------------------------| |8EE9F72C2CE448998*************************AV7DSJ1YCBT8Z7RU_RT| |
3、设置当前默认的SCHEMA
设置完之后，可以避免输入长长的schema。HANA在安全性上非常苛刻，无论是shema名或是用户名，都很长(默认情况下要求)。
1 2 | SET SCHEMA 8EE9F72C2CE44899850DBE198*******SJ1YCBT8Z7RU_RT; CREATE TABLE t123(id int); |
4、查询登录用户信息
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 | select * from "SYS"."USERS" where "LAST_SUCCESSFUL_CONNECT" is not null order by 9 desc; -- 结果 { "select * from \"SYS\".\"USERS\"\r\nwhere \"LAST_SUCCESSFUL_CONNECT\" is not null\r\norder by 9 desc": [ { "USER_NAME" : "8EE9F72C2CE44899850D*****YCBT8Z7RU_RT", "USER_ID" : 320825, "USERGROUP_NAME" : "BROKER_UG_HDISHARED", "USER_MODE" : "LOCAL", "EXTERNAL_IDENTITY" : null, "CREATOR" : "BROKER_PO_USER", "CREATE_TIME" : "2023-04-11T00:39:25.434Z", "VALID_FROM" : "2023-04-11T00:39:25.434Z", "VALID_UNTIL" : null, "LAST_SUCCESSFUL_CONNECT" : "2023-04-13T11:41:14.882Z", "LAST_INVALID_CONNECT_ATTEMPT" : null, "INVALID_CONNECT_ATTEMPTS" : 0, "ADMIN_GIVEN_PASSWORD" : "TRUE", "LAST_PASSWORD_CHANGE_TIME" : "2023-04-11T00:39:25.434Z", "PASSWORD_CHANGE_NEEDED" : "FALSE", "IS_PASSWORD_LIFETIME_CHECK_ENABLED" : "FALSE", "USER_DEACTIVATED" : "FALSE", "DEACTIVATION_TIME" : null, "IS_PASSWORD_ENABLED" : "TRUE", "IS_KERBEROS_ENABLED" : "FALSE", "IS_SAML_ENABLED" : "FALSE", "IS_JWT_ENABLED" : "FALSE", "IS_LDAP_ENABLED" : "FALSE", "IS_X509_ENABLED" : "FALSE", "IS_RESTRICTED" : "FALSE", "IS_RESTRICTED_DETAILS" : null, "IS_CLIENT_CONNECT_ENABLED" : "TRUE", "HAS_REMOTE_USERS" : "FALSE", "AUTHORIZATION_MODE" : "LOCAL", "COMMENTS" : null, "CREATE_PROVIDER_TYPE" : null, "CREATE_PROVIDER_NAME" : null } ]} |
5、获取所有的库
1 2 | select to_char(definition) from public.libraries where schema_name='<schema>'; |
6、获取表定义
1 2 3 4 5 6 7 8 9 10 | call get_object_definition(current_schema,'T123'); |SCHEMA_NAME |OBJECT_NAME|OBJECT_TYPE|OBJECT_OID|OBJECT_CREATION_STATEMENT | |-------------------------------------------------------------|-----------|-----------|----------|----------------------------------------------------------------------------------------------------------------------------------------| |8EE9F72C2CE44899850DBE1986F5683B_BKXQ0I40HAV7DSJ1YCBT8Z7RU_RT|T123 |TABLE |330413 |CREATE COLUMN TABLE "8EE9F72C2CE44899850DBE1986F5683B_BKXQ0I40HAV7DSJ1YCBT8Z7RU_RT"."T123" ("ID" INTEGER) UNLOAD PRIORITY 5 AUTO MERGE | -- 同一张表T123, 如果上边你输入的是't123'，看看结果是啥？ 等同于'"T123"', 默认视同于大写。 ALTER TABLE schema.table ADD PRIMARY KEY (column1,column2); // 略 |
7、检查无效的视图
1 2 | select * from "SYS"."VIEWS" where schema_name not like 'SAP%' and is_valid = 'FALSE'; |
8、检查无效的对象
1 2 | select * from "_SYS_REPO"."INACTIVE_OBJECT" where "PACKAGE_ID" not like 'sap%'; |
9、检查当前用户使用的是哪种语言
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 | select session_context('LOCALE_SAP'), session_context('LOCALE') from dummy; |SESSION_CONTEXT('LOCALE_SAP')|SESSION_CONTEXT('LOCALE')| |-----------------------------|-------------------------| |E |en_US | ## Search executed SQL statements, e.g. 找出谁执行了DROP TABLE操作 select * from "SYS"."M_EXECUTED_STATEMENTS" where "STATEMENT_STRING" LIKE 'DROP TABLE%'; { "select * from \"SYS\".\"M_EXECUTED_STATEMENTS\" \r\nwhere \"STATEMENT_STRING\" LIKE 'DROP TABLE%'": [ { "HOST" : "8a4af3a7-19dd-4b51-b9f5-*****", "PORT" : 30040, "CONNECTION_ID" : 261446, "TRANSACTION_ID" : 1528, "UPDATE_TRANSACTION_ID" : 0, "STATEMENT_ID" : "1122902028586105", "STATEMENT_HASH" : "ea4879ef6b72ea1c03737c8745de41b8", "DB_USER" : "8EE9F72C2CE448*****************", "SCHEMA_NAME" : "8EE9F72C2CE44899850DBE198*********************", "APP_USER" : "Administrator", "CLIENT_IP" : "130.214.230.44", "CLIENT_PID" : 26592, "START_TIME" : "2023-04-13T11:52:05.066Z", "DURATION_MICROSEC" : 1018, "OBJECT_NAME" : "8EE9F72C2CE44899850DBE198*********************_RT.T123", "STATEMENT_STRING" : "DROP TABLE t123", "ERROR_CODE" : 0, "ERROR_TEXT" : "", "PASSPORT_ROOT_CONTEXT_ID" : null, "PASSPORT_ROOTCONTEXT_ID" : null, "PASSPORT_TRANSACTION_ID" : "", "PASSPORT_CONNECTION_ID" : "", "PASSPORT_CONNECTION_COUNTER" : 0, "PASSPORT_COMPONENT_NAME" : "", "PASSPORT_COMPONENT_TYPE" : 0, "PASSPORT_ACTION" : "", "PASSPORT_ACTION_TYPE" : 0, "PASSPORT_PREVIOUS_COMPONENT_NAME" : "", "PASSPORT_SERVICE" : 0, "PASSPORT_USER_ID" : "", "PASSPORT_CLIENT" : "", "PASSPORT_TRACE_FLAGS" : 0, "APPLICATION_SOURCE" : "", "APPLICATION_NAME" : "DBeaver 22.3.1 - SQLEditor <Script-46.sql>" } ]} -- 内容很详细 |
10、查询global.ini and indexserver.ini 中的自定义设置
1 2 3 4 | select * from "SYS"."M_INIFILE_CONTENTS" where ("LAYER_NAME" = 'SYSTEM' or "HOST" <> '') and ("FILE_NAME" = 'global.ini' or "FILE_NAME" = 'indexserver.ini'); |
这个得用dbadmin用户，有权限才行。不然可能都查不到结果。
11、查询权限角色相关
1、查询用户得到的角色
1 2 3 4 5 6 7 8 | select * from "SYS"."GRANTED_ROLES" where "GRANTEE_TYPE" = 'USER'; |GRANTEE |GRANTEE_TYPE|ROLE_SCHEMA_NAME|ROLE_NAME | |-------------------------------------------------------------|------------|----------------|------------------| |8EE9F72C2CE4489985*********YCBT8Z7RU_RT|USER | |PUBLIC | |8EE9F72C2CE4489985*********YCBT8Z7RU_RT|USER |BROKER_USER |STUDIO_ACCESS_ROLE| |8EE9F72C2CE4489985*********YCBT8Z7RU_RT|USER |BROKER_USER |ODATA_ACCESS_ROLE | |8EE9F72C2CE4489985*********YCBT8Z7RU_RT|USER |BROKER_USER |CDS_ACCESS_ROLE | |
2、查询得到的仓库(rep)的权限
1 2 | select * from "SYS"."GRANTED_PRIVILEGES" where object_type = 'REPO'; |
3、查询非系统用户拥有的对象
1 2 3 4 5 6 7 8 9 | select SCHEMA_NAME ,OWNER_NAME , OBJECT_NAME , OBJECT_TYPE from "SYS"."OWNERSHIP" where owner_name not like 'SAP%' and owner_name not like '%SYS%' order by 1,2; |SCHEMA_NAME |OWNER_NAME |OBJECT_NAME |OBJECT_TYPE| |--------------------------------|-----------------------------------|--------------------------------|-----------| |8EE9F72C2CE44899850DBE1986F5683B|8EE9F72C2CE44899850DBE1986F5683B#OO|_SYS_TREE_RS_#323715_#0_#P0 |INDEX | |8EE9F72C2CE44899850DBE1986F5683B|8EE9F72C2CE44899850DBE1986F5683B#OO|IDX_MAPP_TEN_CONT_BV |INDEX | |8EE9F72C2CE44899850DBE1986F5683B|8EE9F72C2CE44899850DBE1986F5683B#OO|IDX_MAPP_TEN_CONUUID |INDEX | |
12、获取引用一个schema的表
1 2 3 4 5 | SELECT BASE_SCHEMA_NAME, BASE_OBJECT_NAME,DEPENDENT_SCHEMA_NAME, DEPENDENT_OBJECT_NAME,DEPENDENT_OBJECT_TYPE FROM "SYS"."OBJECT_DEPENDENCIES" WHERE BASE_SCHEMA_NAME = 'TCMP' /* <== Schema Name */ AND BASE_OBJECT_NAME = 'CS_SALESORDER' /* <== Table Name */ |
13、分析SQL语句并TRACE
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | select to_varchar("STATEMENT_START_TIME",'DD.MM.YYYY') "EXEC_DATE", to_varchar("STATEMENT_START_TIME",'HH24:MI:SS') "EXEC_TIME", to_int("DURATION_MICROSEC"/1000000) "DURATION_S", to_decimal("MEMORY_SIZE"/1073741824,10,1) "MEM_GB", "RECORDS", "DB_USER", "APP_USER", "APPLICATION_NAME", "STATEMENT_STRING", length("STATEMENT_STRING") "SQL_LENGTH", OCCURRENCES_REGEXPR('JOIN' FLAG 'i' IN "STATEMENT_STRING") "JOIN", OCCURRENCES_REGEXPR('CASE' FLAG 'i' IN "STATEMENT_STRING") "DISTINCT", "ERROR_TEXT", "PARAMETERS" from "SYS"."M_EXPENSIVE_STATEMENTS" where "OPERATION" in ('INSERT','SELECT','AGGREGATED_EXECUTION') –exclude background activity and "RECORDS" > 0 and to_varchar("STATEMENT_START_TIME", 'YYYYMMDD') = current_date and to_int(to_varchar("STATEMENT_START_TIME",'HH24′)) between 8 and 17 –business hours order by 3 desc; |
14、空间大小相关
1、回收存储空间
1 | ALTER SYSTEM RECLAIM DATAVOLUME 105 DEFRAGMENT. |
2、列列表内存总和
1 | select round(sum(MEMORY_SIZE_IN_TOTAL)/1024/1024) as "Column Tables MB Used" from M_CS_TABLE |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
3、列列表内存占用
1 | SELECT table_name, memory_size_in_total FROM M_CS_TABLES ORDER BY memory_size_in_total DESC; |
4、各个行表的内存占用
1 | SELECT table_name, (USED_FIXED_PART_SIZE + USED_VARIABLE_PART_SIZE) AS disk_size FROM M_RS_TABLES ORDER BY disk_size DESC; |
5、行表内存总和
1 | select round(sum(USED_FIXED_PART_SIZE + USED_VARIABLE_PART_SIZE)/1024/1024) as "Row Tables MB Used" from M_RS_TABLES; |
6、所有表占用磁盘总和
1 | SELECT SUM(disk_size)/1024/1024/1024 FROM m_table_persistence_statistics; |
7、各个表的磁盘占用情况
1 | SELECT table_name, disk_size FROM m_table_persistence_statistics ORDER BY disk_size DESC; |
15、开发过程中常用的一些语句示例
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 | SELECT SESSION_USER "session user" FROM DUMMY; //会话用户 SELECT TO_DATE('2010-01-12', 'YYYY-MM-DD') "to date" FROM DUMMY; SELECT DAYS_BETWEEN (TO_DATE ('2009-12-05', 'YYYY-MM-DD'), TO_DATE('2010-01-05', 'YYYY-MM-DD')) "days between" FROM DUMMY; SELECT CURRENT_DATE "current date" FROM DUMMY; SELECT ISOWEEK (TO_DATE('2011-05-30', 'YYYY-MM-DD')) "isoweek" FROM DUMMY; SELECT DAYNAME ('2011-05-30') "dayname" FROM DUMMY; SELECT WEEK ('2017-01-02') FROM DUMMY; SELECT LENGTH ('length in char') "length" FROM DUMMY; SELECT WEEKDAY (TO_DATE ('2010-12-31', 'YYYY-MM-DD')) "week day" FROM DUMMY; SELECT DAYOFYEAR ('2021-05-30') "dayofyear" FROM DUMMY; SELECT YEARS_BETWEEN(TO_DATE('2001-01-01'), TO_DATE('2003-03-14')) "years_between" FROM DUMMY; SELECT YEAR (TO_DATE ('2011-05-30', 'YYYY-MM-DD')) "year" FROM DUMMY; SELECT MONTH ('2011-05-30') "month" FROM DUMMY; SELECT NOW () "now" FROM DUMMY; SELECT LAST_DAY (TO_DATE('2010-01-04', 'YYYY-MM-DD')) "last day" FROM DUMMY; SELECT SUBSTRING ('1234567890',4,2) "substring" FROM DUMMY; SELECT TO_VARCHAR (TO_DATE('2009-12-31'), 'YYYY/MM/DD') "to varchar" FROM DUMMY; SELECT TRIM ('a' FROM 'aaa123456789aa') "trim both" FROM DUMMY; SELECT REPLACE ('DOWNGRADE DOWNWARD','DOWN', 'UP') "replace" FROM DUMMY; SELECT RTRIM ('endabAabbabab','ab') "rtrim" FROM DUMMY; SELECT RIGHT('HI0123456789', 20) "right" FROM DUMMY; SELECT UPPER ('Ant') "uppercase" FROM DUMMY; SELECT CONCAT ('C', 'at') "concat" FROM DUMMY; SELECT FLOOR (14.5) "floor" FROM DUMMY; SELECT TO_DECIMAL(7654321.888888, 10, 3) "to decimal" FROM DUMMY; SELECT LANGUAGE(CONTENT) FROM TABLE; |
Windows函数
1 2 3 4 | SELECT ProdName, Type, Sales, RANK() OVER ( PARTITION BY ProdName ORDER BY Sales DESC ) AS Rank FROM ProductSales ORDER BY ProdName, Type; |
建表示例:
1 2 3 4 5 6 7 8 | SET SCHEMA "SAPABC"; CREATE TABLE MY_DATES (FCID NVARCHAR(2), STARTDATE DATE, ENDDATE DATE); INSERT INTO MY_DATES VALUES ('01', '2014-01-01', '2014-02-14'); INSERT INTO MY_DATES VALUES ('01', '2014-04-01', '2014-05-14'); INSERT INTO MY_DATES VALUES ('01', '2014-07-01', '2014-08-05'); INSERT INTO MY_DATES VALUES ('01', '2014-10-01', '2014-10-30'); SELECT WORKDAYS_BETWEEN(FCID, STARTDATE, ENDDATE) "production duration" FROM MY_DATES; |
Json的：
1 | SELECT JSON_VALUE('{"item1":10}', '$.item1') AS "value" FROM DUMMY; |
简单的函数：
1 2 3 4 5 6 7 | CREATE FUNCTION func_add_mul(x Double, y Double) RETURNS result_add Double, result_mul Double LANGUAGE SQLSCRIPT READS SQL DATA AS BEGIN result_add := :x + :y; result_mul := :x * :y; END; |
