Clever Geek Handbook

How to generate sql scripts from query

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

For example,

  • Script some tables.
  • Performing custom actions 1.
  • Script types.
  • Performing a custom action 2.
  • Etc.
0


source to share


4 answers


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 indicate a few things that will help us more accurately answer your question:

  • What type of SQL server are you using? (MSSQL, Oracle, MySQL)
  • What language do you write in? (Java, C ++, PL / SQL, TSQL)


You can write code (Java / C ++) to generate SQL from a query, or perhaps use a cursor to iterate over recordsets (PL / SQL / TSQL). You can use the results to provide you with information, which can then be executed as SQL using exec (of some kind depending on the language).

+1


source


... but please study SQL injection before implementing dynamic SQL. Explore Parameterized Queries ...



+1


source


With Microsoft Sql Server, the best way for script database objects is to use SMO . Sql Control Objects are C # api, but you can always execute t-sql scripts from C # using SqlClient.

0


source


You might 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 that add a description column value with "(new!)" for every last row in every table whose name ends with "Description" in a fictitious database).

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

Hope this helps.

0


source






More articles:


All Articles