SharePoint table PIVOT

1 - SharePoint table PIVOT.sql

with CommaSeparatedColNames as (
	select
		TABLE_NAME,
		DATA_TYPE,
		CHARACTER_MAXIMUM_LENGTH,
		NUMERIC_PRECISION,
		NUMERIC_SCALE,
		ListIdColName,
		ColNameList,
		TPrefixedColNameList,
		'(' + sub.ColNameList + ')' as ColNameListWithParentheses
	from (
		select distinct
			TABLE_NAME,
			DATA_TYPE,
			CHARACTER_MAXIMUM_LENGTH,
			NUMERIC_PRECISION,
			NUMERIC_SCALE,

			stuff(
				(
					select ', ' + COLUMN_NAME
					from INFORMATION_SCHEMA.COLUMNS x
					where x.TABLE_NAME = isc.TABLE_NAME
						and x.DATA_TYPE = isc.DATA_TYPE
						and (
							(x.CHARACTER_MAXIMUM_LENGTH is null and isc.CHARACTER_MAXIMUM_LENGTH is null)
							or (x.CHARACTER_MAXIMUM_LENGTH = isc.CHARACTER_MAXIMUM_LENGTH)
						)
						and (
							(x.NUMERIC_PRECISION is null and isc.NUMERIC_PRECISION is null)
							or (x.NUMERIC_PRECISION = isc.NUMERIC_PRECISION)
						)
						and (
							(x.NUMERIC_SCALE is null and isc.NUMERIC_SCALE is null)
							or (x.NUMERIC_SCALE = isc.NUMERIC_SCALE)
						)
						and (
							(x.DATETIME_PRECISION is null and isc.DATETIME_PRECISION is null)
							or (x.DATETIME_PRECISION = isc.DATETIME_PRECISION)
						)
					order by
						x.TABLE_NAME,
						x.COLUMN_NAME
					for xml path('')
				),
				1, 2, ''
			) as ColNameList,

			stuff(
				(
					select ', ' + 't.' + COLUMN_NAME
					from INFORMATION_SCHEMA.COLUMNS x
					where x.TABLE_NAME = isc.TABLE_NAME
						and x.DATA_TYPE = isc.DATA_TYPE
						and (
							(x.CHARACTER_MAXIMUM_LENGTH is null and isc.CHARACTER_MAXIMUM_LENGTH is null)
							or (x.CHARACTER_MAXIMUM_LENGTH = isc.CHARACTER_MAXIMUM_LENGTH)
						)
						and (
							(x.NUMERIC_PRECISION is null and isc.NUMERIC_PRECISION is null)
							or (x.NUMERIC_PRECISION = isc.NUMERIC_PRECISION)
						)
						and (
							(x.NUMERIC_SCALE is null and isc.NUMERIC_SCALE is null)
							or (x.NUMERIC_SCALE = isc.NUMERIC_SCALE)
						)
						and (
							(x.DATETIME_PRECISION is null and isc.DATETIME_PRECISION is null)
							or (x.DATETIME_PRECISION = isc.DATETIME_PRECISION)
						)
					order by
						x.TABLE_NAME,
						x.COLUMN_NAME
					for xml path('')
				),
				1, 2, ''
			) as TPrefixedColNameList,

			(
				select COLUMN_NAME
				from INFORMATION_SCHEMA.COLUMNS c
				where
					c.TABLE_NAME = isc.TABLE_NAME
					and c.COLUMN_NAME like '%ListId%'
			) as ListIdColName
		from INFORMATION_SCHEMA.COLUMNS isc
		where 
			TABLE_NAME in ('AllUserData', 'AllDocs', 'UserInfo', 'WebMembers')
			and	exists (
				select top 1 1
				from INFORMATION_SCHEMA.COLUMNS c
				where isc.TABLE_NAME = c.TABLE_NAME
					and c.COLUMN_NAME like '%ListId%'
			)
	)
	sub
)

--select * from CommaSeparatedColNames

select
'
select distinct up.ListName, up.ColName, up.DataType, cast(up.Val as nvarchar(max)) as Val
from (
	select distinct l.tp_Title as ListName, ''' + cscn.DATA_TYPE + ''' as DataType, ' + TPrefixedColNameList + '
	from ' + cscn.TABLE_NAME + ' t
	inner join AllLists l on t.' + cscn.ListIdColName + ' = l.tp_ID
) sub
unpivot (
	Val for ColName in ' + ColNameListWithParentheses + '
) up
-- order by up.ListName, up.ColName, Val
union
'
from CommaSeparatedColNames cscn
where TABLE_NAME in ('AllUserData', 'AllDocs', 'UserInfo', 'WebMembers')

