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
Lowell