Link to home
Start Free TrialLog in
Avatar of xizwyck
xizwyckFlag for United States of America

asked on

MS SQL Server 2000/2005 - Export view SQL to text file.

Is there a way to export the actual SQL of all the views in a database?

I'd like to do this without purchasing software.

If there was a way to do this in ASP, even better!
Avatar of Chris Mangus
Chris Mangus
Flag of United States of America image

From the database in question try:

select text from syscomments where text like 'Create View%'
ASKER CERTIFIED SOLUTION
Avatar of Sham Haque
Sham Haque
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This will display the list of views in your database.
select * from sysobjects where type ='V'
Avatar of sybe
sybe

Combining the code snippets below, you got it in ASP
Set oRS = Connection.OpenSchema(20)
Do While Not oRS.EOF
    If oRS("TABLE_TYPE").Value = "VIEW" Then 
        sViewName = oRS("TABLE_NAME").Value
    End If
oRS.MoveNext: Loop
oRS.Close
 
' ====
 
sViewName  = "vw_myview"
Set oRS = Connection.Execute("SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME ='" & sViewName & "'")
sViewCode = oRS("VIEW_DEFINITION").Value

Open in new window

I think the easiest way to do this is the one cmangus:have suggested
Go to the Tasks -> Generate Scripts -> Select all views
and then generate the script.
gbshahaq suggested that
Avatar of xizwyck

ASKER

Thanks gbshahaq! I set it to write to file, changed the extension to text and it worked great! I had tried something like that before but I was having write to a new tab.