April 8, 2016 at 3:44 am
Hi.
The following procedure is used to generate update stored procedures for a database that has in excess of 500 tables. For 95% of tables this works fine. The other 5% of tables contain hundreds of fields and the result in the second field in the grid gets truncated, even though I use convert(varchar(max)) and varchar(max) in the for xml line. It truncates at 43679 characters.
I have tried altering the query options and hitting Google for a solution but have come up blank.
Anyone got any ideas how I can get around this.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[p_Admin_CreateDynamicUpdateProcedures_FieldCompare]
@Type varchar(6)
,@SchemaName varchar(30)
,@TableName varchar(100) = null
-- exec p_Admin_CreateDynamicUpdateProcedures_FieldCompare 'alter', 'dbo'
as
if lower(@Type) != 'alter' and lower(@Type) != 'create'
begin
select '@Type must be either create or alter, cannot generate procedures.'
return 1
end
if (select count(*) from sys.schemas as sch where sch.name = @SchemaName) = 0
begin
select '@SchemaName does not exist, cannot generate ' + lower(@Type) + ' procedures.'
return 2
end
if @TableName is not null and (select count(*) from sys.tables as tbl where tbl.name = @TableName) = 0
begin
select '@TableName does not exist, cannot generate ' + lower(@Type) + ' procedures.'
return 3
end
select tbl.name
,convert(varchar(max),lower(@Type))
+' procedure '
+sch.name -- use same schema as table
+'.p_Update_'
+tbl.name
+' with recompile'
+replicate(char(13) + char(10), 2) -- Carriage Return + Line Feed
+'as'
+replicate(char(13) + char(10), 2) -- Carriage Return + Line Feed
+char(9) -- Tab
+'update [dst] '
+char(13) + char(10) -- Carriage Return + Line Feed
+char(9) -- Tab
+'set'
+char(13) + char(10) -- Carriage Return + Line Feed
+replicate(char(9), 4) -- Tab
+replicate(char(32), 2) -- Space
+replace([Fields], ',', char(13) + char(10) + replicate(char(9), 4)+',')
+char(13) + char(10) -- Carriage Return + Line Feed
+char(9) -- Tab
+'from'
+replicate(char(9), 2) -- Tab
+replicate(char(32), 2) -- Space
+'[ODS_Kestrel].['
+sch.name
+'].['
+tbl.name
+'] as [dst]'
+char(13) + char(10) -- Carriage Return + Line Feed
+char(9) -- Tab
+'inner join'
+char(9) -- Tab
+replicate(char(32), 2) -- Space
+'['
+sch.name
+'].['
+tbl.name
+'] as [src]'
+char(13) + char(10) -- Carriage Return + Line Feed
+replicate(char(9), 2) -- tab
+'on'
+replicate(char(9), 2)
+replicate(char(32), 2) -- space
+replace(PK_Fields, ' and', char(13) + char(10) + replicate(char(9), 2) + 'and' + replicate(char(9),2) + char(32))
+char(13) + char(10) -- Carriage Return + Line Feed
+char(9) -- Tab
+convert(varchar(max),'where ')
+replicate(char(9), 2) -- tab
+replicate(char(32), 2) -- space
+replace([Test], '^', char(13) + char(10) + replicate(char(9), 2) + 'or' + replicate(char(9), 2) + char(32))
+char(13) + char(10) -- Carriage Return + Line Feed
+'GO'
from sys.tables as tbl
inner join sys.schemas as sch
on sch.schema_id = tbl.schema_id
cross apply
(
select stuff(
(
select ', [dst].['
+col.name
+'] = [src].['
+col.name
+']'
from sys.columns as col
inner join sys.tables as ctb
on ctb.object_id = col.object_id
left join dbo.PrimaryKeyFields as pkf
on pkf.table_name = ctb.name
and pkf.column_name = col.name
where col.object_id = tbl.object_id
and pkf.column_name is null
and col.name not in ('CheckSum', 'SCDStartDate','SCDEndDate')
and col.column_id not in
(
selectidc.column_id
fromsys.index_columns as idc
inner joinsys.indexes as idx
onidx.index_id = idc.index_id
whereidc.object_id = tbl.object_id
andidx.is_primary_key = 1
)
order by col.column_id
for xml path(''),type
).value('text()[1]','varchar(max)'),1,2,'')
) o (Fields)
cross apply
(
select stuff(
(
select ' and [src].['
+col.name
+'] = [dst].['
+col.name
+']'
fromsys.columns as col
inner joinsys.index_columns as idc
onidc.column_id = col.column_id
inner joinsys.indexes as idx
onidx.index_id = idc.index_id
whereidc.object_id = tbl.object_id
andcol.object_id = tbl.object_id
andidx.object_id = tbl.object_id
andidx.is_primary_key = 1 -- Denotes Primary Key Index
order byidc.key_ordinal
for xml path(''),type
).value('text()[1]','varchar(max)'),1,5,'')
) p (PK_Fields)
cross apply
(
select stuff(
(
select '^ [dst].['
+col.name
+'] != [src].['
+col.name
+']'
from sys.columns as col
inner join sys.tables as ctb
on ctb.object_id = col.object_id
left join dbo.PrimaryKeyFields as pkf
on pkf.table_name = ctb.name
and pkf.column_name = col.name
left join dbo.ClauseFieldExclusions as exl
on exl.table_name = ctb.name
and exl.column_name = col.name
where col.object_id = tbl.object_id
and pkf.column_name is null
and exl.column_name is null
and col.name not in ('CheckSum', 'SCDStartDate','SCDEndDate')
and col.column_id not in
(
selectidc.column_id
fromsys.index_columns as idc
inner joinsys.indexes as idx
onidx.index_id = idc.index_id
whereidc.object_id = tbl.object_id
andidx.is_primary_key = 1
)
order by col.column_id
for xml path(''),type
).value('text()[1]','varchar(max)'),1,2,'')
) t (Test)
where sch.name = @SchemaName
and (tbl.name = @TableName or @TableName is null)
order by tbl.name
return 0
April 8, 2016 at 3:50 am
Where are you seeing the truncated results? Is it in SSMS's grid? If so, go to Options -> Query Results -> SQL Server -> Results to Grid and check the configuration there.
If it's in SSMS's text output, then check the configuration at Options -> Query Results -> SQL Server -> Results to Text
Or are your results being used by something else and that's what's reporting the problem?
Thomas Rushton
blog: https://thelonedba.wordpress.com
April 8, 2016 at 4:25 am
I am executing it directly in SSMS.
I had already tried altering the query results to grid settings. You can't increase the value of Maximum characters retrieved for non XML data above 65535. I increased the XML data attribute to 5MB, but it made no difference.
The truncation is in the second field returned which contains the SQL script. The first field just returns the table name. I can run this for all 540 tables at the same time and copy the contents of the second column into a new query window. If it weren't for the truncation it would work for all tables. I have to find the affected results and add the missing SQL manually.
April 8, 2016 at 6:31 am
Thank you Phil. In all the years I have used SQL Server I have never used that option, worked a treat.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply