SQL commands that may be useful in the future

I will share with you some commands that are useful for many database operations, such as character encodings of table-columns, table states, triggers, schema change, table reset, database deletion, etc. I think you should have a look. There are no plain filter commands in our article. When you find out that beginners do not use it often, I have added solutions that can save life for you. Continued in the article.

---

• You may want to delete all data in the table. This technique will help you when your IDE gets angry and warns you of an insufficient filter. (where 1=1)

delete from table1 where 1=1;

• Indispensable performance parameter in your test queries. Limit! You don't need to pull all the data and see the result. Try to use limits as much as possible.

select * from table1 limit 10;

• DATABASE DELETION

USE Master;
GO
DROP DATABASE test_db;
GO

DROP DATABASE test_db;

• RESTORING BACKED-UP DATABASE FROM FILE (MSSQL)

RESTORE DATABASE test_db
FROM DISK = 'D:\test_db.bak'
WITH REPLACE,
MOVE 'test_db_data' TO 'D:\Backup\MSSQL\test_db.mdf',
MOVE 'test_db_data_log' TO 'D:\Backup\MSSQL\test_db.ldf'

• CHANGE SCHEMA (changes test_shema name to dbo.)

IF (NOT EXISTS (SELECT *
FROM sys.schemas
WHERE name = 'test_shema'))
BEGIN
    EXEC ('CREATE SCHEMA [dbo] AUTHORIZATION [test_shema]')
END
ALTER SCHEMA dbo
    TRANSFER test_shema .__ EFMigrationsHistory
GO
ALTER SCHEMA dbo
    TRANSFER test_shema.table1
GO
ALTER SCHEMA dbo
    - (Your Other Tables)
GO

• RESET TABLE (Even automatically increasing ids are reset)

truncate table table1;

• LISTING TRIGGERS

SHOW TRIGGERS LIKE 'trg%';

• SEE THE DATABASE CODING (if your filtering or searches do not give you correct results, it is useful to examine this part with the next paragraph)

show variables like "collation_database";

• SEE TABLE STATUS (Here you can see all the details about your table, encodings, characters, size, row and column numbers, data types, etc.)

show table status;
show full columns from table1;