sqlsql-server-2008system-tables

MS Sql Server Object Creation / Alter Script

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

Solution

  • 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')