CREATE PROCEDURE sp_export_KLLschema
AS
BEGIN
  SET NOCOUNT ON
  CREATE TABLE #MyObjectHierarchy 
   (
    HID int identity(1,1) not null primary key,
    ObjectId int,
    TYPE int,OBJECTTYPE AS CASE 
                             WHEN TYPE =  1 THEN 'FUNCTION' 
                             WHEN TYPE =  4 THEN 'VIEW' 
                             WHEN TYPE =  8 THEN 'TABLE' 
                             WHEN TYPE = 16 THEN 'PROCEDURE'
                             WHEN TYPE =128 THEN 'RULE'
                             ELSE ''
                           END,
   ONAME varchar(255), 
   OOWNER varchar(255), 
   SEQ int
   )
   --our results table
  CREATE TABLE #Results(ResultsID int identity(1,1) not null,ResultsText varchar(max) )
  --our list of objects in dependancy order
  INSERT #MyObjectHierarchy (TYPE,ONAME,OOWNER,SEQ)
    EXEC sp_msdependencies @intrans = 1 

 Update #MyObjectHierarchy SET ObjectId = object_id(OOWNER + '.' + ONAME)
  --synonyns are object type 1 Function?!?!...gotta remove them
  DELETE FROM #MyObjectHierarchy WHERE objectid in(
    SELECT [object_id] FROM sys.synonyms UNION ALL
    SELECT [object_id] FROM master.sys.synonyms)
    --custom requirement: only objects starting with KLL
  DELETE FROM #MyObjectHierarchy WHERE  LEFT(ONAME,3) <> 'KLL' 
  DECLARE
    @schemaname     varchar(255),
    @objname        varchar(255),
    @objecttype     varchar(20),
    @FullObjectName varchar(510)

  DECLARE cur1 CURSOR FOR 
    SELECT OOWNER,ONAME,OBJECTTYPE FROM #MyObjectHierarchy ORDER BY HID
  OPEN cur1
  FETCH NEXT FROM cur1 INTO @schemaname,@objname,@objecttype
  WHILE @@fetch_status <> -1
	BEGIN
       SET @FullObjectName = QUOTENAME(@schemaname) + '.' + QUOTENAME(@objname)
              PRINT @FullObjectName
       IF @objecttype IN( 'VIEW','FUNCTION','PROCEDURE')
         BEGIN
           INSERT INTO #Results(ResultsText)
             EXEC sp_helptext @FullObjectName
        --we need a batch seperator:
        INSERT INTO #Results(ResultsText)
        SELECT 'GO'

          END
	   FETCH NEXT FROM cur1 INTO @schemaname,@objname,@objecttype
	 END
    CLOSE cur1
    DEALLOCATE cur1
  SELECT ResultsText FROM #Results ORDER BY ResultsID
END
GO

--- Script out Database objects
set @code = '"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\scptxfr.exe" /s MyServerName ' + ' /d ' + @DatabaseName +  ' /I /F ' 
	+ '\\UNCServerToWriteTo\SQLBACKUP\MyServerName\Object_Scripts\' + @DatabaseName + '_structure_'+ convert(varchar(8),getdate(),112) + '' + '  /q /A /r'
--select @code
EXEC master..xp_cmdshell  @code