I have two MySQL databases that are very similar to each other. How can I find out the differences in tables, and the differences in columns in each table?   the databases are in different schema. It's only the structure I want to compare, not the data.
102942
To automate the backup process of one of my MySQL databases, I would like to compare the structure of two tables (current version vs old version).  Can you think of a query that can compare two tables?  Here are some example tables that you can compare.   CREATE TABLE product_today (   pname VARCHAR(150),   price int,   PRIMARY KEY (pname) );  CREATE TABLE product_yesterday (   pname VARCHAR(150),   price int,   PRIMARY KEY (pname) );  CREATE TABLE product_2days_back (   pname VARCHAR(15),   price int,   PRIMARY KEY (pname) );   The first two tables have identical structures. The last one is different. I just need to know whether two tables have different structures or not. I'm not interested in the how they differ.
39247
How I can copy my public schema into the same database with full table structure, data, functions, fk, pk and etc. My version of Postgres is 8.4 P.S. I need to copy schema NOT database
103953
I'm afraid that I already know the answer to my question, but I'll ask it anyway:  When there are two MySQL DB servers, can I access data that is stored on the other server?   In other words: Can I somehow do this:  INSERT INTO table (x, y, z)    SELECT x, y, x+y       FROM [otherserver].[database].[table]   Is the answer really as short as "No"?
91184
I have following MySQL db structure:  content ======= id | tags --------- 1  | ,abc,def,ghi, 2  | ,abc,foo, 3  | NULL 4  | ,foo,bar,   categories ========== id -- 1 2   categories_to_content ===================== id | category_id | content_id -----------------------------  1 |           1 |          1  2 |           1 |          2  3 |           2 |          3  4 |           2 |          4   What I'm trying to do is to SELECT tags assigned to category and count them. Result for given schema:  tag | category | count ---------------------- abc |        1 |     2 def |        1 |     1 ghi |        1 |     1 foo |        1 |     1 foo |        2 |     1 bar |        2 |     1   Decomposition isn't possible (I hook up into existing CMS database).  My existing query:  SELECT tags, category_id as category FROM content JOIN categories_to_content ON content_id = content.id
93580
I'm working with a MySQL database that has some data imported from . The data contains non- characters (em dashes, etc.) as well as hidden carriage returns or line feeds. Is there a way to find these records using MySQL?
48061
How can ALTER be used to drop a column in a MySQL table if that column exists?   I know I can use ALTER TABLE my_table DROP COLUMN my_column, but that will throw an error if my_column does not exist. Is there alternative syntax for dropping the column conditionally?  I'm using MySQL version 4.0.18.
59131
I'm creating a user-based website. For each user, I'll need a few MySQL tables to store different types of information (that is, userInfo, quotesSubmitted, and ratesSubmitted).  Is it a better idea to:  a) Create one database for the site (that is, "mySite") and then hundreds or thousands of tables inside this (that is, "userInfo_bob", "quotessubmitted_bob", "userInfo_shelly", and"quotesSubmitted_shelly")  or  b) Create hundreds or thousands of databases (that is, "Bob", "Shelly", etc.) and only a couple tables per database (that is, Inside of "Bob": userInfo, quotesSubmitted, ratesSubmitted, etc.)  Should I use one database, and many tables in that database, or many databases and few tables per database?    Edit:  The problem is that I need to keep track of who has rated what.  That means if a user has rated 300 quotes, I need to be able to know exactly which quotes the user has rated.  Maybe I should do this?  One table for quotes. One table to list users. One table to document ALL ratings that have been made (that is, Three columns: User, Quote, rating).  That seems reasonable. Is there any problem with that?
123831
Is there a module that lists which tables are created, owned or modified by a particular module?  I need something that can show me what tables are created or modified by when new modules are installed.
121058
I want to find out unmatched records of two tables in MySQL.
79067
I'm been taxed with the task of creating an application that pulls data from our Micros Point of Sales system. The POS is using a Sybase database running on one of our servers. The database schema is very convoluted. I've been able to figure out most of the schema to gain access to the data I need, however, there are a few things still left to find. I know what they are called in the actual POS, but I can't seem to find them anywhere in the database (although I haven't actually looked through all 200+ tables).  I'm wondering if there's any way to run a query to search for a specific string in all the columns in all the tables. BTW, I'm using the "Interactive SQL" application that comes with the Sybase Database Client software to connect to the database.
28828
I am pretty well versed with SQL Server, MySQL, Oracle etc but putting these Database products aside, is there a resource that will help me design relational databases well? Is there something like patterns or best practices for database design?  I have seen a few times that database is often not scalable; people have personal preferences with keeping columns like isChecked column which is boolean in nature but stored as Char(1) with values like 'Y' and 'N' instead of 0 and 1 which to me sounds better. Ways not to commit common mistakes while doing database design?  Links to books or articles will be highly appreciated.  Thanks in advance.
50956
I'm maintaining a database that is being shared by a few systems. Over the years a few of those systems have been deprecated and lots of the tables are actually orphaned. Is there a query I can run to find out which tables have not been used since a certain date?  Thanks.
82669
I have two databases - Database1 and Database2.  Both databases contain a table that has similar structure, exemplified as follows:   =========================================================================================== | ID | Name | PhoneNoFormat | DialingCountryCode | InternationalDialingCode | InternetTLD | =========================================================================================== |    |      |               |                    |                          |             | ===========================================================================================   However, due to some reason, (a) one of the tables in one of the databases has data that is not exactly the same as (b) that contained in the other table in the another database.  So, how can I compare Database1.Table1 against Database2.Table1?  I tried using the following query, but nothing happened so was wondering if I have to rewrite it:  SELECT MIN(TableName) as TableName,        ID,        Name,        PhoneNoFormat,        DialingCountryCode,        InternationalDialingCode,        InternetTLD  FROM  (    SELECT  'Table A' as TableName,           A.ID,           A.Name,           A.PhoneNoFormat,           A.DialingCountryCode,           A.InternationalDialingCode,           A.InternetTLD    FROM [D:\DATABASE1.MDF].[dbo].[Table1] AS A    UNION ALL    SELECT 'Table B' as TableName,          B.ID, B.Name,          B.PhoneNoFormat,          B.DialingCountryCode,          B.InternationalDialingCode,          B.InternetTLD    FROM [D:\DATABASE2.MDF].[dbo].[Table1] AS B  ) tmp  GROUP BY ID, Name, PhoneNoFormat, DialingCountryCode, InternationalDialingCode, InternetTLD  HAVING COUNT(*) = 1  ORDER BY ID
104926
I have a DB Server running CentOS 6.4 and MySQL 5.5 from RPMs (not source).  I have dozens of databases but one of them is being hit quite hard with large selects which locks out other updates which in turn locks out other selects (at least I think this is what's happening!)  To alleviate this I would like to replicate one of the databases onto the same server so that the writes can write to the "master" and reads can be done on the "slave". I don't have the luxury of additional hardware.  I have found a lot of guides on the internet describing how to replicate to a different server, and one () describing how to replicate to the same server but it doesn't seem to be geared towards retrofitting this functionality to an existing database with 10s of GB of data in it already. It also talks about compiling MySQL from source.  Does anyone know how I can retrospectively convert my MySQL 5.5 config to replicate one of my databases on the same server?  My existing DB runs on port 3306 so I feel it should be as easy as firing up another instance of MySQL on port 3307 and making some changes to my.cnf but not sure what changes.  Thanks in anticipation.
13604
I have a piece of SQL that seems to run really fast in Environment A, but the exact same query runs really slow in Environment B!  Environments are supposed to be the same, so what should I do and/or where should I look in order to see why the query doesn't perform the same?
120116
Problem:  Is there any way of calculating number of bytes occupied by the table creation, i know that you can get some information from information_schema.tables but that information is not accurate enough.             What actually required is the number of bytes according to the definition of the table for innodb only and collation could also be considered as utf-8-general-ci  For example a table test is as following     create table test   (   col1 varchar(25),   col2 int,   col3 varchar(3),   col4 char(15),   col5 datetime   );     Now would require to know the total row size that can be accumulated in one row according to the types of columns in the table.  Found a some sort of similar solution in MSSQL but need its MySQL version    Any help is much appreciated.
122704
I have a MySQL table that contains information on invoices of a company. However, this company has two branches, and each of them has a unique invoicing sequence; a "Serie A" and "Serie B", so to speak. However, this is one single company and I do not want to create two invoice tables. Rather, I somehow want to have two different auto-increments for one table. I know this is technically not possible, but I guess this is a problem others have tackled before, so I'd like to know if there is a well-known 'solution' for this issue?   What I am doing right now is not using the primary key as invoice number (which would be ideal), but rather using a secondary column with the invoice id, which is incremented manually (well, using a PHP script, but it's still not automatic), by checking the latest invoice for that particular series.   This is my current setup:  CREATE TABLE `invoices` (   `id` mediumint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,   `invoicenumber` mediumint unsigned NOT NULL,   `branch` enum('A','B') NOT NULL,   `date` date NOT NULL,   `client` varchar(100) NOT NULL ) COMMENT='' ENGINE='InnoDB';   To check the lateset invoice, I run:  SELECT MAX(invoicenumber+1) AS new_invoice_number FROM invoices WHERE branch = 'A'