June 28, 2023 at 5:04 pm
I wrote a process to store off all sql objects daily. There is one that stores off all stored procedures contents daily. The text column in the dbo.syscomments table is being stored locally in another table whose column is a varchar(max). It stores each proc in 4000 char chunks. So there will be one record per 4000 characters in the proc each having it's own colid which is incremented each time. I've attached the code(tab one) and Example output(tab two)
My issue is when i select the text columns data from the table i stored it in it comes out as one long string and is no longer formatted. I worked w someone in the past who wrote an xml sql query that not only concatenated the different 4000 char strings together but it also formatted the sql so it didn't come out as one long string. Can't seem to find this on google. Can someone point me in the write direction?
June 29, 2023 at 5:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
July 20, 2023 at 6:51 am
This was removed by the editor as SPAM
July 20, 2023 at 6:54 am
You can build a code similar to what I added:
you have a table named StoredProcedure1 with columns Name, Id, and ChData where ChunkData stores the 4000-character chunks of the stored procedure
-- Concatenate the chunks using XML PATH
DECLARE @ConcatenatedText XML;
SELECT @ConcatenatedText = (
SELECT ChData AS [text()]
FROM StoredProcedure1
WHERE Name = 'Your_Procedure_Name'
ORDER BY Id
FOR XML PATH('')
);
-- Format the SQL code
DECLARE @FormattedSQL NVARCHAR(MAX);
SET @FormattedSQL = CONVERT(NVARCHAR(MAX), @ConcatenatedText);
-- Print or do whatever you want with the formatted SQL
PRINT @FormattedSQL;
I hope this will help!!
July 27, 2023 at 2:22 pm
This was removed by the editor as SPAM
August 11, 2023 at 1:08 pm
This was removed by the editor as SPAM
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply