October 27, 2021 at 2:10 pm
I'm working on a script to generate DDL statements. I have a variable declared as a VARCHAR(MAX) which gets set using a combination of the STUFF function and XML. All of that works just fine. Knowing that SQL is going to limit that to 8,000 characters I have a series of substrings breaking that down into smaller segments and assigning multiple variables. The script will print out the values stored in those variables at the end which gives me the DDL needed. However, in cases where it is longer than 8,000 characters it does not provide a clean break and prevents from easily copying and pasting. Here is what I mean by that.
create table dbo.TestTable
(
[column1] int NOT NULL, [column2] varchar(10), ...<characters 47-7996>... , [col
umn450] decimal(8,2)
)
You can see it breaks up column450 into two separate lines which causes error when running as is. Is there a way to have SQL continue to print a different variable on the same line as another exceeding a total of 8,000?
October 27, 2021 at 2:23 pm
Are you referring to the output generated by the SQL PRINT statement?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 27, 2021 at 2:26 pm
Phil - Yes, the SQL PRINT output. In particular printing out strings, or concatenating, strings longer than 8,000 characters.
October 27, 2021 at 2:29 pm
My first suggestion would be to use SELECT rather than PRINT, since SELECT goes up to 65535 chars (I believe, if you have the options set that way in SSMS).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 27, 2021 at 2:35 pm
Here is a chunk of code I've used in the past that may help you, where @SQL1 was previously declared as NVARCHAR(MAX).
DECLARE @CurrentEnd BIGINT; /* track the length of the next substring */
DECLARE @offset TINYINT; /*tracks the amount of offset needed */
SET @SQL1 = REPLACE(REPLACE(@SQL1, CHAR(13) + CHAR(10), CHAR(10)), CHAR(13), CHAR(10));
WHILE LEN(@SQL1) > 1
BEGIN
IF CHARINDEX(CHAR(10), @SQL1)
BETWEEN 1 AND 4000
BEGIN
SET @CurrentEnd = CHARINDEX(CHAR(10), @SQL1) - 1;
SET @offset = 2;
END;
ELSE
BEGIN
SET @CurrentEnd = 4000;
SET @offset = 1;
END;
PRINT SUBSTRING(@SQL1, 1, @CurrentEnd);
SET @SQL1 = SUBSTRING(@SQL1, @CurrentEnd + @offset, LEN(@SQL1));
END;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 27, 2021 at 3:39 pm
Phil - that helps limit each printout to 4,000 characters but in my case the string is 11,698 so it breaks it into three separate lines. The script I'm creating loops through each object in a database so the goal is to be able to copy and paste without having to bring those multiple lines into the same line.
October 27, 2021 at 3:50 pm
Phil - that helps limit each printout to 4,000 characters but in my case the string is 11,698 so it breaks it into three separate lines. The script I'm creating loops through each object in a database so the goal is to be able to copy and paste without having to bring those multiple lines into the same line.
Then PRINT is not going to work for you. As Scott suggested, use SELECT instead.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 27, 2021 at 3:59 pm
May I ask why you are doing this? There are other tried & tested ways of generating DDL.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 27, 2021 at 5:41 pm
When you say use select instead of print are you saying to toggle results to text and then use select @SQL1 which in my case has a string of 11,698 characters? If so, that only shows the first 256 characters.
I'm using this as part of a custom script to loop through all tables in a database and exclude some fields, change some data types, etc based on a set of criteria.
October 27, 2021 at 6:04 pm
You need to change the Options..., Query Results, SQL Server in your SSMS to display more chars.
You don't need (nor likely want) to toggle to text. A grid display should work, at least as long as you don't use -- for inline comments.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 27, 2021 at 6:39 pm
Changing it to select provided all of the characters. Now I need to figure out an easy way to copy those results since it loops over hundreds of tables all in their own grid.
October 27, 2021 at 6:54 pm
You can write them to a table -- add an identity for ordering -- and then SELECT from that table, for example:
SELECT sql_text AS [--sql_to_run]
FROM #some_table_name
ORDER BY $IDENTITY
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 27, 2021 at 10:58 pm
I'm with Phil. Why are you doing this?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
October 28, 2021 at 1:59 am
I'm with Phil. Why are you doing this?
I can't speak for Ron but sometimes it's necessary to see, for example, the full monty for dynamic SQL.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 28, 2021 at 2:03 am
Ron,
The following does the trick for me when I need to do such a thing. Details are in the flower box.
Create FUNCTION [dbo].[ShowLongString]
/**********************************************************************************************************************
Purpose:
Display a string of more than 8000 characters. The string can be Dynamic SQL, XML, or just about anything else.
Usage:
--===== Example with Dynamic SQL
DECLARE @SQL VARCHAR(MAX);
SELECT @SQL = '
SELECT somecolumnlist
FROM some table with joins
;'
;
SELECT LongString
FROM dbo.ShowLongString(@SQL)
;
--===== Example with a call to a table or view
SELECT sm.Object_ID, Definition = ls.LongString
FROM sys.SQL_Modules sm
CROSS APPLY dbo.ShowLongString(sm.Definition) ls
;
Revision History:
Rev 00 - 20 Sep 2013 - Jeff Moden - Initial creation and test.
**********************************************************************************************************************/
--===== Declare the I/O for this function
(@pLongString VARCHAR(MAX))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
SELECT LongString =
(
SELECT REPLACE(
CAST(
'--' + CHAR(10) + @pLongString + CHAR(10)
AS VARCHAR(MAX))
,CHAR(0),'') --CHAR(0) (Null) cannot be converted to XML.
AS [processing-instruction(LongString)]
FOR XML PATH(''), TYPE
)
;
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply