MS Sql Server Object Creation / Alter Script
In Sql Server when you select to modify a SP or U开发者_如何学JAVADF 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
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')
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.
精彩评论