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.
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).
... but please study SQL injection before implementing dynamic SQL. Explore Parameterized Queries ...
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.
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.