Export All Views to Text Files

Question

There are a number of views in a database on one of our servers. I need to save all the view definitions as text files in the following format:

IF OBJECT_ID('<[Schema_Name].[VIEW_NAME]>') IS NOT NULL
    DROP VIEW <[Schema_Name].[VIEW_NAME]>
GO

<<View Definition Here>>

GO

GRANT SELECT ON <[Schema_Name].[VIEW_NAME]> TO [PUBLIC]
GO

The text file should be named as Schema_Name.VIEW_NAME.txt

I don't want to do this manually as this would take up all my time. Any idea if this can be automated? Maybe using SSIS or something?

Answer 1

You can get all views with this query:

SELECT
    s.name,
    av.name,
    sm.definition,
    'IF OBJECT_ID('''+s.name+'.'+av.name+''') IS NOT NULL
    DROP VIEW '+s.name+'.'+av.name+'
GO

'+sm.definition+'

GO

GRANT SELECT ON '+s.name+'.'+av.name+' TO [PUBLIC]
GO' AS script
FROM sys.views AS av
INNER JOIN sys.sql_modules AS sm
    ON av.object_id = sm.object_id
INNER JOIN sys.schemas AS s
    ON av.schema_id = s.schema_id

just create cursor with this select, and for each row store data to file with something like this:

DECLARE @Cmd AS VARCHAR(MAX)
SET @Cmd ='echo ' +  @script + ' > C:\' + @schema + '.' + @view_name + '.txt'
EXECUTE Master.dbo.xp_CmdShell  @Cmd