Tag Info

Hot answers tagged

10

To check for non-default collations on columns, you can use the following query: select table_schema, table_name, column_name, collation_name from information_schema.columns where collation_name is not null order by table_schema, table_name, ordinal_position; Edit: to find the collation of the database, you need to ...


9

In general, procedures should not commit. Those sorts of transaction control decisions should be left to higher-level code that knows when a logical transaction is actually complete. If you commit inside of a stored procedure, you are limiting its reusability because a caller that wants the changes the procedure makes to be part of a larger transaction ...


9

REPLACE does not play with wildcards that way. I think you meant: UPDATE [table] SET [column] = REPLACE([column],'TLD.com','TLD.org') WHERE [column] LIKE '%TLD.com%'; You have no WHERE clause, so it tried to update 618 rows, but it did not find any instances of %TLD.com% in that column. To see which rows should be affected, run a SELECT instead: ...


7

DML can be considered to exclude SELECT statements. The Wikepidia.org entry for “Data Manipulation Launguage” describes it as follows: The purely read-only SELECT query statement is classed with the 'SQL-data' statements2 and so is considered by the standard to be outside of DML. The SELECT ... INTO form is considered to be DML because it ...


7

You do manipulate and restrict data: GROUP BY, ORDER BY, TOP, JOIN, lock hints, etc You just don't change the database state when you do. It boils down to whether you read "manipulate" to include "change state"


6

ANSI is a private non-profit organization that creates voluntary standards. As such it doesn’t actually regulate anything. Often it is to a company’s benefit to follow recognized standards, which is why many database companies follow the ANSI standard for SQL. Of course as each company seeks to differentiate their products, they will develop additional ...


5

At one time or another I've worked with all of the databases you mention. Unfortunately I have found that it doesn't take very long at all for the syntax to deviate in to their own flavours, for anything other than the simplest SELECT, INSERT, UPDATE and DELETE. When it gets to some of the categories you suggest it will quickly get vendor specific. I've ...


4

I can only speak for SQL Server, but the only way this could be done is through a DML trigger (as far as I know). Take the below for example: use TestDB; go create table dbo.TestTriggerTable ( id int identity(1, 1) not null, some_int int not null default 1 ); go insert into dbo.TestTriggerTable default values; go 1000 create trigger ...


3

fn_dblog is the way to look backwards as the other commentators have said. Only allowing the users to modify the data through stored procedures is a great way to prevent this happening in the first place, as you can add logic to prevent users from mass modifying records they shouldn't, or ensuring that they have to provide correct values for updates. This ...


2

Imposing such rules on db level can be very expensive in terms of resources, so I don't think there is a standard way which will work everywhere. I cannot suggest anything better than raising an error inside the body of BEFORE DELETE trigger (or AFTER DELETE, but BEFORE trigger seems to be more appropriate in this case). Unfortunately, it's quite RDMS ...


2

I think I just figured it out... MySQL must count the number of rows that will be updated, not all the ones that will be generated by the join that happens during the update. The difference being that if there are many [B] to one [A], and many [C] to one [B], joining all would create n([C]) rows, but updating [B] would only count n([B]). Adding a set for ...


2

Question asked There is a built-in way to log all statements inside plpgsql functions: auto-explain LOAD 'auto_explain'; SET auto_explain.log_min_duration = 1; -- exclude very fast trivial queries SET auto_explain.log_nested_statements = ON; -- statements inside functions Details under this closely related question: Postgres query plan of a UDF ...


1

So, my suggestion as an actual answer: If you need it only in this function, you can do a RAISE LOG '%', your_statement;, or in your actual code: ... DECLARE exec_str text; ... --Set to NULL the contents of the current 'temp_' column exec_str := 'UPDATE '||dataset_1_row.table_name|| 'SET '||dataset_1_row.column_name||'=NULL ...


1

The least disruptive method would be to use the general query log. The query log output can be a database table or a log (text) file; however it does not support filtering by DML operation or database so all SELECT statements on all databases will be logged as well. Obviously you can filter the logging output later. Also note: The session sql_log_off ...


1

In Oracle when you create a table or execute an alter statement there is an implied commit. You may want to create the table in a separate function returning a boolean value with "PRAGMA AUTONOMOUS_TRANSACTION" then the calling procedure would get a true/false response as to whether or not the table was created. you will then be able to commit or rollback ...


1

update table1 set flag_column = 1 where exists (select 1 from table2 where table1.columnx = table2.columnx and table1.columny = table2.columny) This will update every row in table 1 where the x, y column combination exists in table2.


1

We use ApexSQL Log for auditing our production databases. It can show you who and when has deleted or updated the records. It also tracks inserts and Create/alter/drop statements for data objects. It's good that you have copies of the database and the transaction logs when these changes took place, as ApexSQL Log can read these transactions from the ...


1

From reading the documentation, it looks like DML on a synonym for a normal object will not be replicated. For example, you have a table called FOO & a synonym called BAR that points to FOO. An INSERT into FOO will succeed, but an INSERT into BAR will not, as it is a synonym for another table & not a real base table. Test this in your dev ...


1

Just tossing it out there for discussion, even tho it could've likely been a comment as well. This is why most people like using the various frameworks nowadays like EntityFramework and LINQ, where all the underlying abstraction of querying is relegated to the application layer, and the datalayer is just flat storage. Note that this gives another benefit ...



Only top voted, non community-wiki answers of a minimum length are eligible