There are two primary methods to view the tables in MySQL:
- The MYSQL SHOW TABLES command.
- Querying the Information_schema.tables table.
In this article, we are going to explore them both.
Different Ways to View Tables in MySQL Server
Author: Nisarg Upadhyay
Nisarg is a SQL Server Database Administrator and Microsoft certified professional who has more than 5 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the Bachelors in Information Technology from Ganpat University.
SQL Server RAISERROR Statement with Simple Examples
The SQL RAISERROR statement is used to send a custom message to the client application. It also can be used to debug the application and applies to the error handling mechanism.
SQL DROP Table, DROP Index, and DROP Database Statements Explained with Examples
The SQL DROP statement is a command that deletes existing database components or the entire databases with all their data permanently. There are other SQL commands with a similar effect (TRUNCATE or DELETE), but the specificity of the DROP command is that it deletes everything at once. For instance, DROP TABLE removes the table data, indexes, triggers, permissions, constraints – the entire schema of the table.
The DROP statement requires one to be extremely careful when using it. Once performed, it can’t be rolled back. The information is lost for good. The only chance to return the data will be to restore a backup. Still, in many cases, we have to apply DROP statements. This article will focus on such cases, the specific DROP variants, and how to use them safely.
How to Delete Entire MySQL Database
In some cases, we need to delete the entire database from the MySQL community server. This is simple but irreversible. Thus, the DROP DATABASE command must be used very carefully. The current article will highlight the specificities of the task and explain how to safely drop the MySQL database manually with the help of specialized tools. The following topics will be in the focus:
- Syntax of the Drop Database statement
- Creating a database using MySQL Workbench and command prompt.
- Dropping a database using MySQL Workbench and command prompt.
SQL DEFAULT Constraint to Insert Column with a Default Value to SQL Server Table
To insert data into a table having columns with default constraints, we can use the DEFAULT constraint to a default value in a table. This article will cover the following aspects:
- The DEFAULT constraint and required permission to create them
- Adding a DEFAULT constraint while creating a new table.
- Adding a DEFAULT constraint into an existing table.
- Modifying and Viewing the constraint definition using T-SQL scripts and SSMS.
Understanding SQL DELETE Column from Table
You can delete column from table in SQL in different ways. The most popular of them are:
- Table-designer of SQL Server Management studio.
- ALTER TABLE statement.
In addition to the above methods, we are going to explore the following use cases:
- Drop the column that has a foreign key constraint.
- Drop the column that has a unique constraint.
Understanding DROP TABLE IF EXISTS SQL Statement
The DROP TABLE IF EXISTS statement is used to drop existing database objects. When we create a complex stored procedure, we might also make several intermediate tables (physical tables or temp tables) to store and process the data. Now, we must add the code that drops those intermediate tables in the stored procedure. Otherwise, we might receive an error.
3 Methods to Rebuild All Indexes for All Tables with T-SQL in SQL Server Database
There are several methods of how to rebuild all indexes of all tables in SQL Server, among them:
- Using SQL Server maintenance plans.
- Using T-SQL script based on the fragmentation percent.
- Using ALTER INDEX command.
In this article, we’ll explore these methods and illustrate them with practical examples.
Renaming Indexes with sp_rename Procedure
Not so long ago, I was working on a project where we needed to change the data type of a table. The table had millions of rows, and we decided to create a separate table using the existing table definition and insert the rows into that table. After exporting data into the new table, we renamed the old table, constraint, and indexes using the sp_rename stored procedure.
The current article is dedicated to the ways of renaming indexes. You can also read how to rename table in SQL.
Create and Configure Oracle Linked Server in SQL Server
This article explains the step-by-step process of how to configure SQL Server linked Server Oracle. For this purpose we will consider 2 ways: via SSMS and via T-SQL.
