How to generate sql scripts from a query.

Question

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.

Answer 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).

Answer 2

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

Answer 3

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.

Answer 4

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.