Tutorials, tips&tricks, snippets..




String replace in SQL Server database table

Here a fast snippet

update my_table
set path = replace(path, 'oldstring', 'newstring')

If useful, you can also set a where condition.


SQL Group By Date.Day

Another fast snippet: how to group by the from a date/datetime column.

select sum(amount) as total, dateadd(DAY,0, datediff(day,0, created)) as created
from sales
group by dateadd(DAY,0, datediff(day,0, created))

How to fix Recovery Pending State in SQL Server Database

This problem happened to me after I killed the connections PID to one of my databases. The single user mode was removed, but I wasn’t able to use the database, since it was in the Recovery Pending mode. How to fix this problem?

Continue reading “How to fix Recovery Pending State in SQL Server Database”

SQL Server: exit single-user mode

My database was in Single User mode. I don’t know how it entered in this mode, but this causes a lot of problem with the database itself. For example, trying to expand the database, you get an error:

The database ‘my_db’ is not accessible.(ObjectExplorer)

Also, when trying to delete it, you get:

Changes to the state or options of database ‘my_db’ cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.

How to fix this?

Continue reading “SQL Server: exit single-user mode”

Sql Server “Saving changes is not permitted”

When you create a table in SQL Server and save it, sometimes, if you try to edit the table design you get this error:

Saving changes is not permitted. The change you have made requires the following table to be dropped and re-created. You have either made changes to a table that can’t be recreated or enabled the option prevent saving changes that require the table to be re-created.

This can happen also if the table is a new empty table, never used.
Continue reading “Sql Server “Saving changes is not permitted””

SQL Server: check log size

To see how much MB your SQL Server log is, just run this query:

DBCC SQLPERF(logspace)

MySQL: import/export single table

Here how import/export single table using MySQL as DBMS:

Exporting the Table
To export the table run the following command from the command line:

mysqldump -p --user=username dbname tableName > tableName.sql

This will export the tableName to the file tableName.sql.

Importing the Table
To import the table run the following command from the command line:

mysql -u username -p -D dbname < tableName.sql

The path to the tableName.sql needs to be prepended with the absolute path to that file. At this point the table will be imported into the DB.

Enable/disable foreign keys check Azure SQL database

The classic commad working in SQL Server


isn’t working on Azure database.

Here the code to disable the foreign keys on all the table of a database:

DECLARE @enable_constraints bit = 0

--Don't change anything below this line.
DECLARE @schema_name SYSNAME
DECLARE @table_name  SYSNAME

    INNER JOIN sys.schemas ON tables.schema_id = schemas.schema_id

OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @schema_name, @table_name

DECLARE @cmd varchar(200) 
    SET @cmd = 'ALTER TABLE ' + QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name) + ' '
    SET @cmd = @cmd + (CASE WHEN @enable_constraints = 1 THEN 'CHECK' ELSE 'NOCHECK' END) + ' CONSTRAINT ALL'

    PRINT @cmd
    EXEC( @cmd )

    FETCH NEXT FROM table_cursor INTO @schema_name, @table_name

CLOSE table_cursor
DEALLOCATE table_cursor

To enable them again, just change the firs row with

DECLARE @enable_constraints bit = 0

Enable/disable foreign keys check

When you do some stuff on your database, it can be useful disable, for a while, the foreign keys check. Here how to do it:
Continue reading “Enable/disable foreign keys check”

Powered by

Up ↑