Restrictions and extensions for print, sp_helptext in SQL _mssql

In SQL, it is common to use dynamic SQL. There are some complex calculations, or stored procedures, where the code is very long and there may be multiple execution of SQL statements in the middle. Debugging a string of SQL statements is painful, and it's hard to see what statements to run. So I will often use print this command to print the statement before the run to the screen, and then copied out to another window for debugging, modify, so it is more convenient. However, this print command has some limitations, with a single byte set, the maximum print length of 8,000 characters, while the Double-byte character set is 4,000.

The following stored procedures can print complete information, not limited by length. The code is as follows:

IF object_id (N ' Sp_print_all ') is isn't NULL
BEGIN
DROP PROCEDURE sp_print_all end go
CREATE PROCEDURE Sp_print_all (@dynamic_sql NVARCHAR (MAX))
as
DECLARE @start int
DECLARE @len int
SET @start = 1
SET @len = 4000
while (@start < Len (@dynamic_sql))
BEGIN
PRINT SUBSTRING (@dynamic_sql, @start , @len)
SET @start = @start + @len
end
PRINT SUBSTRING (@dynamic_sql, @start, @len)

Code-1

There is also a storage sp_helptext, you can query stored procedures, functions, and other code, it is also more convenient to use, but also has the length of the restrictions, and the format of the printed source code and some of the format does not correspond. A custom stored procedure was written to replace the following code:

IF object_id (N ' Sp_helptext_user ') is isn't NULL
BEGIN
DROP PROCEDURE sp_helptext_user
End Go CREATE PROCEDURE sp_helptext_user (@obj_name NVARCHAR = ') as
SET NOCOUNT on;
DECLARE @text NVARCHAR (max),
@i INT
, @text2 NVARCHAR (max)
, @db_name SYSNAME
, @obj_id BIGINT
SET @db_name = ParseName (@obj_name, 3)
if @db_name is NULL
SET @db_name = db_name ()
ELSE 
if @db_name & Lt;> db_name ()
BEGIN
RAISERROR (15250, -1, -1) return
(1)
end
SET @obj_id = object_id (@obj _name)
IF @obj_id is NULL
to BEGIN RAISERROR (15009, -1, -1, @obj_name, @db_name) return
(1)
end< C28/>select @text = [definition] from
sys.all_sql_modules
WHERE [object_id] = @obj_id while
LEN (@text) & Gt
BEGIN
Set @i = CHARINDEX (CHAR, @text,) 
Set @text2 = Left (@text, @i)
Set @text = Substrin G (@text, @i + 2, LEN (@text)) 
print @text2
end
print @text
SET NOCOUNT off;

Code-2

Of course, there are many ways to view the source code, you can operate on the SSMs, see personal habits or convenient operation.

Detailed sp_helptext

Displays the text of a rule, default value, unencrypted stored procedure, user-defined function, trigger, or view.

Grammar

sp_helptext [@objname =] ' name '

Parameters

[@objname =] ' Name '

The name of the object that will display the definition information for the object. The object must be in the current database. The data type of name is nvarchar (776) and there is no default value.

Return code value

0 (Success) or 1 (failed)

Comments

Sp_helptext Displays the text used to create objects in more than one row, each of which has 255 characters of Transact-SQL definitions. These definitions reside only in the text of the syscomments table in the current database.

Permissions

Execute permissions are granted to the public role by default.

Example

The following example displays the text of the EMPLOYEE_INSUPD trigger, which is in the database pubs.

Use pubs
EXEC sp_helptext ' employee_insupd '