Scripting Objects from specific schema.

USE [AdventureWorksDW2012]
GO
/****** Object:  StoredProcedure [dbo].[spGenerateScriptFileForSchemaObjects]    Script Date: 10/2/2013 9:46:02 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spGenerateScriptFileForSchemaObjects]
(
    --@schema varchar(10) = 'dbo'
    @ObjectType varchar(10) = 'P'
)
AS
BEGIN

    DECLARE @name varchar(100)
    DECLARE @Definition varchar(max)
    DECLARE @sql varchar(300)   
    DECLARE @Script varchar(max)
    DECLARE @schema varchar(10)

    SET @sql = ''

    CREATE TABLE TEMPTABLE (ID INT IDENTITY(1,1), def varchar(max))

    DECLARE script CURSOR 
    FOR
    SELECT OBJECT_NAME(SYS.SQL_MODULES.OBJECT_ID), [DEFINITION] FROM SYS.SQL_MODULES
    INNER JOIN SYS.OBJECTS ON SYS.OBJECTS.OBJECT_ID = SYS.SQL_MODULES.OBJECT_ID
    WHERE SYS.OBJECTS.TYPE IN (@ObjectType)
   
    OPEN script
    FETCH NEXT FROM script INTO @name, @Definition
    WHILE @@FETCH_STATUS = 0
    BEGIN
      FETCH NEXT FROM script INTO @name, @Definition
      SET @schema = (select SYS.SCHEMAS.[NAME] from SYS.OBJECTS
                     INNER JOIN SYS.SCHEMAS ON SYS.OBJECTS.SCHEMA_ID = SYS.SCHEMAS.SCHEMA_ID
                     WHERE SYS.OBJECTS.[NAME]='' + @name + '')
      SET @sql = 'IF EXISTS (SELECT * FROM ' + (@schema) +
                 '.PROCEDURES WHERE [NAME] = ''' + @name + ''')' + CHAR(10)
      SET @sql = @sql + 'DROP PROCEDURE ' + @schema + '.' + @name + CHAR(10) + 'GO' + CHAR(10)
      --PRINT @sql
      INSERT INTO TEMPTABLE VALUES(@sql + @definition)
 
    END
    CLOSE script
    DEALLOCATE script

    SELECT  @Script = IsNULL(@Script,'') + def FROM TEMPTABLE ORDER BY ID DESC

    --SET @Sql = ('BCP "SELECT  @Script" queryout "D:\' + @name + '.sql" -c -T')
    SET @Sql = ('BCP "SELECT  @Script" queryout "D:\SmartReportingScript.sql" -c -T')
    EXEC XP_CmdShell @Sql  --<---- Export to file

    --SELECT * FROM TEMPTABLE  --<----------- Optional
    --SELECT * FROM TEMPTABLE

    DROP TABLE TEMPTABLE
END