2 - Results (generated queries with minor tweaks).sql

select distinct up.ListName, up.ColName, up.DataType, cast(up.Val as nvarchar(max)) as Val
from (
	select distinct l.tp_Title as ListName, 'bit' as DataType, t.Dirty, t.FFMConsistent, t.IsCurrentVersion, t.ListDataDirty, t.ThicketFlag, t.VersionCreatedSinceSTCheckout
	from AllDocs t
	inner join AllLists l on t.ListId = l.tp_ID
) sub
unpivot (
	Val for ColName in (Dirty, FFMConsistent, IsCurrentVersion, ListDataDirty, ThicketFlag, VersionCreatedSinceSTCheckout)
) up
-- order by up.ListName, up.ColName, Val
union


select distinct up.ListName, up.ColName, up.DataType, cast(up.Val as nvarchar(max)) as Val
from (
	select distinct l.tp_Title as ListName, 'datetime' as DataType, t.CheckoutDate, t.CheckoutExpires, t.MetaInfoTimeLastModified, t.NextToLastTimeModified, t.TimeCreated, t.TimeLastModified, t.TimeLastWritten
	from AllDocs t
	inner join AllLists l on t.ListId = l.tp_ID
) sub
unpivot (
	Val for ColName in (CheckoutDate, CheckoutExpires, MetaInfoTimeLastModified, NextToLastTimeModified, TimeCreated, TimeLastModified, TimeLastWritten)
) up
-- order by up.ListName, up.ColName, Val
union


select distinct up.ListName, up.ColName, up.DataType, cast(up.Val as nvarchar(max)) as Val
from (
	select distinct l.tp_Title as ListName, 'int' as DataType, t.AuditFlags, t.CharSet, t.CheckoutUserId, t.ContentVersion, t.DocFlags, t.DoclibRowId, t.DraftOwnerId, t.EffectiveVersion, t.ETagVersion, t.FileFormatMetaInfoSize, t.FolderChildCount, t.HasStream, t.InheritAuditFlags, t.InternalVersion, t.IsCheckoutToLocal, t.ItemChildCount, t.ListSchemaVersion, t.LTCheckoutUserId, t.MetaInfoSize, t.MetaInfoVersion, t.ParentVersion, t.Size, t.UIVersion, t.UnVersionedMetaInfoSize, t.UnVersionedMetaInfoVersion, t.VirusStatus, t.VirusVendorID
	from AllDocs t
	inner join AllLists l on t.ListId = l.tp_ID
) sub
unpivot (
	Val for ColName in (AuditFlags, CharSet, CheckoutUserId, ContentVersion, DocFlags, DoclibRowId, DraftOwnerId, EffectiveVersion, ETagVersion, FileFormatMetaInfoSize, FolderChildCount, HasStream, InheritAuditFlags, InternalVersion, IsCheckoutToLocal, ItemChildCount, ListSchemaVersion, LTCheckoutUserId, MetaInfoSize, MetaInfoVersion, ParentVersion, Size, UIVersion, UnVersionedMetaInfoSize, UnVersionedMetaInfoVersion, VirusStatus, VirusVendorID)
) up
-- order by up.ListName, up.ColName, Val
union


select distinct up.ListName, up.ColName, up.DataType, cast(up.Val as nvarchar(max)) as Val
from (
	select distinct l.tp_Title as ListName, 'nvarchar' as DataType, t.WelcomePageParameters
	from AllDocs t
	inner join AllLists l on t.ListId = l.tp_ID
) sub
unpivot (
	Val for ColName in (WelcomePageParameters)
) up
-- order by up.ListName, up.ColName, Val
union


select distinct up.ListName, up.ColName, up.DataType, cast(up.Val as nvarchar(max)) as Val
from (
	select distinct l.tp_Title as ListName, 'nvarchar' as DataType, t.ParentVersionString, t.UIVersionString
	from AllDocs t
	inner join AllLists l on t.ListId = l.tp_ID
) sub
unpivot (
	Val for ColName in (ParentVersionString, UIVersionString)
) up
-- order by up.ListName, up.ColName, Val
union


