CODEWITHSUNDEEP
sql
Tired
Tired

Reputation:

How to generate sql scripts from a query

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.

Upvotes: 0

Views: 914

Answers (4)

Alexander Prokofyev
Alexander Prokofyev

Reputation: 34515

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.

Upvotes: 0

Sean Reilly
Sean Reilly

Reputation: 21836

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.

Upvotes: 0

Richard B
Richard B

Reputation: 1183

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

Upvotes: 1

Kieveli
Kieveli

Reputation: 11075

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

Upvotes: 1

Related Questions