SQL Server : Generate Scripts for DB objects having text

Generate Scripts for DB objects having text. Implies objects which have text body, like procedures, TVF's

etc.

Imagine you need script of DB objects, of which you just have string/name identifier. 

You don't know the names of object, and you need this script in order of object's names.

Following query will provide you result as xml script to generate DB scripts for required objects.

Script to get xml script

On executing this script you will get xml output as below.

Click or copy this output to new window

If you click on this output or copy paste this output in new window you will get script as below

DB script to generate scripts of your required objects

Text of query :

Use

GO
SELECT

--DISTINCT
'SP_HelpTEXT ''['+s.name+'].' + o.name +'''
go
'
--,type --2005V
-- ,s.name
--,o.xtype --2000V
FROM

sys.objects o --2005V
/*sysobjects o -- 2000V*/

INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE

--o.xtype not in ( 'D','U') --2000V
o.type not in ( 'D','U','F','PK','UQ') --2005V
AND o.name LIKE '%%'
order by o.type,o.name
FOR XML PATH('')