select distinct up.ListName, up.ColName, up.DataType, cast(up.Val as nvarchar(max)) as Val
from (
	select distinct l.tp_Title as ListName, 'nvarchar' as DataType, t.Extension, t.ExtensionForFile, t.LeafName, t.ParentLeafName
	from AllDocs t
	inner join AllLists l on t.ListId = l.tp_ID
) sub
unpivot (
	Val for ColName in (Extension, ExtensionForFile, LeafName, ParentLeafName)
) up
-- order by up.ListName, up.ColName, Val
union


select distinct up.ListName, up.ColName, up.DataType, cast(up.Val as nvarchar(max)) as Val
from (
	select distinct l.tp_Title as ListName, 'nvarchar' as DataType, t.ProgId, t.SetupPath, t.SetupPathUser, t.VirusInfo
	from AllDocs t
	inner join AllLists l on t.ListId = l.tp_ID
) sub
unpivot (
	Val for ColName in (ProgId, SetupPath, SetupPathUser, VirusInfo)
) up
-- order by up.ListName, up.ColName, Val
union


select distinct up.ListName, up.ColName, up.DataType, cast(up.Val as nvarchar(max)) as Val
from (
	select distinct l.tp_Title as ListName, 'nvarchar' as DataType, t.DirName
	from AllDocs t
	inner join AllLists l on t.ListId = l.tp_ID
) sub
unpivot (
	Val for ColName in (DirName)
) up
-- order by up.ListName, up.ColName, Val
union


select distinct up.ListName, up.ColName, up.DataType, cast(up.Val as nvarchar(max)) as Val
from (
	select distinct l.tp_Title as ListName, 'nvarchar' as DataType, t.WelcomePageUrl
	from AllDocs t
	inner join AllLists l on t.ListId = l.tp_ID
) sub
unpivot (
	Val for ColName in (WelcomePageUrl)
) up
-- order by up.ListName, up.ColName, Val
union


select distinct up.ListName, up.ColName, up.DataType, cast(up.Val as nvarchar(max)) as Val
from (
	select distinct l.tp_Title as ListName, 'nvarchar' as DataType, t.CheckinComment
	from AllDocs t
	inner join AllLists l on t.ListId = l.tp_ID
) sub
unpivot (
	Val for ColName in (CheckinComment)
) up
-- order by up.ListName, up.ColName, Val
union


select distinct up.ListName, up.ColName, up.DataType, cast(up.Val as nvarchar(max)) as Val
from (
	select distinct l.tp_Title as ListName, 'smallint' as DataType, t.CtoOffset
	from AllDocs t
	inner join AllLists l on t.ListId = l.tp_ID
) sub
unpivot (
	Val for ColName in (CtoOffset)
) up
-- order by up.ListName, up.ColName, Val
union


select distinct up.ListName, up.ColName, up.DataType, cast(up.Val as nvarchar(max)) as Val
from (
	select distinct l.tp_Title as ListName, 'tinyint' as DataType, t.BumpVersion, t.Level, t.SetupPathVersion, t.SortBehavior, t.Type
	from AllDocs t
	inner join AllLists l on t.ListId = l.tp_ID
) sub
unpivot (
	Val for ColName in (BumpVersion, Level, SetupPathVersion, SortBehavior, Type)
) up
-- order by up.ListName, up.ColName, Val
union


select distinct up.ListName, up.ColName, up.DataType, cast(up.Val as nvarchar(max)) as Val
from (
	select distinct l.tp_Title as ListName, 'uniqueidentifier' as DataType, t.CacheParseId, t.Id, t.ListId, t.ParentId, t.ScopeId, t.SiteId, t.TransformerId, t.WebId
	from AllDocs t
	inner join AllLists l on t.ListId = l.tp_ID
) sub
unpivot (
	Val for ColName in (CacheParseId, Id, ListId, ParentId, ScopeId, SiteId, TransformerId, WebId)
) up
-- order by up.ListName, up.ColName, Val
union


select distinct up.ListName, up.ColName, up.DataType, cast(up.Val as nvarchar(max)) as Val
from (
	select distinct l.tp_Title as ListName, 'varbinary' as DataType, t.BuildDependencySet, t.FileFormatMetaInfo, t.MetaInfo, t.UnVersionedMetaInfo
	from AllDocs t
	inner join AllLists l on t.ListId = l.tp_ID
) sub
unpivot (
	Val for ColName in (BuildDependencySet, FileFormatMetaInfo, MetaInfo, UnVersionedMetaInfo)
) up
-- order by up.ListName, up.ColName, Val
union


