[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2459
  • Last Modified:

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!
0
xizwyck
Asked:
xizwyck
1 Solution
 
Chris MangusDatabase AdministratorCommented:
From the database in question try:

select text from syscomments where text like 'Create View%'
0
 
Sham HaqueSenior SAP CRM ConsultantCommented:
alternatively, use Enterprise Manager/Management Studio to do it for you (as CREATE VIEW scripts...):

right-click database > All Tasks > Generate SQL script
Click Show All
Tick All Views

Have a look at the Options tab
you can choose to create one long script or individual files per script
0
 
Shanmuga SundaramDirector of Software EngineeringCommented:
This will display the list of views in your database.
select * from sysobjects where type ='V'
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
sybeCommented:
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

0
 
ursangelCommented:
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.
0
 
Chris MangusDatabase AdministratorCommented:
gbshahaq suggested that
0
 
xizwyckAuthor Commented:
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.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now