MS Sql Server Object Creation / Alter Script

Question

Hi,

In Sql Server when you select to modify a SP or UDF it loads the objects alter query in a query window. Are these queries accessible in a system table so I can store the alter statements somewhere? It looks like syscomments has some objects but not all of them.

Thanks

Answer 1

I'm not entirely sure what you're asking, but you can see stored procedures with the following query:

SELECT SO.Type,SO.Name,SC.Text
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
AND SO.Type = 'P'
ORDER BY SO.Name

If you're after different object types, take a look at sys.sysobjects for the different types.

Answer 2

Don't use syscomments, because syscomments.text is nvarchar(4000) and as a result will truncate anything that is longer.

Use sys.sql_modules because definition is nvarchar(max), it will not truncate long code.

use this to view the text of a given procedure, view, or function:

SELECT * FROM sys.sql_modules WHERE object_id=object_id('YourProcedure')

Use this to get the name, type, and text of any procedure, view, or function:

SELECT DISTINCT
    o.name AS Object_Name,o.type_desc, m.definition
    FROM sys.sql_modules        m 
        INNER JOIN sys.objects  o ON m.object_id=o.object_id
    WHERE m.object_id=object_id('CPT_SP_IRA_ComboBox_IncidentStatus')