Sorry - I forgot

What would be the syntax to include additional column data representing
the "system" in a combined table ?

(av1 (AT) email (DOT) t-com.hr) writes:
SELECT system = 'system of tbl1', col1 ,col2, col2,
FROM tbl1
WHERE ...
UNION ALL
SELECT 'system2, col1, col2, col3,
FROM tbl2
WHERE ...
UNION ALL

If the WHERE clause is generally applicable, you can do:

SELECT system, co1l, col2, col3, ...
FROM (SELECT system = 'system of tbl1', col1 ,col2, col2,
FROM tbl1
UNION ALL
SELECT 'system2, col1, col2, col3,
FROM tbl2
UNION ALL
...) AS x
WHERE ...

Here I'm using a derived table, which logically a temp table within
the query, but not necessarily comnputed as such. That is the optimizer
may recast the computation order for better effeciency.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On 25 Apr 2006 03:41:43 -0700, av1 (AT) email (DOT) t-com.hr wrote:

Hi Andro,

And the answer still remains that this is VERY easy if you use just one
table instead of 30 tables for the 30 piping systems.

In my previous message, I asked you to post the DDL (CREATE TABLE
statements, including all constraints and indexes) for the tables that
you are currently using. If you had done that, I could now have given
you the CREATE TABLE statement for the single table to replace your 30
current tables.

Okay. So what exactly does this mean for the sample population for two
of the current tables that I posted? Is that particular combination of
vallues logically permitted? If not, what column(s) need to be changed
and why?

Yes.

--
Hugo Kornelis, SQL Server MVP

Hi,
I am able to retrive the uncommon columns from two similar tables, but the problem is i require a query, which can help me to find out which columns are added separately.
Another query which can say which columns are deleted.
And the last one can say which column has changed .

Plzz.. help me soon.

From http://www.developmentnow.com/g/95_2...lar-value-.htm

Posted via DevelopmentNow.com Groups
http://www.developmentnow.com

On Tue, 04 Dec 2007 14:11:47 GMT, Amit wrote:

Hi Amit,


In order to properly help you, you must give me a better understanding
of your problem. The best way to do that is by posting
* CREATE TABLE statements for the table(s) involved in the problem,
including all constraints, properties, and indexes (though you may omit
irrelevant columns);
* INSERT statements with some well-chosen rows of sample data to
illustrate the problem; and
* expected results.

See www.aspfaq.com/5006 for more information.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Amit (complete.amit (AT) gmail (DOT) com) writes:
It is not clear if you are looking for metadata information or data. But
I assumed that you want to compare the metadata for two tables.

This may get you started:

select a.tblname, a.colname, a.typename, a.max_length,
b.tblname, b.colname, b.typename, b.max_length
from (SELECT tblname = o.name, colname = c.name,
typename = t.name, c.max_length
FROM sys.objects o
JOIN sys.columns c ON o.object_id = c.object_id
JOIN sys.types t ON t.system_type_id = c.system_type_id
AND t.user_type_id <= 255
WHERE o.name = 'tbl1') AS a
full join
(SELECT tblname = o.name, colname = c.name,
typename = t.name, c.max_length
FROM sys.objects o
JOIN sys.columns c ON o.object_id = c.object_id
JOIN sys.types t ON t.system_type_id = c.system_type_id
AND t.user_type_id <= 255
WHERE o.name = 'tbl2') AS b
ON a.colname = b.colname
AND a.typename = b.typename
AND coalesce(a.max_length, 0) = coalesce(b.max_length, 0)
WHERE a.colname IS NULL OR b.colname IS NULL

This query is for SQL 2005. If you are on SQL 2000, the query is similar,
but you need to use sysobjects, syscolumns and systypes, and somewhat
different colunm names. You could look that up in Books Online.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx