Copy a table in MySQL

Use structure CREATE TABLE new_table LIKE old_table

The following queries copy table vtiger_account in vtigerCRM
First, create an empty table with the same structure
CREATE TABLE vtiger_account_bk LIKE vtiger_account;

Second, copy data
INSERT INTO vtiger_account_bk SELECT * FROM vtiger_account;

Get indexes of a MySQL table

Get all indexes
SHOW INDEXES FROM your_table_name;

Get primary keys only
SHOW INDEXES FROM your_table_name
WHERE key_name = "PRIMARY";

Get indexes of vtiger_account table in vtigerCRM
SHOW INDEXES FROM vtiger_account;

Generate script for MySQL table

The following script gets create table script of vtiger_account table in vtigerCRM.

SHOW CREATE TABLE vtiger_account;

Get all tables/views in a database

The following query returns all tables/views in database vtigercrm510 and numbers of records for each table.

SELECT table_name, table_type, engine, table_rows
FROM information_schema.tables t1
WHERE table_schema = 'vtigercrm510'
ORDER BY table_name DESC;

Get all vtigerCRM quotes

The following query gets vtigerCRM Quote List

SELECT t1.quote_no AS 'Quote No'
, t1.subject AS 'Subject'
, t1.quotestage AS 'Quote Stage'
, t4.potentialname AS 'Potential Name'
, t5.accountname AS 'Account Name'
, t1.total AS 'Total'
, t3.user_name AS 'Assigned To'
FROM vtiger_quotes t1
INNER JOIN vtiger_crmentity t2 ON t1.quoteid = t2.crmid AND t2.setype='Quotes'
INNER JOIN vtiger_users t3 ON t2.smownerid = t3.id
LEFT JOIN vtiger_potential t4 ON t1.potentialid = t4.potentialid
LEFT JOIN vtiger_account t5 ON t1.accountid = t5.accountid

Get all vtigerCRM Services

The following query gets vtigerCRM Service List
SELECT t1.service_no AS 'Service No'
, t1.servicename AS 'Service Name'
, t1.service_usageunit AS 'Usage Unit'
, t1.unit_price AS 'Price'
, t1.qty_per_unit AS 'No of Units'
, t1.servicecategory AS 'Category'
, t3.user_name AS 'Owner'
FROM vtiger_service t1
INNER JOIN vtiger_crmentity t2 ON t1.serviceid = t2.crmid AND t2.setype='Services'
INNER JOIN vtiger_users t3 ON t1.handler = t3.id

Backup MySQL Database

Use mysqldump to backup your MySQL db, you can find it in MySQL Server bin folder.
Syntax:
$mysqldump --opt -u [uname] -p[pass] [dbname] > [backupfile.sql]

For example, the following command backup vtigerCRM database:
C:\Program Files\MySQL\MySQL Server 5.0\bin>mysqldump -u root -p vtigercrm510 >vtigercrm510bk.sql

Get all vtigerCRM invoices

The following query gets vtigerCRM Invoice List
SELECT t1.invoice_no AS 'Invoice No'
, t1.subject AS 'Subject'
, t3.subject AS 'Sales Order'
, t1.invoicestatus AS 'Status'
, t1.total AS 'Total'
, t4.user_name AS 'Assigned To'
FROM vtiger_invoice t1
INNER JOIN vtiger_crmentity t2 ON t1.invoiceid = t2.crmid AND t2.setype='Invoice'
LEFT JOIN vtiger_salesorder t3 ON t1.salesorderid = t3.salesorderid
INNER JOIN vtiger_users t4 ON t2.smownerid = t4.id

Get all vtigerCRM Purchase Orders

The following query gets vtigerCRM Purchase Order List