select distinct up.ListName, up.ColName, up.DataType, cast(up.Val as nvarchar(max)) as Val
from (
	select distinct l.tp_Title as ListName, 'varbinary' as DataType, t.ClientId, t.DeleteTransactionId
	from AllDocs t
	inner join AllLists l on t.ListId = l.tp_ID
) sub
unpivot (
	Val for ColName in (ClientId, DeleteTransactionId)
) up
-- order by up.ListName, up.ColName, Val
union


select distinct up.ListName, up.ColName, up.DataType, cast(up.Val as nvarchar(max)) as Val
from (
	select distinct l.tp_Title as ListName, 'bit' as DataType, t.bit1, t.bit10, t.bit11, t.bit12, t.bit13, t.bit14, t.bit15, t.bit16, t.bit2, t.bit3, t.bit4, t.bit5, t.bit6, t.bit7, t.bit8, t.bit9, t.tp_HasAttachment, t.tp_HasCopyDestinations, t.tp_IsCurrent, t.tp_IsCurrentVersion
	from AllUserData t
	inner join AllLists l on t.tp_ListId = l.tp_ID
) sub
unpivot (
	Val for ColName in (bit1, bit10, bit11, bit12, bit13, bit14, bit15, bit16, bit2, bit3, bit4, bit5, bit6, bit7, bit8, bit9, tp_HasAttachment, tp_HasCopyDestinations, tp_IsCurrent, tp_IsCurrentVersion)
) up
-- order by up.ListName, up.ColName, Val
union


select distinct up.ListName, up.ColName, up.DataType, cast(up.Val as nvarchar(max)) as Val
from (
	select distinct l.tp_Title as ListName, 'datetime' as DataType, t.datetime1, t.datetime2, t.datetime3, t.datetime4, t.datetime5, t.datetime6, t.datetime7, t.datetime8, t.tp_Created, t.tp_Modified
	from AllUserData t
	inner join AllLists l on t.tp_ListId = l.tp_ID
) sub
unpivot (
	Val for ColName in (datetime1, datetime2, datetime3, datetime4, datetime5, datetime6, datetime7, datetime8, tp_Created, tp_Modified)
) up
-- order by up.ListName, up.ColName, Val
union


select distinct up.ListName, up.ColName, up.DataType, cast(up.Val as nvarchar(max)) as Val
from (
	select distinct l.tp_Title as ListName, 'float' as DataType, t.float1, t.float10, t.float11, t.float12, t.float2, t.float3, t.float4, t.float5, t.float6, t.float7, t.float8, t.float9, t.tp_ItemOrder
	from AllUserData t
	inner join AllLists l on t.tp_ListId = l.tp_ID
) sub
unpivot (
	Val for ColName in (float1, float10, float11, float12, float2, float3, float4, float5, float6, float7, float8, float9, tp_ItemOrder)
) up
-- order by up.ListName, up.ColName, Val
union


select distinct up.ListName, up.ColName, up.DataType, cast(up.Val as nvarchar(max)) as Val
from (
	select distinct l.tp_Title as ListName, 'int' as DataType, t.int1, t.int10, t.int11, t.int12, t.int13, t.int14, t.int15, t.int16, t.int2, t.int3, t.int4, t.int5, t.int6, t.int7, t.int8, t.int9, t.tp_AuditFlags, t.tp_Author, t.tp_CalculatedVersion, t.tp_CheckoutUserId, t.tp_DraftOwnerId, t.tp_Editor, t.tp_ID, t.tp_InheritAuditFlags, t.tp_InstanceID, t.tp_ModerationStatus, t.tp_Size, t.tp_UIVersion, t.tp_Version, t.tp_WorkflowVersion
	from AllUserData t
	inner join AllLists l on t.tp_ListId = l.tp_ID
) sub
unpivot (
	Val for ColName in (int1, int10, int11, int12, int13, int14, int15, int16, int2, int3, int4, int5, int6, int7, int8, int9, tp_AuditFlags, tp_Author, tp_CalculatedVersion, tp_CheckoutUserId, tp_DraftOwnerId, tp_Editor, tp_ID, tp_InheritAuditFlags, tp_InstanceID, tp_ModerationStatus, tp_Size, tp_UIVersion, tp_Version, tp_WorkflowVersion)
) up
-- order by up.ListName, up.ColName, Val
union


