Skip to content

Instantly share code, notes, and snippets.

@trnktms

trnktms/missing-field-values.sql

Last active May 6, 2019 15:30
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save trnktms/d966e8fe852228b17a31c3cb4bbde477 to your computer and use it in GitHub Desktop.
Save trnktms/d966e8fe852228b17a31c3cb4bbde477 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
missing-field-values.sql
PRINT 'Fields values which are in TDS but not in Unicorn - Shared Fields'
select tds.[ItemId],
unicorn.[ItemId],
tds.[FieldId],
tds.[Value] as 'TDS Value',
unicorn.[Value] as 'Unicorn Value'
from [tds910_Master].[dbo].[SharedFields] as tds
left join [unicorn910_Master].[dbo].[SharedFields] unicorn
on tds.ItemId = unicorn.ItemId
and tds.FieldId = unicorn.FieldId
where unicorn.FieldId is null
and tds.FieldId != 'D9CF14B1-FA16-4BA6-9288-E8A174D4D522' /* __Updated */
and tds.FieldId != '8CDC337E-A112-42FB-BBB4-4143751E123F' /* __Revision */
and tds.FieldId != '25BED78C-4957-4165-998A-CA1B52F67497' /* __Created */
and tds.FieldId != 'BADD9CF9-53E0-4D0C-BCC0-2D784C282F6A' /* __Updated by */
and tds.FieldId != '52807595-0F8F-4B20-8D2A-CB71D28C6103' /* __Owner */
and tds.FieldId != '5DD74568-4D4B-44C1-B513-0AF5F4CDA34F' /* __Created by */
and tds.FieldId != '001DD393-96C5-490B-924A-B0F25CD9EFD8' /* __Lock */
and tds.FieldId != '40E50ED9-BA07-4702-992E-A912738D32DC' /* Blob */
PRINT 'Field values which are in TDS but not in Unicorn - Versioned Fields'
select tds.[ItemId],
unicorn.[ItemId],
tds.[FieldId],
tds.[Value] as 'TDS Value',
unicorn.[Value] as 'Unicorn Value'
from [tds910_Master].[dbo].[VersionedFields] as tds
left join [unicorn910_Master].[dbo].[VersionedFields] unicorn
on tds.ItemId = unicorn.ItemId
and tds.FieldId = unicorn.FieldId
and tds.Language = unicorn.Language
where unicorn.FieldId is null
and tds.Value != ''
and tds.FieldId != 'D9CF14B1-FA16-4BA6-9288-E8A174D4D522' /* __Updated */
and tds.FieldId != '8CDC337E-A112-42FB-BBB4-4143751E123F' /* __Revision */
and tds.FieldId != '25BED78C-4957-4165-998A-CA1B52F67497' /* __Created */
and tds.FieldId != 'BADD9CF9-53E0-4D0C-BCC0-2D784C282F6A' /* __Updated by */
and tds.FieldId != '52807595-0F8F-4B20-8D2A-CB71D28C6103' /* __Owner */
and tds.FieldId != '5DD74568-4D4B-44C1-B513-0AF5F4CDA34F' /* __Created by */
and tds.FieldId != '001DD393-96C5-490B-924A-B0F25CD9EFD8' /* __Lock */
and tds.FieldId != '40E50ED9-BA07-4702-992E-A912738D32DC' /* Blob */
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment