0

Does anyone know how to generate SQL scripts from a query?

For example,

  1. Script some tables.
  2. Do custom action 1.
  3. Script the views.
  4. Do custom action 2.
  5. Etc.
Improve this question
1
  • I'm not sure exactly what you are asking. What is it that you want to end up with? Can you give a simple example? Commented Nov 6, 2008 at 10:13

4 Answers 4

Reset to default
1

It sounds like you want to write a cursor to execute custom SQL. This is common and easy to do. What you need to do is specify a few things to help us more completely answer your question:

  1. What type of SQL server are you using? (MSSQL, Oracle, MySQL)
  2. What language are you writing in? (Java, C++, PL/SQL, TSQL)

You can either write code (Java / C++) to generate SQL from a query, or possibly use a cursor to iterate over recordsets (PL/SQL / TSQL). You can use the results to give you information that can then be executed as SQL via an exec (of some kind depending on the language).

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

... but please investigate SQL injection before implementing dynamic SQL. Look into Parameterized Queries...

Improve this answer

1 Comment

Parameterized Query implementations don't always allow the table names to be parameters. If these are 'create view' statements, I'm not sure these could be parameterized the same way you would a SELECT, INSERT, or DELETE.
0

With Microsoft Sql Server, the best way to script database objects is to use SMO. Sql Management Objects is a c# api, but you could always execute t-sql scripts from c# using a SqlClient.

Improve this answer

Comments

0

You could want something like

select 'UPDATE '+table_name+ ' SET description=''(new!) ''+description WHERE description_date>''2008-11-01''' 
    from information_schema.tables where table_name like '%Description'

(this query generates queries which prepend value of description column with '(new!) ' for each recent row in each table which name ends with 'Description' in a fictional database).

The system view INFORMATION_SCHEMA.TABLES contains data about all database tables, there are also INFORMATION_SCHEMA.VIEWS, INFORMATIONS_CHEMA.COLUMNS and other system views in INFORMATION_SCHEMA table schema.

Hope this will help.

Improve this answer

Comments