TSQL Copy an existing database
I was wondering whether such useful query could also be used in standard ms sql server some how?


No, CREATE DATABASE .. COPY OF .. is ONLY applicable to  Azure SQL Database

As a side note, 


  Copying a database using the CREATE DATABASE statement is an asynchronous operation. Therefore, a connection to the SQL Database server is not needed for the full duration of the copy process.  You can monitor the copy process with the sys.dm_database_copies and sys.databases views. The sys.dm_operations_status view can be used as well as it returns the status of database operations including database copy. 


In order to create a copy of current database in SQL Server, you have to backup the database and then restore it with a different name

e.g.

backup database OriginalDB
to disk = 'D:\backup\OriginalDB_full.bak'
with init, stats =10;

restore database new_db_name
from disk = 'D:\backup\OriginalDB_full.bak'
with stats =10, recovery,
move 'logical_Data_file' to 'D:\data\new_db_name.mdf',
move 'logical_log_file' to 'L:\log\new_db_name_log.ldf'