How do I preview a destructive SQL query?
I would use the OUTPUT clause present in SQL SERVER 2008 onwards...
OUTPUT Clause (Transact-SQL)
Something like...
BEGIN TRANSACTION
DELETE [table] OUTPUT deleted.* WHERE [woof]
ROLLBACK TRANSACTION
INSERTs and UPDATEs can use the 'inserted' table too. The MSDN article covers it all.
EDIT:
This is just like other suggestions of SELECT then DELETE inside a transaction, except that it actually does both together. So you open a transaction, delete/insert/update with an OUTPUT clause, and the changes are made while ALSO outputting what was done. Then you can choose to rollback or commit.
How do I test a sql query that modifies data, i.e. see the output before committing in sql server 2008?
Yes this is possible. You can either use the session option SET IMPLICIT_TRANSACTIONS ON
or create an explicit transaction as below.
BEGIN TRAN
UPDATE YourTable
SET foo=1
/*To Review Changes can use OUTPUT clause here...*/
OUTPUT INSERTED.*, DELETED.*
WHERE bar=2
/*... or a SELECT against the table*/
SELECT *
FROM YourTable
WHERE bar=2
-- Execute the COMMIT or ROLLBACK commands when ready
However you should be aware that your open transaction will hold locks until the transaction completes which may block other transactions so this should be used with caution in any multi user environment.
Preview activerecord queries in rails console?
You can call .to_sql on an ActiveRecord::Relation to see the SQL that would be executed.
User.where(:id => 4).to_sql
=> "SELECT \"users\".* FROM \"users\" WHERE \"users\".\"id\" = 4"
Also, the console will only automatically execute the relation (and instantiate the objects) if it's the last command on the line, so you can do this:
relation = User.where(:id => 4); 1
=> 1
and thus set a variable to the relation without running it.
I'm not actually sure which of these two you wanted to do, but they're both handy tricks.
detectecting destructive SQL queries with C#
You can do:
if (badSqlList.Any(r => sqltext.IndexOf(r, StringComparison.InvariantCultureIgnoreCase) >= 0))
{
//bad SQL found
}
IndexOf
with StringComparison
enum value will ensure case insensitive comparison.
Another approach could be:
return sqltext.Split()
.Intersect(badSqlList,StringComparer.InvariantCultureIgnoreCase)
.Any()
Split
your Sql on white space and then compare each word with your white list array. This could save you in cases where your legal table name has keyword like INESRTEDStudents
Not really sure about your requirements, but, generally, a better option would be to use Parameterized queries in the first place. You can't be 100% sure with your white list and there still would be ways to bypass it.
Is there a command to test an SQL query without executing it? ( MySQL or ANSI SQL )
I realise this is a bit of an old question but for completeness...
If the intention is to find the query processing time without returning any rows (I need this quite often, I want to know how long a piece of code I am using will take without having it return a couple of million rows I am not interested in seeing) then the BLACKHOLE engine can be very useful:
https://dev.mysql.com/doc/refman/8.0/en/blackhole-storage-engine.html
For instance say I have 2 tables, t1 & t2, with millions of rows, that I am joining together. I want to check how long this is likely to take, in a GUI (SQLYog or mysql workbench or somesuch) without returning millions of rows that will eat up memory and presumably take time for the GUI to process and display. I use the blackhole engine to 'dump' the rows to nowhere.
EG:
CREATE TABLE tBH (a TINYINT) ENGINE = BLACKHOLE;
SELECT NOW(); -- Show start time
INSERT tBH
SELECT 1 FROM t1
LEFT JOIN t2 ON t1.key1 = t2.key1;
SELECT NOW(); -- Show end time
Note that as I am just looking for execution time I do not bother returning all the columns (IE with "*") but just a placeholder ("1" in this case).
Performant techniques for finding similar values in SQL?
If you are using SQL Server, you might look into using the SOUNDEX() function as in:
...
where
SOUNDEX("searchterm") = SOUNDEX(searchvaluefield)
it is supposed to do Phonetic matching on the strings ...
Some odd examples ... so it seems you could catch plurals by always appending the plural text to both sides, since multiple 's's sound the same ... :-)
select soundex('Canine'), soundex('Canines')
go
----- -----
C550 C552
1 Row(s) affected
select soundex('Canine'), soundex('Caynyn')
go
----- -----
C550 C550
1 Row(s) affected
select soundex('Canines'), soundex('Caniness')
go
----- -----
C552 C552
1 Row(s) affected
RapidSQL Shortcuts
They're available in the RapidSQL user guide starting on page 53;
Keyboard Shortcuts
Rapid SQL provides a number of keyboard shortcuts to help you expedite your tasks.
The table below lists the taxes and related shortcuts:Delete one character to the left BACKSPACE
Delete one character to the right DELETE
...and so on...
Related Topics
Use Tnsnames.Ora in Oracle SQL Developer
How to Compare Values Which May Both Be Null in T-Sql
Sqlite Order by String Containing Number Starting with 0
Selecting Entries by Date - >= Now(), MySQL
Any References/Manuals on SQL in Excel with Microsoft Ole Db Provider for Jet 4.0
Referencing Outer Query's Tables in a Subquery
Django: Using Custom Raw SQL Inserts with Executemany and MySQL
Entity Framework: How to Properly Handle Exceptions That Occur Due to SQL Constraints
Postgres "Missing From-Clause Entry" Error on Query with With Clause
MySQL Nested Sets - How to Find Parent of Node
SQL Server: Get Data for Only the Past Year
Query to Check Index on a Table
SQL Query to Return Only 1 Record Per Group Id
Custom Post Type Yearly/ Monthly Archive
Do Indexes Work with "In" Clause