well, here's a simple procedure that will return a table with the definitions of just the view/proc/function objects that begin with "KLL" in the correct dependancy order.

you could then create a scheduled job which calls bcp to output this to a single file.

if you have TABLEs that begin with KLL, i have a different version for that, but it's basicalyl the same.

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