Geometry_Columns

Definition

The Geometry_Columns function takes the name of a database table that is not registered with the geodatabase and returns the name of all spatial columns in the table.

Syntax

sde.geometry_columns(schema name, table name)

Return type

String

If there are multiple spatial columns in the table, the names are returned either in a space-delimited list or rowset. If there are no spatial columns in the table, a null string is returned.

Example

The following examples show the use of the Geometry_Columns function in each supported database.

DB2

The following example queries a table, centerline, in schema transport. The centerline table contains only one geometry column named shape.

CALL sde.geometry_columns('TRANSPORT', 'CENTERLINE')

Result set 1
--------------
COLUMN_NAME
------------------------
SHAPE

1 record(s) selected.
Return Status = 0

In this example, table hwys is queried in schema transport. The hwys table contains two geometry columns.

CALL sde.geometry_columns ('TRANSPORT', 'ROADS')

Result set 1
--------------
COLUMN_NAME
------------------------
GEO1 
GEO2

2 record(s) selected.
Return Status = 0

Oracle

The following example queries a table, centerline, in schema transport. The centerline table contains only one geometry column named shape.

SELECT SDE.GDB_UTIL.Geometry_Columns('TRANSPORT', 'CENTERLINE')
FROM DUAL;

SDE.GDB_UTIL.GEOMETRY_COLUMNS('TRANSPORT', 'CENTERLINE')
--------------------------------------------------------
SHAPE

In this example, table roads is queried in schema transport. The roads table contains three geometry columns.

SELECT SDE.GDB_UTIL.Geometry_Columns('TRANSPORT', 'ROADS')
FROM DUAL;

SDE.GDB_UTIL.GEOMETRY_COLUMNS('TRANSPORT', 'ROADS')
--------------------------------------------------------
SHAPEROW SHAPEROAD SHAPEMMRKER

PostgreSQL

The following example queries a table, centerline, in schema transport. The centerline table contains only one geometry column named shape.

SELECT sde.geometry_columns('transport', 'centerline');

shape

In this example, table roads is queried in schema transport. The roads table contains two geometry columns.

SELECT sde.geometry columns('transport', 'roads');

shape1 shape2

SQL Server

The following example queries a table, centerline, in schema transport in an sde-schema geodatabase. The centerline table contains only one geometry column named shape.

DECLARE @owner nvarchar(128) = 'transport';
DECLARE @table nvarchar(128) = 'centerline';

-- The geometry_columns function returns a rowset, much like a SELECT statement does. 
-- Execute geometry_columns, inserting the rowset into a table variable

DECLARE @col_list table (column_name sysname);
INSERT @col_list EXEC sde.geometry_columns @owner, @table;

SELECT column_name "GEOMETRY COLUMNS" 
FROM @col_list

GEOMETRY COLUMNS
shape

In this example, table streets is queried in schema transport in a dbo-schema geodatabase. The streets table contains two geometry columns.

DECLARE @owner nvarchar(128) = 'transport';
DECLARE @table nvarchar(128) = 'streets';

-- Execute geometry_columns, inserting the rowset into a table variable.

DECLARE @col_list table (column_name sysname);
INSERT @col_list EXEC dbo.geometry_columns @owner, @table;

-- Display results stored in table variable
-- Because the results were written to a table, the values are in rows rather than a space-separated list.

SELECT column_name "GEOMETRY COLUMNS" 
FROM @col_list

GEOMETRY COLUMNS
spcol1
spcol2