Hi all,
this sql statement work great on fresh-installed test server
DECLARE @strQry1 varchar(max)
SET @strQry1 =(select STUFF((SELECT TOP 100 PERCENT ' Union All ' + 'Select ''' + s.name + ''' as [schema_name], '''+ o.name+' '' as table_name, ''' +c.name +''' as column_name, Convert(varchar,' +c.name +') as column_value
from '+s.name +'.'+ o.name as qry
FROM
sys.all_columns c
LEFT JOIN sys.all_objects o on c.object_id = o.object_id
left join sys.schemas s on s.schema_id = o.schema_id
where o.type='u' and s.name = 'dbo' --scheme name
FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,11,'') strQry)
PRINT (@strQry1)
EXEC (@strQry1)
But it returns error on production server:
Select 'dbo' as [schema_name], 'spt_fallback_db ' as table_name, 'xserver_name' as column_name, Convert(varchar,xserver_name) as column_value from dbo.spt_fallback_db Union All Select 'dbo' as [schema_name], 'spt_fallback_db ' as table_name, 'xdttm_ins' as column_name, Convert(varchar,xdttm_ins) as column_value from dbo.spt_fallback_db Union All Select 'dbo' as [schema_name], 'spt_fallback_db ' as table_name, 'xdttm_last_ins_upd' as column_name, Convert(varchar,xdttm_last_ins_upd) as column_value from dbo.spt_fallback_db Union All Select 'dbo' as [schema_name], 'spt_fallback_db ' as table_name, 'xfallback_dbid' as column_name, Convert(varchar,xfallback_dbid) as column_value from dbo.spt_fallback_db Union All Select 'dbo' as [schema_name], 'spt_fallback_db ' as table_name, 'name' as column_name, Convert(varchar,name) as column_value from dbo.spt_fallback_db Union All Select 'dbo' as [schema_name], 'spt_fallback_db ' as table_name, 'dbid' as column_name, Convert(varchar,dbid) as column_value from dbo.spt_fallback_db Union All Select 'dbo' as [schema_name], 'spt_fallback_db ' as table_name, 'status' as column_name, Convert(varchar,status) as column_value from dbo.spt_fallback_db Union All Select 'dbo' as [schema_name], 'spt_fallback_db ' as table_name, 'version' as column_name, Convert(varchar,version) as column_value from dbo.spt_fallback_db Union All Select 'dbo' as [schema_name], 'spt_fallback_dev ' as table_name, 'xserver_name' as column_name, Convert(varchar,xserver_name) as column_value from dbo.spt_fallback_dev Union All Select 'dbo' as [schema_name], 'spt_fallback_dev ' as table_name, 'xdttm_ins' as column_name, Convert(varchar,xdttm_ins) as column_value from dbo.spt_fallback_dev Union All Select 'dbo' as [schema_name], 'spt_fallback_dev ' as table_name, 'xdttm_last_ins_upd' as column_name, Convert(varchar,xdttm_last_ins_upd) as column_value from dbo.spt_fallback_dev Union All Select 'dbo' as [schema_name], 'spt_fallback_dev ' as table_name, 'xfallback_low' as column_name, Convert(varchar,xfallback_low) as column_value from dbo.spt_fallback_dev Union All Select 'dbo' as [schema_name], 'spt_fallback_dev ' as table_name, 'xfallback_drive' as column_name, Convert(varchar,xfallback_drive) as column_value from dbo.spt_fallback_dev Union All Select 'dbo' as [schema_name], 'spt_fallback_dev ' as table_name, 'low' as column_name, Convert(varchar,low) as column_value from dbo.spt_fallback_dev Union All Select 'dbo' as [schema_name], 'spt_fallback_dev ' as table_name, 'high' as column_name, Convert(varchar,high) as column_value from dbo.spt_fallback_dev Union All Select 'dbo' as [schema_name], 'spt_fallback_dev ' as table_name, 'status' as column_name, Convert(varchar,status) as column_value from dbo.spt_fallback_dev Union All Select 'dbo' as [schema_name], 'spt_fallback_dev ' as table_name, 'name' as column_name, Convert(varchar,name) as column_value from dbo.spt_fallback_dev Union All Select 'dbo' as [schema_name], 'spt_fallback_dev ' as table_name, 'phyname' as column_name, Convert(varchar,phyname) as column_value from dbo.spt_fallback_dev Union All Select 'dbo' as [schema_name], 'spt_fallback_usg ' as table_name, 'xserver_name' as column_name, Convert(varchar,xserver_name) as column_value from dbo.spt_fallback_usg Union All Select 'dbo' as [schema_name], 'spt_fallback_usg ' as table_name, 'xdttm_ins' as column_name, Convert(varchar,xdttm_ins) as column_value from dbo.spt_fallback_usg Union All Select 'dbo' as [schema_name], 'spt_fallback_usg ' as table_name, 'xdttm_last_ins_upd' as column_name, Convert(varchar,xdttm_last_ins_upd) as column_value from dbo.spt_fallback_usg Union All Select 'dbo' as [schema_name], 'spt_fallback_usg ' as table_name, 'xfallback_vstart' as column_name, Convert(varchar,xfallback_vstart) as column_value from dbo.spt_fallback_usg Union All Select 'dbo' as [schema_name], 'spt_fallback_usg ' as table_name, 'dbid' as column_name, Convert(varchar,dbid) as column_value from dbo.spt_fallback_usg Union All Select 'dbo' as [schema_name], 'spt_fallback_usg ' as table_name, 'segmap' as column_name, Convert(varchar,segmap) as column_value from dbo.spt_fallback_usg Union All Select 'dbo' as [schema_name], 'spt_fallback_usg ' as table_name, 'lstart' as column_name, Convert(varchar,lstart) as column_value from dbo.spt_fallback_usg Union All Select 'dbo' as [schema_name], 'spt_fallback_usg ' as table_name, 'sizepg' as column_name, Convert(varchar,sizepg) as column_value from dbo.spt_fallback_usg Union All Select 'dbo' as [schema_name], 'spt_fallback_usg ' as table_name, 'vstart' as column_name, Convert(varchar,vstart) as column_value from dbo.spt_fallback_usg Union All Select 'dbo' as [schema_name], 'CL_IdentificationType ' as table_name, 'Id' as column_name, Convert(varchar,Id) as column_value from dbo.CL_IdentificationType Union All Select 'dbo' as [schema_name], 'CL_IdentificationType ' as table_name, 'Name' as column_name, Convert(varchar,Name) as column_value from dbo.CL_IdentificationType Union All Select 'dbo' as [schema_name], 'CL_IdentificationType ' as table_name, 'Status' as column_name, Convert(varchar,Status) as column_value from dbo.CL_IdentificationType Union All Select 'dbo' as [schema_name], 'CL_IdentificationType ' as table_name, 'GroupId' as column_name, Convert(varchar,GroupId) as column_value from dbo.CL_IdentificationType Union All Select 'dbo' as [schema_name], 'CL_IdentificationType ' as table_name, 'OrderId' as column_name, Convert(varchar,OrderId) as column_value from dbo.CL_IdentificationType Union All Select 'dbo' as [schema_name], 'CL_IdentificationType ' as table_name, 'CreatorId' as column_name, Convert(varchar,CreatorId) as column_value from dbo.CL_IdentificationType Union All Select 'dbo' as [schema_name], 'CL_IdentificationType ' as table_name, 'Created' as column_name, Convert(varchar,Created) as column_value from dbo.CL_IdentificationType Union All Select 'dbo' as [schema_name], 'CL_IdentificationType ' as table_name, 'ChangerId' as column_name, Convert(varchar,ChangerId) as column_value from dbo.CL_IdentificationType Union All Select 'dbo' as [schema_name], 'CL_IdentificationType ' as table_name, 'Changed' as column_name, Convert(varchar,Changed) as column_value from dbo.CL_IdentificationType Union All Select 'dbo' as [schema_name], 'CL_IdentificationType ' as table_name, 'b2_passporttypeid' as column_name, Convert(varchar,b2_passporttypeid) as column_value from dbo.CL_IdentificationType Union All Select 'dbo' as [schema_name], 'GPB_RULE_INFORMATION ' as table_name, 'EVENT_ID' as column_name, Convert(varchar,EVENT_ID) as column_value from dbo.GPB_RULE_INFORMATION Union All Select 'dbo' as [schema_name], 'GPB_RULE_INFORMATION ' as table_name, 'RULE_NAME' as column_name, Convert(varchar,RULE_NAME) as column_value from dbo.GPB_RULE_INFORMATION Union All Select 'dbo' as [schema_name], 'GPB_RULE_INFORMATION ' as table_name, 'RULE_INFORMATION' as column_name, Convert(varchar,RULE_INFORMATION) as column_value from dbo.GPB_RULE_INFORMATION Union All Select 'dbo' as [schema_name], 'spt_monitor ' as table_name, 'lastrun' as column_name, Convert(varchar,lastrun) as column_value from dbo.spt_monitor Union All Select 'dbo' as [schema_name], 'spt_monitor ' as table_name, 'cpu_busy' as column_name, Convert(varchar,cpu_busy) as column_value from dbo.spt_monitor Union All Select 'dbo' as [schema_name], 'spt_monitor ' as table_name, 'io_busy' as column_name, Convert(varchar,io_busy) as column_value from dbo.spt_monitor Union All Select 'dbo' as [schema_name], 'spt_monitor ' as table_name, 'idle' as column_name, Convert(varchar,idle) as column_value from dbo.spt_monitor Union All Select 'dbo' as [schema_name], 'spt_monitor ' as table_name, 'pack_received' as column_name, Convert(varchar,pack_received) as column_value from dbo.spt_monitor Union All Select 'dbo' as [schema_name], 'spt_monitor ' as table_name, 'pack_sent' as column_name, Convert(varchar,pack_sent) as column_value from dbo.spt_monitor Union All Select 'dbo' aMsg 457, Level 16, State 1, Line 1
Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.
Honestly, I googled it, but couldn't found advises for my case.
How do I resolve it?