Issue with Database Objects Defination

Transact-SQL
1

Hi,

  1. We are using sql server 2012, when we use sp_helptext to see stored procedure definition then it will create blank space in stored procedure every time.

  2. We are try use object_definition, this is also create a issue it does not return complete definition , its actually truncate stored procedure definition.

Please look into it and let me know if we have any alternate solution or any setting to resolved this issue.

Regards

2

Look at this

USE yourdatabase;
GO
SELECT 
    DB_NAME() AS DatabaseName, 
    o.name AS ModuleName, 
    CASE O.Type 
        WHEN 'P' THEN 'USP' 
        WHEN 'FN' THEN 'UDF' 
        WHEN 'IF' THEN 'UDF' 
        WHEN 'TR' THEN 'TRIG' 
    END AS ModuleType, 
    (SELECT [definition] AS [text()] 
        FROM sys.sql_modules sm1 
        WHERE O.object_id = SM1.object_id 
        FOR XML PATH(''), TYPE).value('.','VARCHAR(MAX)') AS ModuleText, 
    O.create_date, 
    O.modify_date 
FROM sys.objects           O 
INNER JOIN sys.sql_modules M ON O.object_id = M.object_id 
WHERE O.is_ms_shipped = 0 
    AND O.[type] in ('P','FN','IF','TR') 
    AND o.name NOT LIKE 'dt%' AND o.name NOT LIKE 'fn%' AND o.name NOT LIKE 'SP%'
ORDER BY o.modify_date desc;
GO
3

Thanks but it also truncating the module text / stored procedure text,

4

That's most likely just a limitation of the tool you are using to view the results.

SSMS defaults to 255 characters, or somesuch, and the limit may well only be 8,000 (maybe its more, I've forgotten), but if you are using SSMS you could, as a first step, increase it to the limit.

Or use a tool that doesn't truncate the result.

Or store your SProcs etc. in individual files, instead of relying on getting them back out of the DB each time. Then you have the extra benefit of being able to store the SPRoc files in a revision-control system, combining them to create release-scripts and so on.

5

It will also add a blank line at the character 255 (I think) position on any long lines, which will break the code ...

It doesn't really work.

RightClick on the SProc ni SSMS and "Edit" will give you the whole text, as well using the Generate Script (at the Database level), but that's not much good for repeated use [each time you want to change one], only really any good for a one-time dump of the whole lot.

6

Thanks, yes we can use through SSMS but every time we cant use.

And we also trying : SELECT OBJECT_DEFINITION(OBJECT_ID('proc name')) but it is truncating the text. Is there any setting to increase char size.

Note : SP_helptext is working in other version except 2012.

7

OBJECT_DEFINITION is likely not actually truncating the text. The display of the results of OBJECT_DEFINITION is being truncated. That is, "max" data columns have a display limit in SSMS, so only the first bytes get shown, but the rest of the bytes are actually there.

If you create an nvarchar(max) variable to hold the definition, you will get the entire definition of the object in the variable:

DECLARE @object_definition nvarchar(max)

SELECT @object_definition = OBJECT_DEFINITION('object_name')

SELECT @object_definition AS object_definition /this may not display the full definition, but that does NOT mean the variable does not contain the full definition/

8

Why are you not able to utilize SSMS to modify or script the procedure? It sounds like you are already using SSMS as your tool so that would be the ideal solution.

Any object you select in Object Explorer generally can be scripted. Right-click the object and select the option to script {object} as - then select the appropriate options.

I don't understand the requirement to utilize a methodology that clearly isn't working when there are plenty of alternatives including using other tools (i.e Powershell and SMO can be used to script objects from SQL Server also).