select distinct up.ListName, up.ColName, up.DataType, cast(up.Val as nvarchar(max)) as Val
from (
	select distinct l.tp_Title as ListName, 'nvarchar' as DataType, t.ntext1, t.ntext10, t.ntext11, t.ntext12, t.ntext13, t.ntext14, t.ntext15, t.ntext16, t.ntext17, t.ntext18, t.ntext19, t.ntext2, t.ntext20, t.ntext21, t.ntext22, t.ntext23, t.ntext24, t.ntext25, t.ntext26, t.ntext27, t.ntext28, t.ntext29, t.ntext3, t.ntext30, t.ntext31, t.ntext32, t.ntext4, t.ntext5, t.ntext6, t.ntext7, t.ntext8, t.ntext9
	from AllUserData t
	inner join AllLists l on t.tp_ListId = l.tp_ID
) sub
unpivot (
	Val for ColName in (ntext1, ntext10, ntext11, ntext12, ntext13, ntext14, ntext15, ntext16, ntext17, ntext18, ntext19, ntext2, ntext20, ntext21, ntext22, ntext23, ntext24, ntext25, ntext26, ntext27, ntext28, ntext29, ntext3, ntext30, ntext31, ntext32, ntext4, ntext5, ntext6, ntext7, ntext8, ntext9)
) up
-- order by up.ListName, up.ColName, Val
union


select distinct up.ListName, up.ColName, up.DataType, cast(up.Val as nvarchar(max)) as Val
from (
	select distinct l.tp_Title as ListName, 'nvarchar' as DataType, t.tp_UIVersionString
	from AllUserData t
	inner join AllLists l on t.tp_ListId = l.tp_ID
) sub
unpivot (
	Val for ColName in (tp_UIVersionString)
) up
-- order by up.ListName, up.ColName, Val
union


select distinct up.ListName, up.ColName, up.DataType, cast(up.Val as nvarchar(max)) as Val
from (
	select distinct l.tp_Title as ListName, 'nvarchar' as DataType, t.nvarchar1, t.nvarchar10, t.nvarchar11, t.nvarchar12, t.nvarchar13, t.nvarchar14, t.nvarchar15, t.nvarchar16, t.nvarchar17, t.nvarchar18, t.nvarchar19, t.nvarchar2, t.nvarchar20, t.nvarchar21, t.nvarchar22, t.nvarchar23, t.nvarchar24, t.nvarchar25, t.nvarchar26, t.nvarchar27, t.nvarchar28, t.nvarchar29, t.nvarchar3, t.nvarchar30, t.nvarchar31, t.nvarchar32, t.nvarchar33, t.nvarchar34, t.nvarchar35, t.nvarchar36, t.nvarchar37, t.nvarchar38, t.nvarchar39, t.nvarchar4, t.nvarchar40, t.nvarchar41, t.nvarchar42, t.nvarchar43, t.nvarchar44, t.nvarchar45, t.nvarchar46, t.nvarchar47, t.nvarchar48, t.nvarchar49, t.nvarchar5, t.nvarchar50, t.nvarchar51, t.nvarchar52, t.nvarchar53, t.nvarchar54, t.nvarchar55, t.nvarchar56, t.nvarchar57, t.nvarchar58, t.nvarchar59, t.nvarchar6, t.nvarchar60, t.nvarchar61, t.nvarchar62, t.nvarchar63, t.nvarchar64, t.nvarchar7, t.nvarchar8, t.nvarchar9
	from AllUserData t
	inner join AllLists l on t.tp_ListId = l.tp_ID
) sub
unpivot (
	Val for ColName in (nvarchar1, nvarchar10, nvarchar11, nvarchar12, nvarchar13, nvarchar14, nvarchar15, nvarchar16, nvarchar17, nvarchar18, nvarchar19, nvarchar2, nvarchar20, nvarchar21, nvarchar22, nvarchar23, nvarchar24, nvarchar25, nvarchar26, nvarchar27, nvarchar28, nvarchar29, nvarchar3, nvarchar30, nvarchar31, nvarchar32, nvarchar33, nvarchar34, nvarchar35, nvarchar36, nvarchar37, nvarchar38, nvarchar39, nvarchar4, nvarchar40, nvarchar41, nvarchar42, nvarchar43, nvarchar44, nvarchar45, nvarchar46, nvarchar47, nvarchar48, nvarchar49, nvarchar5, nvarchar50, nvarchar51, nvarchar52, nvarchar53, nvarchar54, nvarchar55, nvarchar56, nvarchar57, nvarchar58, nvarchar59, nvarchar6, nvarchar60, nvarchar61, nvarchar62, nvarchar63, nvarchar64, nvarchar7, nvarchar8, nvarchar9)
) up
-- order by up.ListName, up.ColName, Val
union


