xizwyck
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!
I'd like to do this without purchasing software.
If there was a way to do this in ASP, even better!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This will display the list of views in your database.
select * from sysobjects where type ='V'
select * from sysobjects where type ='V'
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
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.
Go to the Tasks -> Generate Scripts -> Select all views
and then generate the script.
gbshahaq suggested that
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.
select text from syscomments where text like 'Create View%'