SELECT t1.purchaseorder_no
, t1.subject
, t3.vendorname
, t1.tracking_no
, t1.total
, t4.user_name
FROM vtiger_purchaseorder t1
INNER JOIN vtiger_crmentity t2 ON t1.purchaseorderid = t2.crmid AND t2.setype='PurchaseOrder'
INNER JOIN vtiger_vendor t3 ON t1.vendorid = t3.vendorid
INNER JOIN vtiger_users t4 ON t2.smownerid = t4.id

Get all vtigerCRM Sales Order

The following query gets vtigerCRM Sales Order List

SELECT t1.salesorder_no AS 'SalesOrder No'
, t1.subject AS 'Subject'
, t4.accountname AS 'Account Name'
, t1.total AS 'Total'
, t3.user_name AS 'Assigned To'
FROM vtiger_salesorder t1
INNER JOIN vtiger_crmentity t2 ON t1.salesorderid = t2.crmid AND t2.setype = 'SalesOrder'
INNER JOIN vtiger_users t3 ON t2.smownerid = t3.id
INNER JOIN vtiger_account t4 ON t1.accountid = t4.accountid

Get all vtigerCRM products

The following query gets all vtigerCRM Products

SELECT t1.product_no AS 'Product No'
, t1.productname AS 'Product Name'
, t1.productcode AS 'Part Number'
, t1.commissionrate AS 'Commission Rate'
, t1.qtyinstock AS 'Quality In Stock'
, t1.qty_per_unit AS 'Qty/Unit'
, t1.unit_price AS 'Unit Price'
FROM vtiger_products t1
INNER JOIN vtiger_crmentity t2 ON t1.productid = t2.crmid AND t2.setype = 'Products'

Get all vtigerCRM vendors

This query gets all vtigerCRM vendors

SELECT t1.vendor_no
, t1.vendorname
, t1.phone
, t1.email
, t1.category
FROM vtiger_vendor t1
INNER JOIN vtiger_crmentity t2 ON t1.vendorid = t2.crmid AND t2.setype = 'Vendors'

Get all vtigerCRM Leads

This query get all leads in vtigerCRM

SELECT t1.lead_no AS 'Lead No'
, t1.lastname AS 'Last Name'
, t1.firstname AS 'First Name'
, t1.company AS 'Company'
, t4.phone AS 'Phone'
, t5.website AS 'Website'
, t1.email AS 'Email'
, t3.user_name AS 'Assigned To'
FROM vtiger_leaddetails t1
INNER JOIN vtiger_crmentity t2 ON t1.leadid = t2.crmid AND t2.setype = 'Leads'
INNER JOIN vtiger_users t3 ON t2.smownerid = t3.id
LEFT JOIN vtiger_leadaddress t4 ON t1.leadid = t4.leadaddressid
LEFT JOIN vtiger_leadsubdetails t5 ON t1.leadid = t5.leadsubscriptionid

Get all vtigerCRM campaigns

This query gets all vtigerCRM Campaigns

SELECT t1.campaign_no AS 'Campaign No'
, t1.campaignname AS 'Campaign Name'
, t1.campaigntype AS 'Campaign Type'
, t1.campaignstatus AS 'Campaign Status'
, t1.expectedrevenue AS 'Expected Revenue'
, t1.closingdate AS 'Expected Closde Date'
, t3.user_name AS 'Assigned To'
FROM vtiger_campaign t1
INNER JOIN vtiger_crmentity t2 ON t1.campaignid = t2.crmid AND t2.setype = 'Campaigns'
INNER JOIN vtiger_users t3 ON t2.smownerid = t3.id

Find all accounts in vtiger_crmentity

This query is applied to vtigerCRM database structure
Table vtiger_cmentity contains information about Accounts, Contacts, Vendors, etc. and some other information for tracking.

SELECT * FROM vtiger_crmentity
WHERE setype = 'Accounts'

List all tables contain 'account'

I'm using vtiger CRM, I used this query to find which tables contain account so that I can understand the relationship among them.

SELECT * FROM information_schema.tables
WHERE table_name LIKE '%account%'