Human Capital Management Blogs by SAP
Get insider info on SAP SuccessFactors HCM suite for core HR and payroll, time and attendance, talent management, employee experience management, and more in this SAP blog.
cancel
Showing results for 
Search instead for 
Did you mean: 
yogananda
Product and Topic Expert
Product and Topic Expert

Finding index and primary keys of tables

SELECT IFNULL(CONSTRAINT,'NUNIQUE'),
INDEX_NAME,COLUMN_NAME 
FROM INDEX_COLUMNS WHERE SCHEMA_NAME = '%s'
AND TABLE_NAME = '%s' 
ORDER BY INDEX_OID,POSITION
select * from TABLES;
select * from M_TABLES;
select * from M_CS_TABLES;
select * from M_RS_TABLES;
select * from TABLE_COLUMNS;
select * from M_CS_ALL_COLUMNS;
select * from M_TEMPORARY_TABLES;

Show details of users that have been logged-in

select * from "SYS"."USERS"
where "LAST_SUCCESSFUL_CONNECT" is not null
order by 9 desc;

To get all Libraries

 

select to_char(definition) from public.libraries 
where schema_name='EXT';

To get a complete list of error codes and their descriptions

SELECT * FROM M_ERROR_CODES ORDER BY CODE ASC;

To get all DDL

 

call get_object_definition('<SCHEMA>','<TABLENAME>');

 

ALTER TABLE schema.table ADD PRIMARY KEY (column1,column2)

Check SQL PLAN Cache

select * from M_SQL_PLAN_CACHE

Check invalid custom DB views

select * from "SYS"."VIEWS"
where schema_name not like 'SAP%' and is_valid = 'FALSE';

Check inactive custom DB objects

select * from "_SYS_REPO"."INACTIVE_OBJECT"
where "PACKAGE_ID" not like 'sap%';

Check which SAP language settings are being used by current user

 

select session_context('LOCALE_SAP'),
session_context('LOCALE') 
from dummy;
## Search executed SQL statements,
e.g. to find out who deleted a table

select * from "SYS"."M_EXECUTED_STATEMENTS" where "STATEMENT_STRING" LIKE 'DROP TABLE%';

Show custom settings within global.ini and indexserver.ini

 

select * from "SYS"."M_INIFILE_CONTENTS"
where ("LAYER_NAME" = 'SYSTEM' or "HOST" <> ")
and ("FILE_NAME" = 'global.ini'
or "
FILE_NAME" = 'indexserver.ini');

Show assigned user roles

 

select * from "SYS"."GRANTED_ROLES"
where "GRANTEE_TYPE" = 'USER';

Show assigned repository privileges

 

select * from "SYS"."GRANTED_PRIVILEGES"
where object_type = 'REPO';

Show objects owned by non-system users

 

select * from "SYS"."OWNERSHIP"
where owner_name not like 'SAP%' and owner_name not like '%SYS%'
order by 1,2;

 

Show Corresponding Tables referring to which Schema

 

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 */

 

Analyze expensive statement trace

 

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;
select * from M_PREPARED_STATEMENTS;
select * from M_ACTIVE_STATEMENTS;
select * from M_EXPENSIVE_STATEMENTS;

RECLAIM Storage Space

 

ALTER SYSTEM RECLAIM DATAVOLUME 105 DEFRAGMENT.

Most Common Functions used in Implementation

 

CREATE TABLE "ZTABLE"
(
  "MANDT" NVARCHAR(000003) DEFAULT '000' NOT NULL,
  "RELID" NVARCHAR(000002) DEFAULT ' ' NOT NULL,
  "FIELD1" NVARCHAR(000010) DEFAULT '0' NOT NULL,
  "FIELD2" NVARCHAR(000010) DEFAULT '0' NOT NULL,
  PRIMARY KEY ("MANDT", "RELID")
);

 

SELECT SESSION_USER "session user" FROM DUMMY;
SELECT TO_DATE('2010-01-12', 'YYYY-MM-DD') "to date" FROM DUMMY;

SELECT TRIM ('a' FROM 'aaa123456789aa') "trim both" FROM DUMMY;
SELECT CURRENT_DATE "current date" FROM DUMMY;

 

SELECT ISOWEEK (TO_DATE('2011-05-30', 'YYYY-MM-DD')) "isoweek" 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 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 REPLACE ('DOWNGRADE DOWNWARD','DOWN', 'UP') "replace" FROM DUMMY;
SELECT RTRIM ('endabAabbabab','ab') "rtrim" FROM DUMMY;

 

SELECT RIGHT('HI0123456789', 20) "right" 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 SUBSTRING ('1234567890',4,2) "substring" FROM DUMMY;
SELECT WEEKDAY (TO_DATE ('2010-12-31', 'YYYY-MM-DD')) "week day" FROM DUMMY;
SELECT TO_VARCHAR (TO_DATE('2009-12-31'), 'YYYY/MM/DD') "to varchar" 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 EXTRACT (YEAR FROM TO_DATE ('2010-01-04', 'YYYY-MM-DD')) "extract" FROM DUMMY;

 

SELECT LANGUAGE(CONTENT) FROM TABLE;
SELECT IFNULL (NULL, 'same') "ifnull" FROM DUMMY;

 

SELECT ProdName, Type, Sales,
RANK() OVER ( PARTITION BY ProdName ORDER BY Sales DESC ) AS Rank
FROM ProductSales
ORDER BY ProdName, Type;

 

SELECT DAYOFYEAR ('2021-05-30') "dayofyear" FROM DUMMY;

 

SET SCHEMA "EXT"; 
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;

 

SELECT JSON_VALUE('{"item1":10}', '$.item1') AS "value" FROM DUMMY;
SELECT * FROM
XMLTABLE('/doc/item' PASSING
'<doc>
<item><id>10</id><name>Box</name></item>
<item><id>20</id><name>Jar</name></item>
</doc>'
COLUMNS ID INT PATH 'id', NAME VARCHAR(20) PATH 'name' ) as XTABLE;
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;

The following figure shows used memory, consisting of code, stack, and table data:

SAP HANA Used Memory

 

SAP HANA Used Memory

 

SAP HANA Virtual Memory

SAP HANA Virtual Memory

 

SAP HANA Resident Memory

SAP HANA Resident Memory

Reference


HANA SQL Functions

https://help.sap.com/viewer/7c78579ce9b14a669c1f3295b0d8ca16/Cloud/en-US/20a61f29751910149f99f0300dd...

Keep watching here and comment if you like to add on top of it
11 Comments