I have multiple tables with with varying names for the same piece of data. For example, day vs business_day. I'd like to identify what column names exist for which table. I think this can be done using schema information, but I'm not familiar with it. For simplicity, the tables below are 3 separate tables with these column names. table_1 day    city  weather  table_2 business_day location  status  table_3 day   city  rain  Where day and business_day and city and location are the same piece of information, but unfortunately different naming conventions so I wouldn't be able to use the same selection criteria in a WHERE clause. I'd like to search the table information to see which tables have day or business_day and city or location. The other columns are not important to me in this example. How can I identify table information in this way?
24131
I'm looking for a precise piece of information in a database which I have no knowledge about. The database is on a separate machine, but I can log into it, and launch a psql command line, with administrator rights. It's a third-party product, and they are slow to answer questions. I know the data is inside that database, so I want to do a little bit of reverse-engineering. Given a table name, is it possible to get a list of the names of the columns in that table? For example, in SQL Server, it's possible to dump a table into a reusable CREATE statement, which textually lists all the columns the table is composed of.
88547
I can select all the distinct values in a column in the following ways:   SELECT DISTINCT column_name FROM table_name; SELECT column_name FROM table_name GROUP BY column_name;   But how do I get the row count from that query? Is a subquery required?
98905
How would you determine the column name (e.g. "AQ" or "BH") of the nth column in Excel?  Edit: A language-agnostic algorithm to determine this is the main goal here.
25380
I want to combine two structures with differing fields names.  For example, starting with:  A.field1 = 1; A.field2 = 'a';  B.field3 = 2; B.field4 = 'b';   I would like to have:  C.field1 = 1; C.field2 = 'a'; C.field3 = 2; C.field4 = 'b';   Is there a more efficient way than using "fieldnames" and a for loop?  EDIT: Let's assume that in the case of field name conflicts we give preference to A.
85612
In several database systems (e.g., PostgreSQL and MS SQL Server) there is a concept of schema. There are many questions out there asking what schema are and how they are used; here is a . Usually the answer boils down to "namespace" and "permissions". However, what remains unclear to me is why a schema has any advantage over just adding a new database. A different database allows one to have both separate namespaces and permission assignments.  Can someone provide an example in PostgreSQL where multiple schema in a single database provide an advantage over multiple databases without schema? From a purely organizational perspective they appear equivalent.
71359
Say, we have a query like this:  select a.*,b.* from  a join b on a.col1=b.col1 and len(a.col1)=10   Assuming the above query uses a Hash Join and has a residual, the probe key will be col1 and the residual will be len(a.col1)=10.  But while going through another example, I could see both the probe and the residual to be the same column. Below is an elaboration on what I am trying to say:  Query:  select * from T1 join T2 on T1.a = T2.a    Execution plan, with probe and residual highlighted:         Test data:    create table T1 (a int, b int, x char(200)) create table T2 (a int, b int, x char(200))  set nocount on declare @i int set @i = 0 while @i &lt; 1000   begin       insert T1 values (@i * 2, @i * 5, @i)     set @i = @i + 1   end  declare @i int set @i = 0 while @i &lt; 10000   begin     insert T2 values (@i * 3, @i * 7, @i)     set @i = @i + 1   end   Question:    How can a probe and a residual be the same column? Why can't SQL Server use the probe column only? Why does it have to use the same column as a residual to filter rows again?  References for test data:
10000
The accepted answer is factually incorrect; one can simply call  row.Table.Columns.Contains(name);   What should be done?
117172
A typical database table might look something like this:  date-------geography---clicks---cost---conversions ___________________________________________________ 1/1/2010---Kansas------56-------12-----1   When transposed, it looks like this.  date-----geography---metric_name---metric_value ____________________________________________ 1/1/2010-Kansas------clicks--------56 1/1/2010-Kansas------cost----------12 1/1/2010-Kansas------conversions---1   The transposed table works better for some use cases where the number of metrics being collected changes frequently.  Is there a standard name for a table structured like that in the transposed table?
20023
Is there are unified way of how to check for the existance of an INDEX for a given column irregardless of the actual SQL database system used?  For MySQL one could for instance check for the existance using SHOW CREATE TABLE mytable. In the result there would be something like this if column mycolumn has an index: KEY 'Index_1' ('mycolumn').  Is this indicator KEY unified among all SQL database systems?  Are there better ways to check for an index?
20479
I have a shapefile whose features I can filter by specifying a where clause in the query dialogue. F.x. I have a field named 'city_name' and by stating 'city_name = "London"' in the where clause only London is displayed. What I would like to do now is to fetch all values in 'city_name' from the attribute table.   Something like that:  select distinct city_name from [attribute table]   I found several tools in QGIS apparently dealing with SQL and I also had a quick look at the different Plugins. But I fail at connecting to a database or the table name ... what database or which table in that case?   In the end I want to export the result list and use it for further processing.  I am using QGIS 1.8.
68396
I want a way to establish which columns in a given database are joined via PK/FK relationships. I can return the PK/FK information for a given table via   SELECT *   FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS cu  WHERE EXISTS (     SELECT tc.*      FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc      WHERE tc.CONSTRAINT_CATALOG = 'MyDatabase'           AND tc.TABLE_NAME = 'MyTable'           /*AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'*/         AND tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME); GO   but for a PK returned from such a query how do I establish the associated FK (assuming there is one)?  I know you can also get the referenced tables via:  SELECT CONSTRAINT_NAME = name,         FOREIGN_SCHEMA = OBJECT_SCHEMA_NAME(parent_object_id),         FOREIGN_TABLE = OBJECT_NAME(parent_object_id),         REFERENCED_SCHEMA = OBJECT_SCHEMA_NAME(referenced_object_id),         REFERENCED_TABLE = OBJECT_NAME(referenced_object_id)  FROM sys.foreign_keys WHERE OBJECT_NAME(referenced_object_id) = 'MyTable'; GO   but I am struggling now to get the explicit column references.  I am creating a script generator for QlikView. To generate the script I need the constraints and the associated links. I need all of the constraint information for any given column (if any).  I want to construct a database class that holds all the information for a given database. This class structure database.table.column.constraints will then be used to get the matches between different columns on PK/FKs.  Clearly some columns will have FKs only and in this case I also want to retrieve the PK information of the corresponding key; some will have only PKs and then I want the reverse. Some of course can have both.
6305
I'm not used to the 'as' keyword and I thought it would be a nice idea to use it. So why do I get an 'unknown column' (eid) error? I use mysql...  select t1.index as eid, t1.col1 as col1, t1.col2 as col2,  t2.index, t2.col1, t2.col2, t3.index, t3.col1, t3.col2  from table1 t1  left join table2 t2 on t2.index=eid left join table3 t3 on t3.index=eid;   The full statement is too long to present here without boring everybody.  The column and table names are random.
74052
I have lots of schemas and tables. I want to generate create script of all of my tables. I am using below statement and it is working pretty well.  SELECT DBMS_METADATA.GET_DDL('TABLE','table_name','schema') FROM DUAL   But this statement also generates all primary and foreign key scripts that belong to table. So, is there any way to not include primary and foreign keys in create table scripts
98776
Is there a way to display the unique values in a column ONLY?  Example:   AAPL AAPL AAPL AAPL AKAM AMZN AMZN AMZN AMZN AMZN AMZN  Expected result:   AAPL AKAM AMZN  I have already done a sort for multiple factors, and the first match for each is the best match.  I'm not sure if this is a vlookup or something else.
39979
I have a simple problem when querying the SQL Server 2005 database. I have tables called Customer and Products (1->M). One customer has most 2 products. Instead of output as  CustomerName, ProductName ...  I like to output as   CustomerName, Product1Name, Product2Name ...  Could anybody help me?  Thanks!
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.
47138
I have a table in a Postgres DB where col1 and col2 are foreign keys referring to the same column. I want only unique combinations of (col1, col2), i.e. if (1,2) is entered then (2,1) should be rejected. How can I achieve that?   My table definition:  CREATE TABLE mytable (     id serial primary key,     col1 int NOT NULL,     col2 int NOT NULL,     unique (col1, col2) )