October 28, 2021 at 8:35 am
Michael L John wrote: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.
Presumably you have your DBs in source control, Jeff? If so, you always have whatever version of the code you are interested in at your fingertips.
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 28, 2021 at 3:45 pm
That's true, Phil... but I don't keep the results of Dynamic SQL in source control. I don't keep the code that gets stored in cache that I interrogate with my sp_WhatsRunning or sp_ShowWorst code. There are a thousand reasons why someone might want or need to display way more than 8000 characters. It's even good for looking at LOBs that you want to parse or whatever that are unfortunately stored in a table.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 28, 2021 at 4:35 pm
That's true, Phil... but I don't keep the results of Dynamic SQL in source control. I don't keep the code that gets stored in cache that I interrogate with my sp_WhatsRunning or sp_ShowWorst code. There are a thousand reasons why someone might want or need to display way more than 8000 characters. It's even good for looking at LOBs that you want to parse or whatever that are unfortunately stored in a table.
There has been a miscommunication, I think.
I was not referring to the 8,000 character limit. I was questioning the generation of DDL for the objects in a database.
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 28, 2021 at 10:40 pm
Agreed. I was only reading what you quoted and that was my comment about Dynamic SQL. I totally agree DDL should be in source control. Sometimes, though, source control and what's actually in a prod box are different. It, of course, should never get that on the trunk until a branch is committed but I've seen people screw things up pretty badly on source control.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 29, 2021 at 2:42 am
This is a stored procedure that I created a to print strings that exceed the 8K limit of the PRINT command.
USE master;
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE OR ALTER PROCEDURE dbo.sp_LongPrint
/* ===================================================================================================
2017-01-05 Jason Long, Allows the printing of strings that are greater that the 4K limit of the PRINT commant.
=================================================================================================== *//*
DECLARE @x NVARCHAR(MAX) = (SELECT REPLICATE(CONVERT(NVARCHAR(MAX), 'abcdefghijklmnop' + char(13) + char(10)), 10000) );
EXEC dbo.sp_LongPrint @x;
*/@_string NVARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;
IF LEN(@_string) <= 4000
BEGIN
PRINT(@_string);
END;
ELSE
BEGIN
DECLARE @string_part NVARCHAR(4000);
WHILE 1 = 1
BEGIN
SELECT
@string_part = SUBSTRING(@_string, 1, ISNULL(NULLIF(se.string_end, 4000) - 1, 4000)),
@_string = STUFF(@_string, 1, ISNULL(NULLIF(se.string_end, 4000) + 1, 4000), N'')
FROM
( VALUES ( 4000 - CHARINDEX(CHAR(10) + CHAR(13), REVERSE(SUBSTRING(@_string, 1, 4000)))) ) se (string_end)
IF @string_part = N''
BEGIN
BREAK;
END;
ELSE
BEGIN
PRINT(@string_part);
END;
END;
END;
END;
GO
EXEC sys.sp_MS_marksystemobject N'dbo.sp_LongPrint';
GO
October 29, 2021 at 1:27 pm
To expand on the reason I'm not using other DDL scripting methods is because I'm looping through hundreds of tables and running tests on converting data types and changing data in some columns. So one time through I might change all decimal(10,4) fields to decimal(10,2) and next time through to an int. There will also be functions manipulating data in other fields so scripting and doing a find and replace would be more work in my opinion.
I decided to select the strings into a table because this gave me the best solution to copying and pasting the DDLs I need.
Jeff - your function is very handy and I'll have to keep that in my back pocket
Jason - that does print out more than 8,000 characters but it breaks it up into different lines which was my initial problem
October 29, 2021 at 1:44 pm
To expand on the reason I'm not using other DDL scripting methods is because I'm looping through hundreds of tables and running tests on converting data types and changing data in some columns. So one time through I might change all decimal(10,4) fields to decimal(10,2) and next time through to an int. There will also be functions manipulating data in other fields so scripting and doing a find and replace would be more work in my opinion.
I decided to select the strings into a table because this gave me the best solution to copying and pasting the DDLs I need.
Jeff - your function is very handy and I'll have to keep that in my back pocket
Jason - that does print out more than 8,000 characters but it breaks it up into different lines which was my initial problem
Ahhhhhh. Fun stuff.
As long as you are going to use the "Print" statement, it's never going to give you more than 8096 characters. That's the limit for SSMS in an output window.
Have you tried sending the results to a file??? That worked for me when I was faced with a similar situation
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 29, 2021 at 2:32 pm
No luck with the results to file. Thanks for the help though
October 29, 2021 at 10:30 pm
No luck with the results to file. Thanks for the help though
Perhaps BCP from a LOB column to a file?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply