sdetable

The sdetable command administers business tables, the data within them, their indexes, and views.

Usage syntax

sdetable -o add_uuid_column -t <table> -c <column>
[-D <database_name>] -u <DB_user_name> [-p <DB_user_password>]
[-i {<service> | <port#> | <direct connection>] [-s <server_name>}] [-q]
 

sdetable -o alter_column -t <table> -c <column>
[-x {<ALLOW_NULLS | DISALLOW_NULLS}] [-z <sde_column_type>]
[-S <column_description>] [-i {<service> | <port#> | <direct connection>}]
[-s <server_name>] [-D <database_name>] [-N]
-u <DB_user_name> [-p <DB_user_password>] [-q]
 

sdetable -o alter_reg -t <table>
[{-c <row_id_column> -C {SDE|USER} } | {-C NONE} ]
[-L {OFF | ON}] [-M <minimum_row_id>] [-S <table_description>]
[-V {SINGLE,version_name | MULTI | HYBRID}] [-F] [-k <config_keyword>]
[-H {VISIBLE | HIDDEN}] [-R {MANY | SINGLE}]
[-y {OFF|<history_table,<history_rowid_column>,<from_date_column>,<to_date_column>}]
[-i {<service> | <port#> | <direct connection>}] [-s <server_name>] [-D <database_name>]
-u <DB_user_name> [-p <DB_user_password>] [-N] [-q]
 

sdetable -o create -t <table> -d <column_definition> [-k <config_keyword>]
[-i {<service> | <port#> | <direct connection>}] [-s <server_name>]
[-D <database_name>] -u <DB_user_name> [-p <DB_user_password>] [-q]
 

sdetable -o create_index -t <table> -n <index>
-c <column> [-k <config_keyword>] [-Q] [-s <server_name>]
[-i {<service> | <port#> | <direct connection>}] [-D <database_name>]
-u <DB_user_name> [-p <DB_user_password>] [-q]
 

sdetable -o create_mv_view -T <view_name> -t <table>
[-i {<service> | <port#> | <direct connection>}]
[-s <server_name>] [-D <database_name>] -u <DB_user_name>
[-p <DB_user_password>] [-N] [-q]
 

sdetable -o create_view -T <view_name> -t <table1,table2...tablen>
-c <table_col1,table_col2...table_coln>
[-a <view_col1,view_col2...view_coln>] [-w <"where_clause">]
[-i {<service> | <port#> | <direct connection>}] [-s <server_name>]
[-D <database_name>] -u <DB_user_name> [-p <DB_user_password>] [-N] [-q]
 

sdetable -o delete -t <table> [-s <server_name>]
[-i {<service> | <port#> | <direct connection>}]
[-D <database_name>] -u <DB_user_name>
[-p <DB_user_password>] [-N] [-q]
 

sdetable -o delete_index -n <index> [-t <table>] [-s <server_name>]
[-i {<service> | <port#> | <direct connection>}] [-D <database_name>]
-u <DB_user_name> [-p <DB_user_password>] [-N] [-q]
 

sdetable -o delete_mv_view -t <table> [-s <server_name>]
[-i {<service> | <port#> | <direct connection>}] [-D <database_name>]
-u <DB_user_name> [-p <DB_user_password>] [-N] [-q]
 

sdetable -o describe -t <table> [-s <server_name>]
[-i {<service> | <port#> | <direct connection>}]
[-D <database_name>] -u <DB_user_name>
[-p <DB_user_password>] [-q]
 

sdetable -o describe_long -t <table> [-s <server_name>]
[-i {<service> | <port#> | <direct connection>}] [-D <database_name>]
-u <DB_user_name> [-p <DB_user_password>] [-q]
 

sdetable -o describe_reg [{-U <user> | -t <table>}]
[-i {<service> | <port#> | <direct connection>}]
[-s <server_name>] [-D <database_name>]
-u <DB_user_name> [-p <DB_user_password>] [-q]
 

sdetable -o {grant | revoke} -t <table> -U <user>
-A <SELECT,UPDATE,INSERT,DELETE> [-s <server_name>]
[-i {<service> | <port#> | <direct connection>}] 
[-D <database_name>] -u <DB_user_name>
[-p <DB_user_password>] [-I] [-q]
 

sdetable -o list -t <table> -c <column> -v <column_value>
[-i {<service> | <port#> | <direct connection>}] [-s <server_name>]
[-D <database_name>] -u <DB_user_name> [-p <DB_user_password>]
[-N] [-q]
 

sdetable -o list_grantees -t <table> [-i {<service> | <port#> | <direct connection>}]
[-s <server_name>] [-D <database_name>] -u <DB_user_name> [-p <DB_user_password>]
[-N] [-q] 

sdetable -o {load_only_io | normal_io} -t <table>
[-i {<service> | <port#> | <direct connection>}] [-s <server_name>]
[-D <database_name>] -u <DB_User_name> [-p <DB_User_password>]
[-N] [-q]
 

sdetable -o migrate -t <table> -k <config_keyword> [-D <database_name>]
[-i {<service> | <port#> | <direct connection>}] [-s <server_name>]
-u <DB_user_name> [-p <DB_user_password>] [-N] [-q]
 

sdetable -o populate_uuid_column -t <table> -c <column> [-D <database_name>]
[-s <server_name>] [-i {<service> | <port#> | <direct connection>}]
-u <DB_user_name> [-p <DB_user_password>]  [-q]
 

sdetable -o rebuild_index -t <table>
[-x {RASTER | SPATIAL | VERSIONED | XML | ALL}]
[-i {<service> | <port#> | <direct connection>}]
[-s <server_name>] [-D <database_name>] -u <DB_user_name>
[-p <DB_user_password>] [-N] [-q]
 

sdetable -o register -t <table>
[{-c <row_id_column> -C {SDE|USER} } | {-C NONE} ]
[-L {OFF | ON}] [-M <minimum_row_id>] [-S <table_description>]
[-V {SINGLE | MULTI | HYBRID}] [-k <config_keyword>]
[-H {VISIBLE | HIDDEN}] [-R {MANY | SINGLE}]
[-y {<history_table,<history_rowid_column>,<from_date_column>,<to_date_column>}]
[-i {<service> | <port#> | <direct connection>}] [-s <server_name>]
[-D <database_name>] -u <DB_user_name> [-p <DB_user_password>] [-q]
 

sdetable -o rename -t <current_name_of_table> -T <new_name_of_table>
[-i {<service> | <port#> | <direct connection>}] [-s <server_name>]
[-D <database_name>] -u <DB_user_name> [-p <DB_user_password>]
[-N] [-q]
 

sdetable -o truncate -t <table> [-s <server_name>]
[-i {<service> | <port#> | <direct connection>}] [-D <database_name>]
-u <DB_user_name> [-p <DB_user_password>] [-N] [-q]
 

sdetable -o unregister -t <table> [-F] [-s <server_name>]
[-i {<service> | <port#> | <direct connection>}]
[-D <database_name>] -u <DB_user_name>
[-p <DB_user_password>] [-N] [-q]
 

sdetable -o update_dbms_stats has database specific usage:

Oracle:

sdetable -o update_dbms_stats -t <table> [-K <keyword>]
[-n <{ALL | <index_name>}>] [-i {<service> | <port#> | <direct connection>}]
[-s <server_name>] -u <DB_user_name> [-p <DB_user_password>]
[-N] [-q]

SQL Server:

sdetable -o update_dbms_stats -t <table> [-K <keyword>]
-m <"{with fullscan | with sample %% percent} [index]">
[-i {<service> | <port#> | <direct connection>}] [-s <server_name>]
[-D <database_name>] -u <DB_user_name> [-p <DB_user_password>]
[-N] [-q]

Informix:

sdetable -o update_dbms_stats -t <table> [-K <keyword>]
-m <{low | medium | high}> [-i {<service> | <port#> | <direct connection>}]
[-s <server_name>][-D <database_name>] -u <DB_user_name>
[-p <DB_user_password>] [-N] [-q]

DB2:

sdetable -o update_dbms_stats -t <table> [-K <keyword>]
[-n {ALL | <index_name>}] [-i {<service> | <port#> | <direct connection>}]
[-s <server_name>] [-D <database_name>] -u <DB_user_name>
[-p <DB_user_password>] [-N] [-q]
 

PostgreSQL:

sdetable -o update_dbms_stats -t <table> [-K <keyword>]
[-m {VACUUM | VACUUM_FULL}] [-n {ALL | <index_name>}]
[-i {<service> | <port#> | <direct connection>}] [-s <server_name>]
[-D <database_name>] -u <DB_user_name> [-p <DB_user_password>]
[-N] [-q]

sdetable -h
 

sdetable -?
 

Operations

Operation	Description	Examples
add_uuid_column	Adds a universal unique ID column to the table	Add a universal unique ID column
alter_column	Alters the definition of a column	Alter the definition of a column
alter_reg	Modifies a registration entry such as converting the table from single to multiversioned or single to hybrid, changing the row_id to be maintained by ArcSDE rather than by the user, or enabling/disabling archiving Only the owner of the table can alter the registration of the table. NOTE: You cannot alter the registration of the row_id column on a view.	Alter the registration of a table
create	Creates a new table	Create a table
create_index	Creates an index on a table	Create an index on a table
create_mv_view	Creates a multiversioned view	Create a multiversioned view
create_view	Creates a database management system (DBMS) view	Create a database view
delete	Deletes a table; can be used to completely delete a layer (including business table) and a view	Delete a table
delete_mv_view	Deletes a multiversioned view	Delete a multiversioned view
delete_index	Deletes an index on a table	Delete a table's index
describe	Displays a table definition; if no table name is specified, the description for all tables owned by the specified user will be returned	Describe of a table
describe_long	Displays the table definition in detail; if no table name is specified, the description for all tables owned by the specified user will be returned	Obtain a detailed description of a table
describe_reg	Displays table registration entries	Display table registration entries
grant	Grants access to a table for a user	Grant a user access to a table
list	Lists column data for a given field value	List column data
list_grantees	Returns a list of all users who have been granted access to the specified table along with the specific permissions the users have been granted on the table.	List all users who have access to a table and the type of access they have been granted
load_only_io	Puts table in load-only input/outpu (I/O) mode, which drops the spatial index and makes the table unavailable to ArcSDE clients Used primarily to improve performance when bulk loading data	Switch a table to load-only I/O mode, then back to normal I/O mode
migrate	On Oracle, the migrate operation can migrate a LONG RAW attribute column to a BLOB data type provided the configuration keyword specified with the -k option contains an ATTRIBUTE_BINARY parameter set to BLOB. If run against the business table of a feature class in Oracle or SQL Server, this operation also migrates the geometry storage for the associated layer to the target specified by GEOMETRY_STORAGE parameter of the keyword specified by the -k option. If run against the business table of a raster catalog in Oracle, migrate also converts the associated LONG RAW columns in the raster auxiliary and raster block tables to BLOB, if the configuration keyword specified with the -k option contains a RASTER_STORAGE parameter set to BLOB. If run against the business table of a raster in Microsoft SQL Server, Oracle, or PostgreSQL, migrate converts the binary raster columns to ST_Raster, provided the configuration keyword specified with the -k option contains a RASTER_STORAGE parameter set to ST_RASTER. You must own the table to be able to migrate storage types in it.	Migrate a column to another storage type
normal_io	Puts table in normal I/O mode This re-creates the indexes and makes the table available to client applications.	Switch a table to load-only I/O mode, then back to normal I/O mode
populate_uuid_column	Populates the universal unique ID column	Populate the universal unique ID column of a table
rebuild_index	Rebuilds the raster, spatial, versioned, and/or XML indexes of the specified objects	Rebuild an index
register	Registers a table in the geodatabase table registry (TABLE_REGISTRY or SDE_table_registry) The register operation is also used to register a table as versioned and enable it for archiving. You must be the owner of the table to register it as versioned or enable archiving on it.	Register a table
rename	Renames a table Note: Do not use sdetable -o rename to rename a business table you created in ArcGIS Desktop or an ArcGIS Engine application. Also do not use rename for a table in DB2; renaming tables is not supported for tables in ArcSDE geodatabases in DB2.	Rename a table
revoke	Removes access to a table for a user	Remove access to a table
truncate	Deletes all records from a table	Delete all records from a table
unregister	Removes the corresponding record for a table from the ArcSDE table registry To unregister a table, it cannot have been registered as versioned or registered with the geodatabase. Only the owner of the table can unregister it.	Remove a table from the ArcSDE table registry
update_dbms_stats	Updates RDBMS table and/or index statistics Only the owner of the table can update its statistics.	Update database statistics

 

Options

Options	Description
-a	A comma-separated list of a the column names included in the view Use this option if you want the columns in the view to have different names than they did in the source table. The order in which you list these should correspond to the columns specified with the -c option. Used only with the create_view operation
-A	Type of access: SELECT, UPDATE, INSERT, or DELETE Used only with the grant and revoke operations
-c	For the alter_reg or the registration operation, specifies the row ID column name This option requires using -C. If using the create_view operation, use a comma-separated list of column names. For the add_uuid_column and the populate_uuid_column operations, specifies the uuid column to be added or populated
-C	Row_ID column type: SDE: The row_ID column is maintained by ArcSDE. Tables that are registered with the geodatabase must be maintained by ArcSDE. Your application must not alter the values of the row_ID column. USER: The row_ID column is user maintained. Your application maintains the row_ID. NONE: No row_ID column created. The table_registry does not have a reference to a row_ID column. You cannot use the -c option with this choice.
-d	Column definition; used only with the create operation
-D	Database or data source name (not supported on Oracle)
-F	Forces a multiversioned table with edits to be converted to a nonversioned table, with loss of edits The records stored in the delta tables (and therefore, your edits) are lost when they are dropped. Used only with the alter_reg and unregister operations
-h or -?	Use either of these options to see the usage and options for the command. Note: If using a C shell, use -h or "-\?".
-H	Registers the table as either visible or hidden; used only with the register and alter_reg operations
-i	ArcSDE service name, port number, or direct connection information (default: esri_sde or 5151)
-I	Inherit privilege to grant The grant option is included with the granted privilege. For example, if user A grants user B update privileges on a table, the -I option indicates that user A also wants to grant user B the ability to grant other users update privileges on that table.
-k	Configuration keyword present in the DBTUNE table (default: DEFAULTS)
-K	Used only with the update_dbms_stats operation. Specifies one of the following update DBMS statistics keywords: a—UPDATE_ADDS_TABLE_STATS b—UPDATE_BUSINESS_TABLE_STATS d—UPDATE_DELETES_TABLE_STATS f—UPDATE_FEATURE_TABLE_STATS r—UPDATE_RASTER_TABLE_STATS x—UPDATE_XML_TABLE_STATS
-L	Enables (on) or disables (off) a ROW_LOCK on a registered table; used only with the register and alter_reg operations
-m	Only used with the update_dbms_stats operation. Denotes the update DBMS statistics mode Specify if running against a SQL Server, Informix, or PostgreSQL database; options are as follows: SQL Server with fullscan with sample <#> percent with sample <#> rows with resample Informix low medium high PostgreSQL VACUUM VACUUM_FULL There is no need to specify the -m option if running update_dbms_stats against a DB2 database. Beginning with ArcGIS Server 9.2, do not use the -m option when executing update_dbms_stats on Oracle databases. Consult your DBMS documentation for details on modes for updating statistics.
-M	Minimum row ID Used only with the register and alter_reg operations
-n	When used with the update_dbms_stats operation, the value passed with the -n option can be either ALL for all indexes or a particular index name. If you specify this option, only indexes are updated. Do not specify ALL if running against a SQL Server table that contains a Geometry or Geography column. When used with the create_index or delete_index operations, -n specifies the name of the index to create or delete.
-N	No verification is performed; the operation begins immediately after being invoked.
-o	Operation
-p	DBMS user password
-q	Quiet—all titles and warnings are suppressed.
-Q	Index is unique.
-R	[{SINGLE | MANY}]: Sets the row ID allocation method for a row ID column maintained by ArcSDE Set the method to SINGLE if records are infrequently inserted into the table. Metadata tables are examples of these types of tables. Setting the method to SINGLE causes the session to get a single ID value each time a record is inserted into the table. Setting the method to MANY (the default) causes the session to fetch a block of ID values each time an insert occurs. The next block is not fetched until enough inserts have occurred to exhaust the current block of IDs. Use MANY for regular business tables that have many rows inserted during a session. This option is currently only implemented for Oracle to avoid gaps in the metadata tables' row_ID columns maintained by ArcSDE.
-s	ArcSDE server host name (default: localhost)
-S	For the registration or alter_reg operations, specifies the registration description string For the alter_column operation, specifies the column description Descriptions are limited to 65 characters.
-t	Table name If you do not own the table, qualify the table name as "owner.table". If you are using the create_view operation, use a comma-separated list of table names.
-T	When used with the rename operation, -T specifies the new table name. When used with the create_mv_view or create_view operations, specifies the view's name.
-u	DBMS user name
-U	When used with the grant or revoke operations, -U specifies the user or group to whom table access is granted or revoked. When used with the describe_reg operation, it specifies the user who owns the table.
-v	Column value
-V	Specifies if the table is registered as single versioned (SINGLE), multiversioned (MULTI), or hybrid (HYBRID) Registering a table as single versioned means you can perform nonversioned edits; registering as multiversioned lets you do versioned editing and allows you to enable archiving and geodatabase replication. Registering as hybrid allows you to do either versioned or nonversioned edits but does not allow for archiving or replication. Specifying the version name when reregistering a table as single versioned allows you to specify which version's delta tables will be moved into the table.
-w	Structured Query Language (SQL) WHERE clause
-x	When used with the alter_column operation, indicates whether a column allows or disallows NULL values (ALLOW_NULLS or DISALLOW_NULLS) When used with the rebuild_index operation, specifies the type of index to be rebuilt: either RASTER, SPATIAL, XML, VERSIONED, or ALL RASTER rebuilds the indexes for the Raster tables. SPATIAL rebuilds the spatial index table. XML rebuilds the XML indexes. VERSIONED rebuilds the indexes for the Adds and Deletes tables. ALL rebuilds raster, spatial, XML, and versioned indexes on the table; if -x is not specified, ALL is assumed.
-y	Enables or disables geodatabase archiving To enable archiving with the register or alter_reg operations, you must specify the name of the history table, the names of the history row_ID, from date, and to date columns in the history table. Archiving can only be disabled using the sdetable command if you use the alter_reg operation.
-z	Used with the alter_column operation; specifies the data type you want the column to be, either INT16, INT32, FLOAT32, or FLOAT64. Do not alter the data type of a column maintained by ArcSDE. Allowed data type conversions are dependent on the DBMS used to store the data. Consult your DBMS documentation for specific information on data type conversions.

 

Discussion and Examples

Add a universal unique ID column

To add a universal unique ID column to a table, use the add_uuid_column operation. In the example below, a universal unique ID column, parceluuid, is being added to the table victoria.

sdetable -o add_uuid_column -t victoria -c parceluuid -u av -p mo

Alter the definition of a column

Use the alter_column operation to alter a column's definition. You can change the NULL constraint or the data type if the DBMS allows it. This is limited to the following data type conversions:

• INT16 to INT32
• INT32 to FLOAT32
• INT32 to FLOAT64
• FLOAT32 to FLOAT64

Note: Whether the data type conversion is allowed at the database level depends on the DBMS. If the column type conversion listed above fails on a certain DBMS, check the DBMS documentation to determine if the attempted data conversion is allowed in that DBMS.

You cannot alter the definition of a column that has been registered as maintained by ArcSDE.

The following example alters a column, nest_size, to allow null values and changes the data type to FLOAT64.

sdetable -o alter_column -t birds -c nest_size -x ALLOW_NULLS -z float64 -u av -p mo -N -q

Alter the registration of a table

You can alter the registration of a table from and to one of three options for editing: single, multiversioned, or hybrid. If registered as multiversioned, you can enable or disable archiving. In the example below, the registration of the table victoria is being changed to multiversioned (-V) and enabled for archiving (-y).

sdetable -o alter_reg -t av.victoria -c objectid -C sde -V multi -y h_victoria,h_rowID,h_from,h_to
-u av -p mo -N

You can also alter the registration of a table to change how the row_id column is maintained. In the following example, the row_id column (object_id) is altered to be user maintained.

sdetable -o alter_reg -t av.birds -c object_id -C user -u av

NOTE: You cannot alter the registration of the row_id column of a view if the row_id column for the view has already been defined. You can use the sdetable -o describe_reg command (see example under "Display table registration entries" to determine if the row_id column is already defined on the view.

Create a table

You can create a new table in the database using the create operation.

To create a business table, supply the table name and field specifications. The field specification uses standard SQL syntax to specify a field list, so you cannot use any SQL-reserved words as field names. The field specification may use any data type defined by ANSI SQL89. The field specification of sdetable accepts standard ArcSDE data types, which it maps to DBMS-native data types. The standard ArcSDE data types are as follows:

smallint(n) where: 0 < n <= 4
int16
int32
int64
float32
float64
integer(n) where: 4 < n <= 10
float(n,m) where: 0 < n <= 6, 0 < m <= DBMS limit
double(n,m) where: 6 < n <= DBMS limit, 0 < m < DBMS limit
string(n) where: 0 < n <= DBMS limit
blob
clob
uuid
nstring
nclob
date

In the case of ArcSDE for Informix, ArcSDE for DB2, and ArcSDE for Oracle using the spatial type for Oracle, the spatial data types are also accepted. Creating a business table with a spatial type does not create a feature class. To create a feature class from an empty business table containing a spatial type, use the sdelayer -o add command. If the table contains records, use the sdelayer -o register command to register the spatial table as a feature class.

Spatial data types

st_point
st_curve
st_linestring
st_polygon
st_multipoint
st_multicurve
st_multilinestring
st_multipolygon
st_multisurface
st_surface
st_geometry
st_geometrycollection

sdetable -o create -t victoria -d "name string(20), tot_pop integer(9)" -k vict -u av -p mo -s ultra -i esri_80

The table created as a result of executing the above command contains two columns: a string column (name) and an integer column, tot_pop.

Tables are not versioned when they are created. Use ArcGIS Desktop or the alter_reg operation to reregister them as multiversioned or hybrid if you want to performed versioned edits of the table with  ArcGIS applications. A row_ID column that will store the unique ObjectID to the edited rows must be added to the table, or an existing one must be named. The row_ID column must be managed by the sde user. The table victoria is reregistered as multiversioned. The column ObjectID is added as the row_ID column and is populated by ArcSDE with unique ID values.

If you have the DBTUNE parameter UNICODE_STRING set to true (Oracle and SQL Server DBMSs only), and you want to create a table that contains a Unicode string column, you must specify nstring for the data type instead of string. For the previous example, the name column would be defined as nstring.

sdetable -o create -t victoria -d "name nstring(20), tot_pop integer(9)" -k vict -u av -p mo -s ultra -i esri_80

Create an index on a table

To improve query performance, you can create an index on a table. The create_index operation creates an index on a business table column. This example creates an index (index1) on column parcel_no.

sdetable -o create_index -t victoria -c parcel_no -n index1 -u av -p morti26 -i esri_40

You can create aggregate indexes by specifying a comma-separated list of columns. The next example creates an aggregate index on the parcel_no and zone_no columns.

sdetable -o create_index -t victoria -c parcel_no,zone_no -n index2 -u av -p morti26 -i 6000

Create a multiversioned view

To access the attribute information of a multiversioned feature class from a client application that does not recognize versioning, you can create a multiversioned view. Multiversioned views should only be used with simple geodatabase objects (those not involved in geodatabase functionality such as networks, parcel fabrics, representations, relationship classes, schematics, terrains, replicas, archiving or topologies), and can only be applied to one versioned table or feature class. You cannot create a multiversioned view on a view. The following example creates a multiversioned view, world_imv_view, on the base table of the feature class world.

sdetable -o create_mv_view -T world_imv_view -t world -u av -p morti26 -s alsace -D storm

Unlike a standard ArcSDE view, you do not choose columns or define a WHERE clause. The schema of a multiversioned view is identical to that of the business table on which it is based.

When querying or editing through a multiversioned view, you need to specify which version. Using the DBMS SQL interface, set the current version by executing the stored procedure or function set_current_version. (The specific name of this file varies from one DBMS to another. See the topics "Editing versioned data in <DBMS> using multiversioned views and SQL" in the SQL section of the ArcGIS Server or ArcGIS Desktop help for information on the stored procedures used for each DBMS.)

Note: Multiversioned views are not supported in geodatabases in DB2 on the z operating system.

Create a database view

You can define views on a single feature class or table (for instance, to limit a user's access to the columns in a feature class), between two feature classes, or between a feature class and a table, or you can create more complex views containing subqueries. This example creates a view, view_victoria, which includes the parcel_no column from the victoria feature class and the address from the province table, for all records where the province is 10 and the zone_no value in the victoria feature class equals the zone_no value in the province table.

sdetable -o create_view -T view_victoria -t victoria -c victoria.parcel_no,province.address
-w "province=10 and victoria.zone_no = province.zone_no" -u av -p morti26

You must list the columns you want included in the view. If you are creating a view using multiple tables as shown above, you must also include a WHERE clause.

The following example creates a view that selects all roads categorized as freeways from a roads table. It also specifies different names for the columns in the view using the -a option. The number and order of the columns specified for -a must match the columns selected from the table with the -c option.

sdetable -o create_view -T fwy_vw -t roads -c objectid,name,type -a id,freeway,code -w "type='FWY'"
-i 6000 -D pubworks -u editor -p owpen4u

In addition to creating the view, sdetable –o create_view registers the view with ArcSDE. This means an entry for the view is placed in the system table TABLE_REGISTRY (SDE_table_registry in SQL Server databases) and an entry for each of the view’s columns in COLUMN_REGISTRY (SDE_column_registry in SQL Server databases).

You can also create spatial views. That means you include the spatial column (for example, the SHAPE column) in the view. These views can only reference one table that contains a spatial column. If you want to include attribute information from another table containing a spatial column in your spatial view, you must first create a nonspatial view of that second table. You can then create your spatial view using one table with a spatial column and attribute information from the nonspatial view. Spatial views should only be created on nonversioned tables.

When you define a spatial view, you must include the object ID field from the feature class in the list of returned columns (-c). If you also include an attribute table in the view and include its object ID field, the object ID field for the attribute column will be renamed in the view since there cannot be two columns with the same name.

Delete a table

To delete a business table or view, use the delete operation. The associated feature class (layer), if one exists, is deleted as well.

sdetable -o delete -t victoria -u av -p morti26 -s ultra -i esri_40

Delete a table's index

To drop a table's index, use the delete_index operation.

sdetable -o delete_index -n index1 -u har -p sugar -i 6544

Not all DBMSs maintain unique index names, in which case you must qualify the index name with the table name (for example, victoria.index1).

Delete a multiversioned view

Use the delete_mv_view operation to remove a multiversioned view from a table. This example drops the multiversioned view from the world business table.

sdetable -o delete_mv_view -t world -u av -p morti26 -D thedb -s theirserver -i sde:sqlserver:thedb\myinstance

The view can also be dropped using the ArcSDE C-API function SE_table_drop_mv_view.

Describe a table

Use the describe operation to display a table's definition, as shown in the example below.

sdetable -o describe -t victoria -u av -p morti26 -i esri_40

To list rows of a business table, you must specify the table, a column name, and a value of the specified column. An implicit query is performed on the table to fetch and display the rows that have a column equal to the value.

Obtain a detailed description of a table

To list the business table definition in column detail, use the describe_long operation.

sdetable -o describe_long -t victoria -u sasha -p polar.bear -i esri_40

The describe_long operation lists the following properties of each column:

Column Owner
Column Table
Column Name
SDE Column Type
Column Size
Decimal Digits
Null Allowed?
Layer ID

Display table registration entries

Use the describe_reg operation to obtain information about a table's registration status. Use -t to return information for one table or -U to return information for all tables owned by a specific user. Otherwise, describe_reg returns all tables. In the example below, the registration entry for one table, landmarks, is returned.

sdetable –o describe_reg –t moe.landmarks -i sde:sqlserver:instance1 -D spdata

Attribute        Administration Utility

Table Database          : SPDATA
Table Owner             : MOE
Table Name              : LANDMARKS
Registration Id         : 47
Row ID Column           : OID
Row ID Column Type      : SDE Maintained
Row ID Allocation       : Many
Row Lock                : Not Enable
Minimum Row ID          : 1
Dependent Objects       : None
Dependent Object Names  : 147
Registration Date       : 01/24/06 09:41:00
Config. Keyword         : DEFAULTS
User Privileges         : SELECT, UPDATE, INSERT, DELETE
Visibility              : Visible

Grant a user access to a table

Use the grant operation to allow a user specific types of access to a table. (SELECT, INSERT, UPDATE, DELETE). In the following example, user hp is given select permission to the table hydrants owned by RJP.

sdetable -o grant -t sde.RJP.hydrants -U hp -A SELECT,INSERT,UPDATE,DELETE -u RJP -D arcsde -i 8000

Enter Database User password:

ArcSDE 9.2 for SQL Server Build 723 Wed Aug  3 16:02:07  2005
Attribute      Administration Utility
------------------------------------------------------------
Permissions successfully granted on table sde.RJP.hydrants

To grant privileges to (or revoke privileges from) a Windows group, you need to preface the group name with the word "group:". In the following example, UPDATE, INSERT, and DELETE privileges are being granted to the OS group sdeusers in a DB2 database:

sdetable -o grant -t population -U group:sdeusers -A UPDATE,INSERT,DELETE -u cbrel
-p open.sesame -d econdev -i sde_4000

List column data

You can list column data for a given field value using the list operation.

sdetable -o list -t sde.cat.ACCIDENTS -c ACCTYPE -v 11 -u cat -p zoom -i 5999

Attribute          Administration Utility

OBJECTID: 12
MILE: 120
DAY_NIGHT: D
ALCOHOL: 0
HITRUN: 0
ACCTYPE: 11
WEATHER: RAIN
MEANS: 7
SPEED: 38

OBJECTID: 23
MILE: 1432
DAY_NIGHT: N
ALCOHOL: 1
HITRUN: 0
ACCTYPE: 11
WEATHER: CLEAR
MEANS: 7
SPEED: 40

List all users who have access to a table and the type of access they have been granted

You can determine which users in the database have access to a specific table and the permissions the users have been granted on the table.

In this example, all users who have access to the business table parks are listed along with the permission each has to the parks table.

sdetable -o list_grantees -t parks -i sde:sqlserver:myserver\ssinstance -D mydatabase -s myserver -u guil -p lkjh

Attribute           Administration Utility

2 grantees on table: parks

User Name: rolf
Privileges: SELECT,INSERT,UPDATE,DELETE

User Name: ernst
Privileges: SELECT

Migrate a column to another storage type

The following migrations are possible using the migrate operation with the sdetable command:

DBMS	Column type	Migration
Oracle	Attribute	LONG RAW to BLOB
	Raster	LONG RAW to BLOB LONG RAW to ST_RASTER BLOB to ST_RASTER
	Geometry	LONG RAW to BLOB LONG RAW to ST_GEOMETRY BLOB to ST_GEOMETRY SDO_GEOMETRY to ST_GEOMETRY
PostgreSQL	Raster	BYTEA to ST_RASTER
SQL Server	Raster	IMAGE to ST_RASTER VARBINARY(MAX) to ST_RASTER
	Geometry	SDEBINARY to GEOMETRY SDEBINARY to GEOGRAPHY OGCWKB to GEOMETRY OGCWKB to GEOGRAPHY

Note: You must install ST_Raster separately in the geodatabase using the sdesetup command with the install_st_raster operation.

To migrate these columns, you specify a DBTUNE configuration keyword with the -k option that includes the storage parameters set to the new storage type you want. For example, to migrate the data type used for a raster columns, the DBTUNE configuration keyword you specify must contain an RASTER_STORAGE parameter that is set to the new storage type you want the raster dataset or raster catalog to use. Similarly, if you want to migrate the shape column of a feature class to a new storage type, you must specify a DBTUNE configuration keyword that contains a GEOMETRY_STORAGE parameter set to the new storage type.

In Oracle databases, the migrate operation can change the storage of an attribute column from LONG RAW to BLOB by specifying a DBTUNE configuration keyword that has the ATTRIBUTE_BINARY parameter set to BLOB. In the following example, the channels table contains a LONG RAW column that is migrated to BLOB storage using the configuration keyword BLOB_ATTRIBUTE.

sdetable -o migrate -t cyu.channels -k BLOB_ATTRIBUTE -s server2 -u cyu -i 2222

If the migrate operation is executed against the business table of a raster catalog, the raster column in the raster auxiliary (sde_aux_<id>) and block_data column of the raster blocks (sde_blk_<id>) tables are converted to BLOB or ST_RASTER in Oracle databases, or ST_RASTER in PostgreSQL or SQL Server databases provided the keyword specified with the -k option contains a RASTER_STORAGE parameter set to BLOB or ST_RASTER.

The following example shows the migration of raster columns for the raster catalog business table, ras_area2 in a SQL Server database.

sdetable -o migrate -t snar.ras_area2 -k RASTER -D gdbdb -i sde:sqlserver:ssinstance1 -u snar

In this example, the raster in a business table baseimg in a PostgreSQL database is migrated from bytea storage to ST_Raster.

sdetable -o migrate -t tex.baseimg -k ST_RASTER -D ourgdb -s server1 -u tex -i sde:postgresql:server1

If the migrate operation is executed against the business table of a feature class in Oracle or SQL Server, the geometry storage of the feature class will be migrated to the geometry storage type in the DBTUNE configuration keyword you specify with the -k option.

In the following example, the geometry storage of a feature class in SQL Server is migrated to use the Microsoft geometry type by specifying a keyword, GEOMETRY, that contains the configuration parameter GEOMETRY_STORAGE set to GEOMETRY.

sdetable -o migrate -t val.neighborhoods -k GEOMETRY -D plangdb -i 46000 -u val

You can migrate multiple column types at once by specifying a configuration keyword with the -k option that includes multiple supported migration parameters. For example, you could run the migrate operation on the business table of a feature class in Oracle and migrate the shape and LONG RAW attribute columns by specifying a keyword that contains both a GEOMETRY_STORAGE and ATTRIBUTE_BINARY parameter.

In the following example, a custom configuration keyword, MIGRATE, is specified to migrate the geometry storage of the poles feature class from SDEBINARY to ST_GEOMETRY storage and a binary attribute column in the business table from LONG RAW to BLOB. (The MIGRATE keyword was added to the DBTUNE table using the sdedbtune command. The keyword contains the parameter ATTRIBUTE_BINARY set to BLOB and the GEOMETRY_STORAGE parameter set to ST_GEOMETRY.)

sdetable -o migrate -t rufus.poles -k MIGRATE -s s2 -u rufus -i 9009

For all of these examples, you will receive an error message if any of the following is true:

• The data is spatial and it is stored using basic precision.
• You attempt to convert the data type of a spatial column, but the table does not contain an object ID column.
• The user specified with the –u option is not the owner of the table.
• The keyword specified with the –k option contains a RASTER_STORAGE, ATTRIBUTE_BINARY, or GEOMETRY_STORAGE parameter set to an unsupported type or is set to the same geometry storage type that the feature class is already using.
• You attempt to use the migrate operation in a 9.2 or lower geodatabase in Oracle.
• You attempt to use the migrate operation in a 9.3 or lower geodatabase to convert raster columns to the ST_RASTER type.
• You attempt to use the migrate operation in a 9.3 or lower geodatabase to convert geometry columns in a geodatabase in SQL Server.

As indicated in the first bullet point, if you plan to migrate a geometry column in the table, you must first migrate it from basic (low) to high precision. This can be done using the sdelayer command with the alter operation or the Upgrade Spatial Reference geoprocessing tool.

Additionally, if the table does not have an object ID column, you can either use the alter_reg operation of the sdetable command to add an object ID column, or you can register the table with the geodatabase using the tools in ArcCatalog, which automatically adds an object ID column.

When migrating data storage types in an Oracle database using the migrate operation, a new segment is created in the database to which the data is copied. Once the migration is complete, the metadata gets pointed to the new segment and the old one is deleted. That means that at one point during the migration, there are two copies of the data. Therefore, the database must have enough space to store two copies of the data. Note: This is mostly an issue when converting from LONG RAW to BLOB raster data.

Switch a table to load-only I/O mode, then back to normal I/O mode

You can put a table in load-only I/O mode when you want to load large amounts of data at one time. When finished, you should switch it back to normal I/O mode.

sdetable -o normal_io -t sde.RJP.hydrants -u RJP -p windowpane -D mystuff

Attribute     Administration Utility
----------------------------------------------------------------
Successfully changed mode for sde.RJP.hydrants

Populate a universal unique ID column of a table

Use the populate_uuid_column operation to populate the uuid of a table.

sdetable -o populate_uuid_column -t victoria -c parceluuid -u rocket -p bubby -i esri_40

Rebuild an index

To rebuild the index of a specified object, use the rebuild_index operation. Here, all the indexes on the table landmarks are rebuilt.

sdetable -o rebuild_index -t landmarks -u squeaky -s remote -D bigd

In this example, the indexes on the raster tables associated with the business table vegetation are rebuilt.

sdetable -o rebuild_index -t vegetation -x RASTER -u vijay -s remote -D bigd

Register a table

Use the register operation to register a table in the TABLE_REGISTRY system table. The following table, world, is registered with a row ID column (ID) maintained by ArcSDE.

sdetable -o register -t world -c ID -C SDE -u dart -p wolfy -i 3355

You will need to register the table with a row ID maintained by ArcSDE to use it with an ArcSDE client application such as ArcGIS Desktop.

Rename a table

The rename operation can be used to change the name of a table. In the following example, the table world is renamed world2000.

sdetable -o rename -t world -T world2000 -u dart -p wolfy -i 3355

NOTE: Not all DBMSs support table renaming. For example, DB2 databases do not.

Remove access to a table

You can take away a user's access to a table with the revoke operation. In the example below, user doc's UPDATE privilege to table victoria is revoked.

sdetable -o revoke -t victoria -U doc -A UPDATE -u av -D bigd -s server2 -i sde:db2:bigd

Delete all records from a table

To remove all records from a table, use the truncate operation. Truncating deletes all the records of the business table and the associated feature class, leaving the definition of the table and feature class intact. In the following example, all records from the table victoria are removed, but the table definition remains in the database.

sdetable -o truncate -t victoria -u av -p mo -s ultra -i esri_40

Remove a table from the ArcSDE table registry

Use unregister to remove a table from the TABLE_REGISTRY table (SDE_table_registry in SQL Server databases).

sdetable -o unregister -t av.world -u av -p mo -i 7654

NOTE: Tables cannot be unregistered if they have been registered as multiversioned, are part of a geodatabase object, or have a spatial column. In all cases, the dependencies must be removed first or you must use the -F option.

To manually remove the dependencies, unregister the table from the geodatabase using ArcCatalog (if it has been registered with the geodatabase) and remove the spatial column (if it has one) using the sdelayer -o delete operation.

Alternatively, you can use the -F option to force the unregistering of the table. However, when you do so, all edits stored in the delta tables are lost. The table will not be unregistered with the geodatabase.

Update database statistics

The update_dbms_stats operation updates DBMS table and/or index statistics. These are specific to the type of DBMS you are using.

Oracle

Here, statistics on all indexes on the table dist_centers in an Oracle database are being updated:

sdetable -o update_dbms_stats -t hermann.dist_centers -n ALL -u hermann -p open.up -s out -i 3948

NOTE: Beginning with ArcGIS Server 9.2 for Oracle, update_dbms_stats uses the Oracle DBMS_STATS package and not ANALYZE. Consult your Oracle9i or 10g documentation for details on what the DBMS_STATS package does.

SQL Server

On SQL Server databases, the syntax used with the -m option corresponds to that used with the Transact-SQL statement UPDATE STATISTICS. Include "with fullscan" in the -m option as shown in this example, to use all the rows in the table to gather the statistics.

sdetable -o update_dbms_stats -t dao.students -m "with fullscan" -u dao -p wachtwoord -D mydb -i 8765

If you are updating statistics on a very large table, rather than do a full scan, you might use one of the following options instead:

• sample <#> percent, which uses the specified percent of the table to use when collecting statistics:

  sdetable -o update_dbms_stats -t lilly.books -m "with sample 50 percent" -u lilly -p senha -D share -i sde:sqlserver:share

• sample <#> rows, which uses the specified number of rows to collect statistics
• resample, which uses a sampling ratio

Informix

Below, statistics are updated in medium mode on the table wells in an Informix database:

sdetable -o update_dbms_stats -t wells -m medium -u rolf -p kennwort -i 6543

For tips on when to use low, medium, or high, consult your IBM Informix documentation on updating statistics.

PostgreSQL

When updating statistics in a PostgreSQL geodatabase, you can specify whether to mark expired records in tables and indexes to be reused (VACUUM) or physically reclaim the space no longer used by expired records in tables and indexes (VACUUM_FULL). VACUUM_FULL places an exclusive lock on the table being vacuumed, so be sure no one else is connected to the table when this command is run.

In the example below, the expired records in the table habitat are being eliminated, the space they used returned to the operating system, and database statistics updated.

sdetable -o update_dbms_stats -t pavel.habitat -m VACUUM_FULL -u pavel -p napo -D keeper -i sde:postgresql:keeper

For details on the VACUUM operation, consult the PostgreSQL documentation at www.postgresql.org/docs.

DB2

In the following example, the index statistics for table orchards in a DB2 database are being updated:

sdetable -o update_dbms_stats -t pp.orchards -n orchard_idx -u pp -p llama -i 1357

 

Home Copyright © Environmental Systems Research Institute, Inc. 2004 - 2010.