I am working on a huge database. I want to find all the tables in all the database which contains columns name say "xyz" (cann't say actual column name). Is there any query or other way to solve the my problem. TIA  FYI  DB : MySql
2549
I have 2-3 different column names that I want to look up in the entire DB and list out all tables which have those columns. Any easy script?
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.
21375
I want to write a select statement that with some fixed columns and dynamic columns. here Dynamic Columns names are the rows in another table.  I have tried this but getting error. Please tell me where my error is  set @sql=(Select subject from tbl_subjects where C_Id=22); SELECT      ta.rollno,     ta.StdNm,     tc.C_Name,     @sql FROM     tbl_cmarks tm,     tbl_admission ta,     tbl_classes tc WHERE     tm.Cid = tc.C_Id         AND ta.rollno = tm.rollno         AND tm.Cid = 22 and tm.Examid=9    Mysql showing:     Error Code: 1242. Subquery returns more than 1 row   Could any one tell me how to do this in mysql?
110579
I've been working with databases for the last few years and I'd like to think that I've gotten fairly competent with using them. However I was reading recently about Joel's  and I realised that even though I can write a query to get pretty much anything I want out of a database, I have no idea how the database actually interprets the query. Does anyone know of any good articles or books that explain how databases work internally?  Some specific things I'm interested in are:   What does a database actually do to find out what matches a select statement? How does a database interpret a join differently to a query with several "where key1 = key2" statements? How does the database store all its memory? How are indexes stored?
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"?
81999
What's the most efficient way to select the last n number of rows in a table using mySQL? The table contains millions of rows, and at any given time I don't know how large the table is (it is constantly growing). The table does have a column that is automatically incremented and used as a unique identifier for each row.
98355
With java.sql.ResultSet is there a way to get a column's name as a String by using the column's index? I had a look through the API doc but I can't find anything.
44222
I would like to search an entire MS SQL 2000 database for one value. This would be to aid development only. Keep that in mind when considering this question.   This will get all the table names and the column of the data type I'm looking for:  SELECT Columns.COLUMN_NAME, tables.TABLE_NAME FROM INFORMATION_SCHEMA.Columns as Columns  JOIN INFORMATION_SCHEMA.TABLES as tables  On Columns.TABLE_NAME = tables.TABLE_NAME WHERE Columns.DATA_TYPE = 'INT'     I was thinking something like this:  -- Vars DECLARE @COUNTER INT DECLARE @TOTAL INT DECLARE @TABLE CHAR(128) DECLARE @COLUMN CHAR(128) DECLARE @COLUMNTYPE CHAR(128) DECLARE @COLUMNVALUE INT  -- What we are looking for SET @COLUMNTYPE = 'INT' SET @COLUMNVALUE = 3 SET @COUNTER = 0  -- Find out how many possible columns exist SELECT @TOTAL = COUNT(*)   FROM INFORMATION_SCHEMA.Columns as Columns  JOIN INFORMATION_SCHEMA.TABLES as tables  On Columns.TABLE_NAME = tables.TABLE_NAME WHERE Columns.DATA_TYPE = @COLUMNTYPE PRINT CAST(@TOTAL AS CHAR) + 'possible columns' WHILE @COUNTER &lt; @TOTAL BEGIN     SET @COUNTER = @COUNTER +1     -- ADD MAGIC HERE END   Any ideas?  UPDATE I recently found this  that works quite well.
38962
Whenever I design a database, I always wonder if there is a best way of naming an item in my database. Quite often I ask myself the following questions:   Should table names be plural? Should column names be singular? Should I prefix tables or columns? Should I use any case in naming items?   Are there any recommended guidelines out there for naming items in a database?
124269
How do I select all the columns in a table that only contain NULL values for all the rows?  I'm using MS SQL Server 2005.  I'm trying to find out which columns are not used in the table so I can delete them.
88467
We have a question related to a DB query :   CREATE TABLE `Action` (   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,   `emp_Id` int(10) unsigned NOT NULL,   `name` varchar(60) NOT NULL,   `updated_action_at` datetime(3) DEFAULT NULL,   `created_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,   `notes` varchar(400) DEFAULT NULL,   PRIMARY KEY (`id`),    KEY `action_empId_fk` (`emp_Id`),    CONSTRAINT `action_empId_fk` FOREIGN KEY (`emp_Id`) REFERENCES `Employee` (`id`) ON DELETE CASCADE,  ) ENGINE=InnoDB AUTO_INCREMENT=502004 DEFAULT CHARSET=latin1   CREATE TABLE `ActionAssignedTo` (   `action_Id` int(10) unsigned DEFAULT NULL,   `emp_Id` int(10) unsigned DEFAULT NULL,   KEY `actionassignedto_emp_id_foreign` (`emp_Id`),   KEY `actionassignedto_action_id_foreign` (`action_Id`),   CONSTRAINT `ActionAssignedTo_ibfk_1` FOREIGN KEY (`emp_Id`) REFERENCES `Employee` (`id`) ON DELETE CASCADE,   CONSTRAINT `ActionAssignedTo_ibfk_2` FOREIGN KEY (`action_Id`) REFERENCES `Action` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1  CREATE TABLE `Employee` (   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,   `vendor_Id` int(10) unsigned DEFAULT NULL,   `name` varchar(40) NOT NULL,   `mobile_Number` varchar(15) NOT NULL,   `active` tinyint(1) DEFAULT '1',   `updated_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,   `created_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,   PRIMARY KEY (`id`),   KEY `employee_vendor_id_foreign` (`vendor_Id`),   CONSTRAINT `employee_vendor_id_foreign` FOREIGN KEY (`vendor_Id`) REFERENCES `Vendor` (`vendor_Id`) ) ENGINE=InnoDB AUTO_INCREMENT=511 DEFAULT CHARSET=latin1   We want to find the action created by employee with Action.empId = 4 along with the list of Assignee (an action can be assigned to multiple people)  We have the following query :   Select     notes,             Action.id,             AssigneeName.name,             ActionAssignedTo.emp_Id  from       Employee inner join Action  on         (Action.emp_Id = Employee.id  and        Action.emp_Id in (select Employee.id                               from Employee                               where Employee.vendor_Id = 1)) inner      join ActionAssignedTo  on         Action.id =  ActionAssignedTo.action_Id inner join Employee as AssigneeName  on         ActionAssignedTo.emp_Id =  AssigneeName.id where      Action.emp_Id = 4   Now, lets say if Action with id = 10 was assigned to 2 people : John (empId : 2) And Jack (empId : 5) --> the above query returns following results :  (columns from the query)  notes                       | Action.id | AssigneeName.name | ActionAssignedTo.emp_Id 1. Finish Designing Queries | 10        | John              | 2 2. Finish Designing Queries | 10        | Jack              | 5   If we observe the above results the first 2 columns get repeated for the Assignees for SAME ACTION ID  --> is there a way we can merge them something like   1. Finish Designing Queries |10         | { John : 2  , Jack  : 5}   Basically records being merged into one.
108573
I am getting below error while importing the dump,     ERROR 3105 (HY000) at line 82: The value specified for generated   column 'column_name' in table 'table_name' is not allowed.   When I describe the table with error, there are some generated columns in it.     option_type         | varchar(45)  | YES  | MUL | NULL    | VIRTUAL   GENERATED   What is the correct command to use when taking mysql dump for the database contains generated column?  MySQL server source and destination version:     mysqld Ver 5.7.26 for Linux on x86_64 (MySQL Community Server (GPL))   MariaDB mysqldump CLI version:     mysqldump Ver 10.17 Distrib 10.3.12-MariaDB, for Linux (x86_64)
114133
I'm running a project on a Postgres database and need to retrieve the comments on columns within the DB to be used as table headings and such. I have seen that there are a couple of built in functions ( and ) but i haven't been able to find examples on how to use them and playing around with them has proved pretty futile.  So I was wondering if any has been able to do this before and if so, how?
3565
After reading a couple of answers and comments on some SQL questions here, and also hearing that a friend of mine works at a place which has a policy which bans them, I'm wondering if there's anything wrong with using backticks around field names in MySQL.  That is:  SELECT `id`, `name`, `anotherfield` ... -- vs -- SELECT id, name, anotherfield ...
17694
I can read the MySQL documentation and it's pretty clear. But, how does one decide which character set to use? On what data does collation have an effect?  I'm asking for an explanation of the two and how to choose them.
116869
I am trying to learn PostgreSQL administration and have started learning how to use the psql command line tool.  When I log in with psql --username=postgres, how do I list all databases and tables?  I have tried \d, d and dS+ but nothing is listed. I have created two databases and a few tables with pgAdmin III, so I know they should be listed.
46559
One of my columns is called from. I can't change the name because I didn't make it. Am I allowed to do something like SELECT from FROM TableName or is there a special syntax to avoid the SQL Server being confused?