--drop table fileOutput
CREATE PROCEDURE [dbo].[spWriteOutAllRoutines]
@whereTo VARCHAR(100) --'e:\MyDir\MyDir\wSubdir'
--you must enter the pathname without a '\' at the end
AS
SET nocount ON
--you can only exec into a permanent or temporarry file
--but BCPing out requires a permanent one which is why
--we use the FileOutput table!
IF NOT EXISTS ( SELECT *
FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[dbo].[FILEoutput]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1 )
BEGIN
CREATE TABLE [dbo].[FILEoutput]
(
[MyID] [int] IDENTITY(1, 1)
NOT NULL,
[line] [varchar](8000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
)
ON [PRIMARY]
END
DECLARE @listOfJobs TABLE
(
MyID INT IDENTITY(1, 1),
name VARCHAR(100),
thetype VARCHAR(5)
)
DECLARE @ii INT
DECLARE @iiMax INT
DECLARE @RoutineName VARCHAR(100)
DECLARE @type VARCHAR(10)
DECLARE @Command VARCHAR(8000)
INSERT INTO @listOfJobs ( Name, TheType )
SELECT name,
xtype
FROM sysobjects o
INNER JOIN syscomments c ON c.id = o.id
WHERE xtype IN ( 'C', --CHECK constraint
'D', --Default or DEFAULT constraint
'FN',--function
'P', --procedure
'R',--rule
'TF', --table function
'TR',--Trigger
'V' ) --view
AND category <> 2
AND colid = 1
SELECT @ii = MIN(MyID),
@iiMax = MAX(MyID)
FROM @ListOfJobs
WHILE @ii <= @iiMax
BEGIN
SELECT @RoutineName = Name,
@Type = TheType
FROM @ListOfJobs
WHERE MyID = @ii
SELECT @ii = @ii + 1
TRUNCATE TABLE FileOutput
INSERT INTO FileOutput ( line )
SELECT '
' + @routineName + '
'
INSERT INTO FileOutput ( line )
SELECT '' + @routineName + '
'
+ CASE @type
WHEN 'C' THEN 'Check constraint'
WHEN 'D' THEN 'Default or default constraint'
WHEN 'FN' THEN 'function'
WHEN 'P' THEN 'procedure'
WHEN 'R' THEN 'rule'
WHEN 'TF' THEN 'table function'
WHEN 'TR' THEN 'Trigger'
WHEN 'V' THEN 'view'
ELSE 'unknown ''' + COALESCE(@type, 'NULL')
+ ''''
END + '
' + ''
+ CONVERT(CHAR(11), GETDATE(), 113) + '
'
INSERT INTO FileOutput ( line )
EXECUTE spPrettify @routineName = @RoutineName
SELECT @command
SELECT @Command = 'bcp "USE ' + DB_NAME() + ' Select line from '
+ DB_NAME()
+ '.dbo.FILEoutput order by MyID asc" queryout '
+ @whereTo + '\' + @RoutineName + '_' + LTRIM(RTRIM(@Type))
+ '.html -c -S ' + @@Servername + ' -E -T'
EXECUTE MASTER..xp_cmdshell @command
END
TRUNCATE TABLE FileOutput
INSERT INTO FileOutput ( line )
SELECT '
Index of routines in' + @@ServerName + '.' + DB_NAME()
+ '
Index of routines in' + @@ServerName + '.' + DB_NAME() + '
' + CONVERT(CHAR(11), GETDATE(), 113) + '
'
INSERT INTO FileOutput ( line )
SELECT ' ' + name + ' ('
+ +CASE @type
WHEN 'C' THEN 'Check constraint'
WHEN 'D' THEN 'Default or default constraint'
WHEN 'FN' THEN 'function'
WHEN 'P' THEN 'procedure'
WHEN 'R' THEN 'rule'
WHEN 'TF' THEN 'table function'
WHEN 'TR' THEN 'Trigger'
WHEN 'V' THEN 'view'
ELSE 'unknown ''' + COALESCE(@type, 'NULL') + ''''
END + ')
'
FROM @ListOfJobs
ORDER BY name
SELECT @Command = 'bcp "USE ' + DB_NAME() + ' Select line from '
+ DB_NAME() + '.dbo.FILEoutput order by MyID asc" queryout '
+ @whereTo + '\index.html -c -S ' + @@Servername + ' -E -T'
EXECUTE MASTER..xp_cmdshell @command, no_output