Stored procedure and how to script them
Imagine having hundred of stored procedures in your database and you want to move them to another database. There are several ways to move them.
1) In MMSM you can right-click each procedure and select "script stored procedure as -> create to -> new query window" but this might be tidious and everlasting
2) You can use information_schema.routines:
select routine_definition from information_schema.routines
where routine_type = 'PROCEDURE'
There is only one limitation - the size of routine_definition. Try this with procedure longed that 8000 characters. Failed
3) One can also use catalog view syscomments :
select
c.text
,c.colid
,o.name
from syscomments as c
join sysobjects as o
on c.id = o.id
where
o.xtype = 'P'
again. there is limitation to field text of syscomments to only 4000 characters. due to this limitation one get field called colid denoting the part of procedure so you can either later concenate it. but stil tidious job.Failed
4) and finally, using catalog view sys.sql_modules:
select
s.definition
,s.object_id
,o.name
from sys.sql_modules as s
join sys.objects as o
on o.object_id = s.object_id
where
o.type_desc = 'SQL_STORED_PROCEDURE'
Finally catalog view in SQL Server 2008 with field definition set as nvarchar(max) is much more promising that any other catalog views (in previous versions as well as more promising as information_schema views).
Only sys.sql_modules is SQL Server 2008 code, everything else can be used in 2000, 2005 or 2008.
And additional hint. Set your results in SSMS to "Results to text". And you will do all the magic.
Happy scripting