生成xml并更新表字段

 

 

 

--生成tm_xUnique对应的xml

DECLARE @x1 XML

SET @x1 = 

    CAST((SELECT a.name AS "@TableName",

  'uq_' + a.name + '_id_num' AS "UniqueConstraint/uq_name", 

  '(' + STUFF((

  SELECT ',' + name FROM sys.columns 

  WHERE a.object_id = object_id 

  AND ( name LIKE '%s_cID' ESCAPE 's' OR name LIKE '%s_cNum' ESCAPE 's' )

  FOR XML PATH('')),1,1,'')+ ')' AS "UniqueConstraint/uq_fields"

FROM sys.objects a

WHERE type = 'u' 

    AND a.name in (select tm_ctable from xt_tableml)

FOR XML PATH('Table')) AS XML)

 

UPDATE xt_tableml SET tm_xUnique