select distinct up.ListName, up.ColName, up.DataType, cast(up.Val as nvarchar(max)) as Val
from (
	select distinct l.tp_Title as ListName, 'nvarchar' as DataType, t.tp_CopySource
	from AllUserData t
	inner join AllLists l on t.tp_ListId = l.tp_ID
) sub
unpivot (
	Val for ColName in (tp_CopySource)
) up
-- order by up.ListName, up.ColName, Val
union


select distinct up.ListName, up.ColName, up.DataType, cast(up.Val as nvarchar(max)) as Val
from (
	select distinct l.tp_Title as ListName, 'sql_variant' as DataType, t.sql_variant1, t.sql_variant2, t.sql_variant3, t.sql_variant4, t.sql_variant5, t.sql_variant6, t.sql_variant7, t.sql_variant8
	from AllUserData t
	inner join AllLists l on t.tp_ListId = l.tp_ID
) sub
unpivot (
	Val for ColName in (sql_variant1, sql_variant2, sql_variant3, sql_variant4, sql_variant5, sql_variant6, sql_variant7, sql_variant8)
) up
-- order by up.ListName, up.ColName, Val
union


select distinct up.ListName, up.ColName, up.DataType, cast(up.Val as nvarchar(max)) as Val
from (
	select distinct l.tp_Title as ListName, 'tinyint' as DataType, t.tp_Level, t.tp_RowOrdinal
	from AllUserData t
	inner join AllLists l on t.tp_ListId = l.tp_ID
) sub
unpivot (
	Val for ColName in (tp_Level, tp_RowOrdinal)
) up
-- order by up.ListName, up.ColName, Val
union


select distinct up.ListName, up.ColName, up.DataType, cast(up.Val as nvarchar(max)) as Val
from (
	select distinct l.tp_Title as ListName, 'uniqueidentifier' as DataType, t.tp_DocId, t.tp_GUID, t.tp_ListId, t.tp_ParentId, t.tp_SiteId, t.tp_WorkflowInstanceID, t.uniqueidentifier1
	from AllUserData t
	inner join AllLists l on t.tp_ListId = l.tp_ID
) sub
unpivot (
	Val for ColName in (tp_DocId, tp_GUID, tp_ListId, tp_ParentId, tp_SiteId, tp_WorkflowInstanceID, uniqueidentifier1)
) up
-- order by up.ListName, up.ColName, Val
union


select distinct up.ListName, up.ColName, up.DataType, cast(up.Val as nvarchar(max)) as Val
from (
	select distinct l.tp_Title as ListName, 'varbinary' as DataType, t.tp_DeleteTransactionId
	from AllUserData t
	inner join AllLists l on t.tp_ListId = l.tp_ID
) sub
unpivot (
	Val for ColName in (tp_DeleteTransactionId)
) up
-- order by up.ListName, up.ColName, Val
union


select distinct up.ListName, up.ColName, up.DataType, cast(up.Val as nvarchar(max)) as Val
from (
	select distinct l.tp_Title as ListName, 'varbinary' as DataType, t.tp_ContentTypeId, t.tp_ThreadIndex
	from AllUserData t
	inner join AllLists l on t.tp_ListId = l.tp_ID
) sub
unpivot (
	Val for ColName in (tp_ContentTypeId, tp_ThreadIndex)
) up
-- order by up.ListName, up.ColName, Val
union


select distinct up.ListName, up.ColName, up.DataType, cast(up.Val as nvarchar(max)) as Val
from (
	select distinct l.tp_Title as ListName, 'varchar' as DataType, t.tp_Ordering
	from AllUserData t
	inner join AllLists l on t.tp_ListId = l.tp_ID
) sub
unpivot (
	Val for ColName in (tp_Ordering)
) up
order by up.ListName, up.ColName, Val

For anyone reading this:

The purpose of this code-generation query is to facilitate examination of data for the purpose of identifying columns that potentially contain Personally identifiable information (PII).

SharePoint lists are complex, because tables of interest contain dozens of repeating sparsely populated columns (e.g.: datetime1, datetime2, datetime3, etc).

These code-generated queries allow the normalisation of all data types into a table with the following columns:
ListName, ColName, DataType, Val (where Val is a string).

From there, you can eyeball the data, make note of suspect values (including the table/column they're in), and develop a data scrambling script that runs a series of UPDATE queries on the PII columns you've found.

Good luck!