SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SAP ABAP Programming and HANA Database Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




CRM Companies List
Web Based CRM Software


List Table Column Names on SAP HANA Database using SQLScript


SAP HANA database developers can list table columns names and column properties for a specific table using SQL via system views like SYS.TABLE_COLUMNS In this SQLScript tutorial, I want to show how programmers can list database table columns and build the list of table columns as a concatenated SQL string variable.

Following SAP HANA database SQL codes will help developers to get the table structure for various tasks during database SQLScript developments.

For example, to list all column names of SAP MAKT table where the MARA material description translations are stored, following SQL Select statement can be executed.

SELECT * FROM SYS.TABLE_COLUMNS WHERE TABLE_NAME = 'MAKT' ORDER BY POSITION;

You see, I query system view sys.table_columns which provides information about all table columns by filtering table_name field with the target table name.

HANA database table columns list using SQL query

It is also possible to get the same list using parameter for database table name as follows:

do
begin

declare pTable nvarchar(256);
pTable = 'MAKT';

SELECT
 SCHEMA_NAME,
 TABLE_NAME,
 COLUMN_NAME,
 DATA_TYPE_NAME,
 LENGTH,
 SCALE,
 IS_NULLABLE,
 DEFAULT_VALUE,
 COLLATION,
 POSITION
FROM SYS.TABLE_COLUMNS
WHERE TABLE_NAME = :pTable
ORDER BY POSITION;

end;

As seen in following screenshot, displayed columns are the most important and frequently refereneced fields of a HANA database table.

list all columns of a SAP HANA database table using system view

Another SQLScript code example that I want to share in this tutorial with database developers is concatenating all column names of a HANA table using STRING_AGG() function to form a single string variable.

declare ColumnList nvarchar(8000);
declare pTable nvarchar(256);
pTable = 'MAKT';

SELECT
 string_agg(column_name, ', ' order by position)
INTO "COLUMNLIST"
FROM SYS.TABLE_COLUMNS WHERE TABLE_NAME = :pTable;

select :ColumnList as ColumnList from dummy;

SQL programmers can realize, I first declare variables; one for input parameter for table name and the second one is for concatenated table column names list seperated by comma and a space

Then I execute STRING_AGG() string aggregation function which returns row column values into a single string and stores into ColumnList variable. Please note that I use the variable in capital letters surrounded with (")

HANA database table column names listed as comma seperated with SQLScript

For more information on SYS.Table_Columns system view, please refer to SAP HANA SQL and System Views Reference on help.sap.com






SAP Tutorials

SAP Tutorial

SAP Forums

SAP Tools

SAP Transaction Codes Table








Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems