Add Field to a SQL Geodatabase using T-SQL

Sometimes a new field will need to be added quickly. The usual process of testing changes and pushing said changes to production is not very time sensitive. Here is a method I use when a new field is needed ASAP.

In this case the feature classes have been registered in the geodatabase and archiving has been enabled. This means there is an SDE base table and several SDE delta tables. The information needed to proceed is listed below. This will require DBO level access and SQL Server Management Studio or other suitable RDBMS interface like Toad.

  • Database Name = DemoData
  • New Field Name = NewField
  • New Field Type (Length) = string(50) / nvarchar(50)
    • SDE Field Type Code = Unknown
  • New Field Name = NewField
  • Feature Class Name = Marker
    • Base Table Name = Marker
    • Base Table ID = Unknown
    • Delta Table (Adds) = Unknown
    • Delta Table (Archive) = Unknown

First thing to do is find the unknown values. The SDE Field Type Code via Esri tells us that the SDE Field Type Code for String is "14".
To find the feature class base table ID run the following query.


1
2
3
4
5
6
--Identify the base table registration ID from the SDE table registry
Use DemoData
go
select * from dbo.SDE_table_registry 
 where table_name='Marker'
go

The value from field "registration_id" is the base table ID.




Now time to run the queries which will add the new field. The base table is shown in line 2, the archive delta table is in line 6 and the adds delta table is in line 10. The SDE code type is a value which will be inserted into the 5th column in the SDE column registry table. Each one of these queries must be run to successfully add a new field to a registered database.

 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
--Alter base and delta tables by adding the new field
alter table GISAdmin.Marker
 add NewField nvarchar(50) NULL
go

alter table GISAdmin.Marker_H
 add NewField nvarchar(50) NULL
go

alter table GISAdmin.a84
 add NewField nvarchar(50) NULL
go
--Insert values into the SDE column registry. 
--Fields: 1)database_name 2)table_name 3)owner aka schema 4)column_name 5)sde_type 6)column_size
--7)decimal_digits 8)description 9)object_flags 10)object_id
insert into dbo.SDE_column_registry
 VALUES ('DemoData','Marker','GISADMIN','NewField',14,50,0,NULL,4,NULL)
go

If the feature class is not enabled for archiving than disregard the archive delta table query.

Comments

Post a Comment

Popular posts from this blog

Updating GDB_Items XML Definitions

First, run a query to identify the feature class "PhysicalName" or UID from GDB_ITEMS. Note: The physical name appears as DBNAME.SCHEMA.FCNAME. Use one of the two for the WHERE clause in the other queries. First Query USE [DBName] SELECT ITEMS.ObjectID ,ITEMS.UUID ,ITEMTYPES.Name as ItemType ,ITEMS.[PhysicalName] ,ITEMS.[Definition] FROM [sde].[GDB_ITEMS] as ITEMS INNER JOIN [sde].[GDB_ITEMTYPES] ITEMTYPES ON [ITEMS].[ Type ] = [ITEMTYPES].[UUID] WHERE [PhysicalName] like '%FC Name%' In the results, click the XML link under the Definition field. You should see something similar to the XML below. Notice the highlighted line of code. This is the "Remarks" field. The node "IsNullable" is set as "false". I want the Remarks field to allow NULL values and would like to update it from "false" to "true". Note: The base and delta tables should be updated as well. XML Definition View <DEFeatu...
Read more

Enterprise Geodatabase Maintenance

I have several enterprise level geodatabases (GDB) to manage. Instead of setting up maintenance scripts for each project GDB, I decided to write one script to manage them all. (Get the movie reference?) This script requires two directories to be setup 1) OwnerFileDir and 2) AdminFileDir. The first will store several SDE owner connection files; one per database. Owner is usually "dbo" or "sde". The second will store several Esri object owner connection files; one per database. The Esri object owner is the user that owns each object's schema e.g. < DatabaseName >.< Schema >.< PolygonFeatureClassName >. 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 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 ...
Read more