suresh738

How to query table/fc with domain values in SQL SDE

Blog Post created by suresh738 on Jan 8, 2016

DECLARE @TABLE_NAME NVARCHAR(max);

DECLARE @column_name VARCHAR(max);

DECLARE @domain_name VARCHAR(max);

DECLARE @sql_columns VARCHAR(max);

DECLARE @sql_where_clause VARCHAR(max);

DECLARE @sql_from_clause VARCHAR(max);

DECLARE @column_cursor CURSOR;

DECLARE @rowcnt_txt NVARCHAR(max);

DECLARE @SQLString NVARCHAR(max);

DECLARE @ParmDefinition NVARCHAR(500);

DECLARE @seperator NVARCHAR(2);

DECLARE @column_counter INT;

DECLARE @data_owner VARCHAR(max);

 

 

SET @TABLE_NAME = 'projects';

SET @data_owner = 'data';

 

 

IF Object_id('tempdb..#CLASS_DEFS') IS NOT NULL

  DROP TABLE #class_defs

 

 

CREATE TABLE #class_defs

  (

     tablename  NVARCHAR(max),

     definition XML

  )

 

 

IF Object_id('tempdb..#TABLE_DOMAINS') IS NOT NULL

  DROP TABLE #table_domains

 

 

CREATE TABLE #table_domains

  (

     tablename  NVARCHAR(max),

     columnname NVARCHAR(max),

     domainnmae NVARCHAR(max)

  )

 

 

IF Object_id('tempdb..#DOMAIN_CODE_VALUES') IS NOT NULL

  DROP TABLE #domain_code_values

 

 

CREATE TABLE #domain_code_values

  (

     owner      NVARCHAR(max),

     domainname NVARCHAR(max),

     code       NVARCHAR(max),

     value      NVARCHAR(max)

  )

 

 

INSERT INTO #class_defs

SELECT sde.gdb_items.NAME,

       sde.gdb_items.definition

FROM   ((SELECT gdb_items.uuid AS UUID

        FROM   sde.gdb_items

               INNER JOIN sde.gdb_itemtypes

                       ON sde.gdb_items.type = sde.gdb_itemtypes.uuid

        WHERE  sde.gdb_itemtypes.NAME IN ( 'Coded Value Domain', 'Range Domain'

                                         )) AS

       Domain

        INNER JOIN sde.gdb_itemrelationships

                ON Domain.uuid = sde.gdb_itemrelationships.destid)

       INNER JOIN sde.gdb_items

               ON Domain.uuid = sde.gdb_itemrelationships.destid

 

 

INSERT INTO #table_domains

SELECT ClassDefs.tablename                              AS TableName,

       fielddef.value('Name[1]', 'nvarchar(max)')       AS FieldName,

       fielddef.value('DomainName[1]', 'nvarchar(max)') AS DomainNmae

FROM   #class_defs AS ClassDefs

       CROSS apply definition.nodes('/*/GPFieldInfoExs/GPFieldInfoEx') AS

                   FieldDefs(

                   fielddef)

WHERE  Upper(ClassDefs.tablename) LIKE Upper(

       '%' + @data_owner + '.' + @TABLE_NAME)

UNION

SELECT ClassDefs.tablename                              AS TableName,

       fielddef.value('FieldName[1]', 'nvarchar(max)')  AS FieldName,

       fielddef.value('DomainName[1]', 'nvarchar(max)') AS DomainNmae

FROM   #class_defs AS ClassDefs

       CROSS apply

       definition.nodes('/*/Subtypes/Subtype/FieldInfos/SubtypeFieldInfo')

       AS FieldDefs

                                       (fielddef)

WHERE  Upper(ClassDefs.tablename) LIKE Upper(

       '%' + @data_owner + '.' + @TABLE_NAME)

 

 

--select * from #TABLE_DOMAINS

INSERT INTO #domain_code_values

SELECT definition.value('(/GPCodedValueDomain2/Owner)[1]', 'nvarchar(max)')

       AS

       "Owner",

       definition.value('(/GPCodedValueDomain2/DomainName)[1]', 'nvarchar(max)')

       AS

       "DomainName",

       codedvalue.value('Code[1]', 'nvarchar(max)')

       AS "Code",

       codedvalue.value('Name[1]', 'nvarchar(max)')

       AS "Value"

FROM   sde.gdb_items AS items

       INNER JOIN sde.gdb_itemtypes AS itemtypes

               ON items.type = itemtypes.uuid

       CROSS apply items.definition.nodes (

                   '/GPCodedValueDomain2/CodedValues/CodedValue') AS

                                  CodedValues(codedvalue)

WHERE  itemtypes.NAME = 'Coded Value Domain';

 

 

SET @column_cursor= CURSOR

FOR SELECT column_name,

           d.domainnmae AS DomainName

    FROM   information_schema.columns i

           LEFT JOIN #table_domains AS d

                  ON i.column_name = d.columnname

    WHERE  table_name LIKE @TABLE_NAME

    ORDER  BY ordinal_position

SET @sql_columns = '';

SET @sql_where_clause ='';

SET @sql_from_clause='';

SET @seperator='';

SET @column_counter = 0;

 

 

OPEN @column_cursor;

 

 

FETCH next FROM @column_cursor INTO @column_name, @domain_name;

 

 

WHILE @@FETCH_STATUS = 0

  BEGIN

      IF ( @domain_name IS NULL )

        BEGIN

            SET @sql_columns = @sql_columns + @seperator + Char(13) + Char(10)

                               + @column_name

        END

      ELSE

        BEGIN

            SET @sql_columns = @sql_columns + @seperator + ' a'

                               + Cast(@column_counter AS VARCHAR(3))

                               + '.value as ' + @column_name

            SET @sql_from_clause = @sql_from_clause + Char(13) + Char(10)

                                   +

' left join (select code, value from #DOMAIN_CODE_VALUES where domainname='

                       + '''' + @domain_name + '''' + ') as a'

                       + Cast(@column_counter AS VARCHAR(3))

                       + ' on p.' + @column_name + '=a'

                       + Cast(@column_counter AS VARCHAR(3))

                       + '.code'

SET @column_counter = @column_counter + 1;

END

 

 

SET @seperator=', ';

 

 

FETCH next FROM @column_cursor INTO @column_name, @domain_name;

END;

 

 

CLOSE @column_cursor;

 

 

DEALLOCATE @column_cursor;

 

 

IF Object_id('tempdb..##temp_table1') IS NOT NULL

  DROP TABLE ##temp_table1

 

 

SET @SQLString = N'select ' + @sql_columns

                 + ' into ##temp_table1 from SPOT.'

                 + @TABLE_NAME + ' p ' + @sql_from_clause

 

 

EXECUTE (@SQLString)

 

 

SELECT *

FROM   ##temp_table1

ORDER  BY objectid

Outcomes