Finding Missing Records

I have 18 tables that are all related by the primary key. When I join all
the fields together for reporting, it only shows the records that have all
the data filled in. How can I find which tables don't have a record?
Thanks,
DrewLEFT JOIN
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:%23O17e%233OFHA.2144@.TK2MSFTNGP09.phx.gbl...
>I have 18 tables that are all related by the primary key. When I join all
>the fields together for reporting, it only shows the records that have all
>the data filled in. How can I find which tables don't have a record?
> Thanks,
> Drew
>|||Didn't think it would be that easy! Thought I would have to use an EXIST
query for this, and couldn't figure out how to incorporate all the tables
into 1 query...
Thanks,
Drew
"Michael C#" <howsa@.boutdat.com> wrote in message
news:%23EbjnA4OFHA.2680@.TK2MSFTNGP09.phx.gbl...
> LEFT JOIN
> "Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
> news:%23O17e%233OFHA.2144@.TK2MSFTNGP09.phx.gbl...
>|||SELECT *
FROM Table1
LEFT JOIN Table2
WHERE Table2.Field1 IS NULL
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:uRZzCL4OFHA.3072@.TK2MSFTNGP09.phx.gbl...
> Didn't think it would be that easy! Thought I would have to use an EXIST
> query for this, and couldn't figure out how to incorporate all the tables
> into 1 query...
> Thanks,
> Drew
> "Michael C#" <howsa@.boutdat.com> wrote in message
> news:%23EbjnA4OFHA.2680@.TK2MSFTNGP09.phx.gbl...
>|||OOPS, left out the ON clause:
SELECT *
FROM Table1
LEFT JOIN Table2
ON Table1.Column1 = Table2.Column1
WHERE Table2.Column1 IS NULL
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:uRZzCL4OFHA.3072@.TK2MSFTNGP09.phx.gbl...
> Didn't think it would be that easy! Thought I would have to use an EXIST
> query for this, and couldn't figure out how to incorporate all the tables
> into 1 query...
> Thanks,
> Drew
> "Michael C#" <howsa@.boutdat.com> wrote in message
> news:%23EbjnA4OFHA.2680@.TK2MSFTNGP09.phx.gbl...
>|||>> When I join all the fields together for reporting, it only shows the
Depending on your requirements, you can use OUTER JOINs or correlated
subqueries to retrieve all rows in one table which does not have a matching
value for a joining column in another table.
Anith

finding foreign key info

Hi,
Is there a way to find out if a column on a table is a foreign key, what
table it references and what column on that table it is referencing. All of
my foreign keys are a single column, which should make things easier.
Any help would be greatly appreciated.
Regards,
NedYou can get this from a couple of INFORMATION_SCHEMA views, e.g.
select
pk_ccu.table_name as PK_Table,
pk_ccu.column_name as PK_Column,
fk_ccu.table_name as FK_Table,
fk_ccu.column_name as FK_Column
from
information_schema.constraint_column_usage pk_ccu
join information_schema.referential_constraints rc on
pk_ccu.constraint_name=rc.unique_constraint_name
join information_schema.constraint_column_usage fk_ccu on
rc.constraint_name=fk_ccu.constraint_name
where
fk_ccu.table_Name='table_in_question'
and fk_ccu.column_name='column_in_question'
Ned wrote:
> Hi,
> Is there a way to find out if a column on a table is a foreign key, what
> table it references and what column on that table it is referencing. All
of
> my foreign keys are a single column, which should make things easier.
> Any help would be greatly appreciated.
> Regards,
> Ned
>|||Thanks Trey,
Worked like a charm.
Regards,
Ned
"Trey Walpole" <treyNOpole@.comSPAMcast.net> wrote in message
news:exXjWn0rFHA.460@.TK2MSFTNGP15.phx.gbl...
> You can get this from a couple of INFORMATION_SCHEMA views, e.g.
> select
> pk_ccu.table_name as PK_Table,
> pk_ccu.column_name as PK_Column,
> fk_ccu.table_name as FK_Table,
> fk_ccu.column_name as FK_Column
> from
> information_schema.constraint_column_usage pk_ccu
> join information_schema.referential_constraints rc on
> pk_ccu.constraint_name=rc.unique_constraint_name
> join information_schema.constraint_column_usage fk_ccu on
> rc.constraint_name=fk_ccu.constraint_name
> where
> fk_ccu.table_Name='table_in_question'
> and fk_ccu.column_name='column_in_question'
>
> Ned wrote:

finding first record ... !?

I have a table as follows:

- user_id (key)
- user_email_address (text)
- user_request_date (text)
- user_sent_date (time/date)

An automated macro will run every 5 minutes and needs to run a SQL UPDATE
that:

- finds the FIRST (ONE ROW ONLY) (youngest) record where
- user_request_date is the most recent
- AND user_sent_date is NULL (i.e. blank)

- THEN UPDATES the user_sent_date for THAT record to NOW() (i.e. the
current system date/time)

For example:

- record 33 - request_date 01-JUL-03 14:55:03 - user_sent_date = NULL
- record 34 - request_date 01-JUL-03 15:03:46 - user_sent_date = NULL
- record 35 - request_date 01-JUL-03 16:08:08 - user_sent_date = NULL
- record 36 - request_date 01-JUL-03 16:57:31 - user_sent_date = NULL
- record 37 - request_date 01-JUL-03 17:11:41 - user_sent_date = NULL

SO - the above NEEDED SQL UPDATE would update ONLY row 33 (the FIRST ROW)

- update record 33 - with NOW() time and date
THEN record 34, then 35, then 36, then 37
ONE ROW at a time per each SQL UPDATE

Any ideas!?
Thanks!
Richard

richard @. rcbuchanan . comRichard,

Try something like

update Richard set
user_sent_date = getdate()
where user_id = (
select top 1 user_id from Richard R
where R.user_sent_date is null
order by user_request_date
)

Your narrative and your example don't agree on whether
you want the most recent (youngest), or least recent (oldest)
user_request_date, so you may need to add DESC to the
order by clause.

Steve Kass
Drew University

Richard C Buchanan wrote:

>I have a table as follows:
>- user_id (key)
>- user_email_address (text)
>- user_request_date (text)
>- user_sent_date (time/date)
>An automated macro will run every 5 minutes and needs to run a SQL UPDATE
>that:
>- finds the FIRST (ONE ROW ONLY) (youngest) record where
> - user_request_date is the most recent
> - AND user_sent_date is NULL (i.e. blank)
> - THEN UPDATES the user_sent_date for THAT record to NOW() (i.e. the
>current system date/time)
>
>For example:
>- record 33 - request_date 01-JUL-03 14:55:03 - user_sent_date = NULL
>- record 34 - request_date 01-JUL-03 15:03:46 - user_sent_date = NULL
>- record 35 - request_date 01-JUL-03 16:08:08 - user_sent_date = NULL
>- record 36 - request_date 01-JUL-03 16:57:31 - user_sent_date = NULL
>- record 37 - request_date 01-JUL-03 17:11:41 - user_sent_date = NULL
>SO - the above NEEDED SQL UPDATE would update ONLY row 33 (the FIRST ROW)
>- update record 33 - with NOW() time and date
>THEN record 34, then 35, then 36, then 37
>ONE ROW at a time per each SQL UPDATE
>
>Any ideas!?
>Thanks!
>Richard
>richard @. rcbuchanan . com
>

Finding duplicates in the table

source table AAA
-------

pvn_acct_id
acct_id

no primary key, there can be duplicates, for ex:

pvn_acct_id acct_id
======= =========
111 111
111 111
111 111

222 333
222 334
222 335

333 222
334 222
335 222

from first set, using the below query to extract data, it will result in 111,111, for 2nd set, 222, 335, for third set, 335, 222.

select distinct a.pvn_acct_id, a.acct_id
from owner.AAA where a.acct_id = (select max(b.acct_id) from tstg.t_acct_num_hist b
where a.pvn_acct_id = b.pvn_acct_id)
and a.pvn_acct_id = (select max(c.pvn_acct_id) from tstg.t_acct_num_hist c
where a.acct_id = c.acct_id)

Above query is directly populating the target table BBB

What I want to find out is a sample of data set where i can find different instances of acct_id where pvn_Acct_id is same, and vice versa.
For ex:

I want to know where pvn_acct_id is 222, acct_id is 333,334,335, not just acct_id=335.

pls help.Did you try this:

select distinct a.pvn_acct_id, a.acct_id
from owner.AAA a

;)|||select pvn_Acct_id from owner.AAA aaa where acct_id in (select acct_id from owner.BBB bbb
where aaa.pvn_acct_id != bbb.pvn_acct_id) order by pvn_Acct_id

select Acct_id from owner.AAA aaa where pvn_acct_id in (select pvn_acct_id from owner.BBB bbb
where aaa.acct_id != bbb.acct_id) order by Acct_id

now this query works. got it with trial and error. have fun testing.

Finding Correct Primary Key

Hello,
I could not reach a conclusion on this. Maybe someone helps me to understand
better.
I have a consolidated text extract from other systems and want to upload
the data to an SQL table.
Below is the fields in extract.
OrderNo
LineNumber
SellingCompany
PurcCtry
FinalizeDt
ItemNo
CustomerID
SalesRep
Quantity
Sales_USD
At first analysis, I thought OrderNo and LineNumber are the PK of the table
and designed the table.
When I receive the data I realized that since the data is coming from
different sources. Some of them has duplicate Orderno LineNumber
combination.
Now my data has two different of type of data one has unique key of Orderno
LineNumber combination. On the other hand second part of data needs OrderNo
LineNumber ,FinalizeDt,ItemNo and CustomerID for a unique key.
Question is : Should I keep these data in two different table, or should I
keep them in a single table by adding more columns on PK ?
What is the affect of defining larger PK even it is not needed for some of
the data?
My table has data has 1.5 million record and increasing 75000 each month.
70 % of data has Unique Orderno Line Number
Data is used by sql server reporting services, I have also some plans to use
analysis server in the future.
Thanks in advance.
erdal,You can use up to 16 columns for a composite primary key, however, make sure
you only use as many as you actually need, based on your data.
If you expect frequent and large increases in table size, make the primary
key nonclustered, and find a more appropriate candidate for the clustered
index. An ideal candidate for this kind of situation is a column with
non-nullable unique values, that increase for each new inserted row (e.g. an
identity column).
Using several tables is an option, yet it may complicate querying for the
reporting purposes.
ML|||> Question is : Should I keep these data in two different table, or should I
> keep them in a single table by adding more columns on PK ?
It seems to me that the data from the 2 sources represents different
entities so you should store these in different tables.

> On the other hand second part of data needs OrderNo
> LineNumber ,FinalizeDt,ItemNo and CustomerID for a unique key.
You need to do some analysis to determine the appropriate PK. What does the
data from this data source actually represent (e.g. order details history or
different order entry systems)? Simply adding columns until you come up
with a unique combination isn't the right approach. Data may be unique
based on your current data sample but not necessarily in the future.
Perhaps the data is bad and you are only masking an underlying problem.
Maybe some data scrubbing is needed before import. These are all issues
that need to be addressed in order to make intelligent schema design
decisions.
Hope this helps.
Dan Guzman
SQL Server MVP
"Erdal Akbulut" <erdalim21@.yahoo.com> wrote in message
news:ukME7Fv1FHA.2348@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I could not reach a conclusion on this. Maybe someone helps me to
> understand
> better.
> I have a consolidated text extract from other systems and want to upload
> the data to an SQL table.
> Below is the fields in extract.
> OrderNo
> LineNumber
> SellingCompany
> PurcCtry
> FinalizeDt
> ItemNo
> CustomerID
> SalesRep
> Quantity
> Sales_USD
>
> At first analysis, I thought OrderNo and LineNumber are the PK of the
> table
> and designed the table.
> When I receive the data I realized that since the data is coming from
> different sources. Some of them has duplicate Orderno LineNumber
> combination.
> Now my data has two different of type of data one has unique key of
> Orderno
> LineNumber combination. On the other hand second part of data needs
> OrderNo
> LineNumber ,FinalizeDt,ItemNo and CustomerID for a unique key.
> Question is : Should I keep these data in two different table, or should I
> keep them in a single table by adding more columns on PK ?
> What is the affect of defining larger PK even it is not needed for some of
> the data?
> My table has data has 1.5 million record and increasing 75000 each month.
> 70 % of data has Unique Orderno Line Number
> Data is used by sql server reporting services, I have also some plans to
> use
> analysis server in the future.
> Thanks in advance.
>
> erdal,
>|||On Sat, 22 Oct 2005 13:15:06 +0300, Erdal Akbulut wrote:

>Hello,
>I could not reach a conclusion on this. Maybe someone helps me to understan
d
>better.
>I have a consolidated text extract from other systems and want to upload
>the data to an SQL table.
>Below is the fields in extract.
>OrderNo
>LineNumber
>SellingCompany
>PurcCtry
>FinalizeDt
>ItemNo
>CustomerID
>SalesRep
>Quantity
>Sales_USD
>
>At first analysis, I thought OrderNo and LineNumber are the PK of the table
>and designed the table.
>When I receive the data I realized that since the data is coming from
>different sources. Some of them has duplicate Orderno LineNumber
>combination.
(snip)
>Question is : Should I keep these data in two different table, or should I
>keep them in a single table by adding more columns on PK ?
Hi Erdal,
It seems to me that you're approaching this from the wrong way. You
should start with how the data is supposed to be related in the
business.
Not knowing your business and making some wild assumptions about the
meaning of some columns names, my first guess is that (OrderNo,
LineNumber) might be a good candidate key for this table - but that's
noway sure! If your business assigns one and only one salesrep toe ach
customer, than SalesRep should be in the Customers table and can be
removed from this table. Similar, if each customer makes all it's
purchases in one country, then PurcCtry should be moved to the Customers
table as well. There might be more similar dependencies between the
columns that only someone with knowledge of the business model of your
company can find.
WRT the duplicated order numbers: if these sources each have their own
system for assigning order numbers (as might be the case after the
merger of two companies, or in a company with independent branches) and
duplicates are to be expected, then you should add a column to indicate
the source (not the data source, but the source of the order - e.g. the
name or ID of the branch where the order comes from), and add that
column to the primary key. OTOH, if the order numbers really should be
unique in the complete collection of data, then you'll have to hunt down
the reason why duplicates were entered in the first place, fix the
problem, clean up the erronous data before proceeding with this
consolidation project.
(previously snipped text:)
>Now my data has two different of type of data one has unique key of Orderno
>LineNumber combination. On the other hand second part of data needs OrderNo
>LineNumber ,FinalizeDt,ItemNo and CustomerID for a unique key.
I'm not sure if I understand you right. Maybe you're just trying to find
a set of columns that would technically (but not logically) qualify as
primary key - in that case see above: either add a column or fix the
real problem.
But if you're saying that the business model logically implies that the
data from one source has (OrderNo, LineNumber, FinalizeDt, ItemNo,
CustomerID) as the "real" primary key, then
a) You have a very unusual business model (but of course, mine is not to
judge), and
b) You can't combine the data from the two sources into one table, since
they have a different logical structure - it would be like trying to
combine ledger entries and personnel records into one table.

>What is the affect of defining larger PK even it is not needed for some of
>the data?
You'll get corrupted data in your table. Would you consider defining the
primary key on your Personnel table as (EmployeeID, EmpName)? I'd hope
not - since it's not the question IF you'll end up with duplicated data,
but WHEN you'll get it. And the answer is "sooner than you think".
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||> When I receive the data I realized that since the data is coming from
> different sources. Some of them has duplicate Orderno LineNumber
> combination.
>
It sounds as if what you need is something like SourceID, OrderNo,
LineNumber. If SourceID is not included in the extract files, then populate
it during the transform/load step.
.|||Thanks for the answers,
Let me try to clarify the issue.
we have 7 companies running on the same application, and 2 more using
different systems. All applications run on a mainframe environment and the
data is also consolidated in mainframe.
For the first 7 companies. OrderNo and Orderline are the candidates. No
problem at this part.
For the other 2 companies, we do not have the data in orderline level. The
data is summed by item, customer etc by month, and they have dummy ordenos
as 999999 and line no as 001
Currently I have two tables. one for the first 7 companies having Orderno
and Linenumber as PK. and another table for the other 2 companies having PK
with multiple columns.
I was thinking to merge the data in one table and define a larger PK having
PK columns from first and second table.
Seems it is not recommended in this case.
regards,
"Erdal Akbulut" <erdalim21@.yahoo.com> wrote in message
news:ukME7Fv1FHA.2348@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I could not reach a conclusion on this. Maybe someone helps me to
understand
> better.
> I have a consolidated text extract from other systems and want to upload
> the data to an SQL table.
> Below is the fields in extract.
> OrderNo
> LineNumber
> SellingCompany
> PurcCtry
> FinalizeDt
> ItemNo
> CustomerID
> SalesRep
> Quantity
> Sales_USD
>
> At first analysis, I thought OrderNo and LineNumber are the PK of the
table
> and designed the table.
> When I receive the data I realized that since the data is coming from
> different sources. Some of them has duplicate Orderno LineNumber
> combination.
> Now my data has two different of type of data one has unique key of
Orderno
> LineNumber combination. On the other hand second part of data needs
OrderNo
> LineNumber ,FinalizeDt,ItemNo and CustomerID for a unique key.
> Question is : Should I keep these data in two different table, or should I
> keep them in a single table by adding more columns on PK ?
> What is the affect of defining larger PK even it is not needed for some of
> the data?
> My table has data has 1.5 million record and increasing 75000 each month.
> 70 % of data has Unique Orderno Line Number
> Data is used by sql server reporting services, I have also some plans to
use
> analysis server in the future.
> Thanks in advance.
>
> erdal,
>|||On Mon, 24 Oct 2005 18:30:15 +0300, Erdal Akbulut wrote:

>Thanks for the answers,
>Let me try to clarify the issue.
>we have 7 companies running on the same application, and 2 more using
>different systems. All applications run on a mainframe environment and the
>data is also consolidated in mainframe.
>For the first 7 companies. OrderNo and Orderline are the candidates. No
>problem at this part.
>For the other 2 companies, we do not have the data in orderline level. The
>data is summed by item, customer etc by month, and they have dummy ordenos
>as 999999 and line no as 001
>
>Currently I have two tables. one for the first 7 companies having Orderno
>and Linenumber as PK. and another table for the other 2 companies having P
K
>with multiple columns.
>I was thinking to merge the data in one table and define a larger PK having
>PK columns from first and second table.
>Seems it is not recommended in this case.
Hi Erdal,
Seems to me that you have two options to chose from:
A) Use two tables. One keyed on (OrderNo, LineNumber) to store the data
from the first 7 companies, and the second one with a smaller amount of
columns, and keyed on (Item, Customer, etc, Month) for the last 2
companies. Don't introducce fake order and line numbers in the last
table, they'll only confuse people.
B) Use a staging table to accept the data from the first 7 companies.
Aggregate this data to the same level as the data you receive from the
other 2 companies. Then combine the data from the latter 2 companies
with the aggregated data from the first 7 companies into one table.
Which of these solutions suits your problem best is for you to decide,
since I don't know what you're planning to do with the data, once it's
in the database.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Finding Correct Primary Key

Hello,
I could not reach a conclusion on this. Maybe someone helps me to understand
better.
I have a consolidated text extract from other systems and want to upload
the data to an SQL table.
Below is the fields in extract.
OrderNo
LineNumber
SellingCompany
PurcCtry
FinalizeDt
ItemNo
CustomerID
SalesRep
Quantity
Sales_USD
At first analysis, I thought OrderNo and LineNumber are the PK of the table
and designed the table.
When I receive the data I realized that since the data is coming from
different sources. Some of them has duplicate Orderno LineNumber
combination.
Now my data has two different of type of data one has unique key of Orderno
LineNumber combination. On the other hand second part of data needs OrderNo
LineNumber ,FinalizeDt,ItemNo and CustomerID for a unique key.
Question is : Should I keep these data in two different table, or should I
keep them in a single table by adding more columns on PK ?
What is the affect of defining larger PK even it is not needed for some of
the data?
My table has data has 1.5 million record and increasing 75000 each month.
70 % of data has Unique Orderno Line Number
Data is used by sql server reporting services, I have also some plans to use
analysis server in the future.
Thanks in advance.
erdal,Hello Erdal,
I usually add a source system key when combining data sets from
different sources. If you add a source system key to Orderno,
LineNumber combination does that give you a unique key?
If the answer is yes then you can have a common table. I normally have
different staging tables and one common table for reporting and star
schema generation.
Hope this helps,
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/|||Hello Erdal,
I just re-read my post and forgot to mention that I hard code or
generate the source system key. It helps me trace the source of records
in the systems I build.
Hope this is a bit clearer now,
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/

Finding Correct Primary Key

Hello,
I could not reach a conclusion on this. Maybe someone helps me to understand
better.
I have a consolidated text extract from other systems and want to upload
the data to an SQL table.
Below is the fields in extract.
OrderNo
LineNumber
SellingCompany
PurcCtry
FinalizeDt
ItemNo
CustomerID
SalesRep
Quantity
Sales_USD
At first analysis, I thought OrderNo and LineNumber are the PK of the table
and designed the table.
When I receive the data I realized that since the data is coming from
different sources. Some of them has duplicate Orderno LineNumber
combination.
Now my data has two different of type of data one has unique key of Orderno
LineNumber combination. On the other hand second part of data needs OrderNo
LineNumber ,FinalizeDt,ItemNo and CustomerID for a unique key.
Question is : Should I keep these data in two different table, or should I
keep them in a single table by adding more columns on PK ?
What is the affect of defining larger PK even it is not needed for some of
the data?
My table has data has 1.5 million record and increasing 75000 each month.
70 % of data has Unique Orderno Line Number
Data is used by sql server reporting services, I have also some plans to use
analysis server in the future.
Thanks in advance.
erdal,
Hello Erdal,
I usually add a source system key when combining data sets from
different sources. If you add a source system key to Orderno,
LineNumber combination does that give you a unique key?
If the answer is yes then you can have a common table. I normally have
different staging tables and one common table for reporting and star
schema generation.
Hope this helps,
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/
|||Hello Erdal,
I just re-read my post and forgot to mention that I hard code or
generate the source system key. It helps me trace the source of records
in the systems I build.
Hope this is a bit clearer now,
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/
sql

Find values from record that had a max value in a group by

I am looking for the MS SQL variant of the oracle 'KEEP DENSE_RANK' construction.

If you have a table like this:

key date item 1 20070101 Apple 1 20070202 Banana 1 20070303 Cherry

The query for retrieving the item from the last date should pick the Cherry, but what would be the most efficient way to do this (without using multiple queries, in oracle I don't need a sub query!).

You would like to do something like:

Select Key, max(Date) as LastDate, PickLastValue(Cherry) on Date desc

from Table

group by Key.

any suggestions?

hans

You can do the same funciton in SQL Server 2005..

Code Snippet

Create Table #data (

[key] Varchar(100) ,

[date] Varchar(100) ,

[item] Varchar(100)

);

Insert Into #data Values('1','20070101','Apple');

Insert Into #data Values('1','20070202','Banana');

Insert Into #data Values('1','20070303','Cherry');

Insert Into #data Values('2','20070101','Apple');

Insert Into #data Values('2','20070202','Banana');

Code Snippet

--Over all

;With CTE

as

(

Select [key],[date],[item],DENSE_RANK() Over (Order By [date] Desc) rank From #Data

)

Select [key],[date],[item] from CTE Where rank=1

Code Snippet

--For each Key

;With CTE

as

(

Select [key],[date],[item],DENSE_RANK() Over (Partition By [Key] Order By [date] Desc) rank From #Data

)

Select [key],[date],[item],rank from CTE Where rank=1

|||

If you use sql server 2000 then you have to use the subquery...

Code Snippet

--For Overall

Select * from #data Where [date] in (Select max(date) From #data)

--For Each Key

Select * from #data Data

Join (Select [Key], max(date) date From #data Group By [Key]) Sub On

Sub.[Key]=Data.[Key] and Sub.date=Data.date

|||

This will do!

thanx

hans

find unique identifier through multiple tables

>In one table there is GUID as a primary key and another GUID as
foreign key. But there is no relation defined to what table this
foreign key refers [I think you mean that you have no REFERENCES
clause?]. I suppose the link [sic: reference] between these two tables
is purely managed programmatically. <<

I worked for a company that wrote crap like that when I first moved to
Austin. They are still in business, but down from ~1200 employees to
~200 and are now outsourcing their Indian operations to China as they
shrink.

This kind of thing falls apart in about a year. You get orphans all
over the schema (we choked a hard disk for a relatively small custom
sales commission package). There is no ways to validate or verify a
GUID; you need a proper key instead of a bad attempt at pointer
chains. The "pseudo Foreign Key" will be referenced by Cabbages and
Kings simply because all GUIDs can be compared (like pointers or other
exposed physical locators).

You really need to throw this thing out. But if you cannot, then
update your resume.--CELKO-- wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

>>In one table there is GUID as a primary key and another GUID as

foreign key. But there is no relation defined to what table this
foreign key refers [I think you mean that you have no REFERENCES
clause?]. I suppose the link [sic: reference] between these two tables
is purely managed programmatically. <<
>
I worked for a company that wrote crap like that when I first moved to
Austin. They are still in business, but down from ~1200 employees to
~200 and are now outsourcing their Indian operations to China as they
shrink.
>
This kind of thing falls apart in about a year. You get orphans all
over the schema (we choked a hard disk for a relatively small custom
sales commission package). There is no ways to validate or verify a
GUID; you need a proper key instead of a bad attempt at pointer
chains. The "pseudo Foreign Key" will be referenced by Cabbages and
Kings simply because all GUIDs can be compared (like pointers or other
exposed physical locators).

He can add REFERENCES clauses (once he figures out which ones should
be added, and cleans up any existing exceptions), surely?|||>He can add REFERENCES clauses (once he figures out which ones should be added, and cleans up any existing exceptions), surely? <<

Then he will have mimicked a 1970's pointer chain DB in SQL instead of
making this a properly designed RDBMS.

For example, if I use an VIN for an automobile, I can verify the VIN
by going to the automobile, the DMV, insurance company, etc. But if I
use a GUID (or any other hardware generated value), I have no trusted
external source for verification.

I do not have a good way to validate it, in fact. The magical
universal GUID might be used for an automobile, a squid or Britney
Spears!

Find the Forign keys's primary key table

I hava a table, called Items which has 2 foriegn keys. i want to find the
forign keys's primary key table through sql query.
Please anybody help me.
Regards,
R.SathiamoorthyIf you're using SQL Server 2005 you can piece together the info from
the sys.foreign_keys and sys.foreign_key_columns views.
object_id in sys.fk is the object_id of the constraint
parent_object_id in sys.fk is the table that the constraint is created
in
constraint_object_id in sys.fkc relates to object_id in sys.fk
parent_object_id in sys.fkc is the table that the constraint is
created in
parent_column_id is the column on which the constraint is created
referenced_object_id is the table the constraint is referencing
referenced_column_id is the column in the referenced table that is
used in the constraint.
The joining to sys.tables and sys.columns will get you the info you
require.
Cheers,
Jason Lepack
On Sep 7, 6:05 am, "Sathiamoorthy" <some...@.microsoft.com> wrote:
> I hava a table, called Items which has 2 foriegn keys. i want to find the
> forign keys's primary key table through sql query.
> Please anybody help me.
> Regards,
> R.Sathiamoorthy|||I though this was an intresting question, so I worte a little code. Sorry I
didn't have time to finish it, gotta go to work.
-- Create and use a NEW, empty database.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id =OBJECT_ID(N'[dbo].[tab2]') AND type in (N'U'))
DROP TABLE [dbo].[tab2]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id =OBJECT_ID(N'[dbo].[tab1]') AND type in (N'U'))
DROP TABLE [dbo].[tab1]
CREATE TABLE tab1 (col1 INT, CONSTRAINT PK_tab1 PRIMARY KEY(col1))
CREATE TABLE tab2 (
col1 INT,
col2 INT,
CONSTRAINT PK_tab2 PRIMARY KEY(col1, col2),
CONSTRAINT FK_tab2_tab1 FOREIGN KEY (col1) REFERENCES tab1(col1)
)
SELECT so1.[name], so1.id, so2.name, so2.id, so2.xtype, so2.type, *
FROM sysobjects so1
INNER JOIN sysobjects so2 ON so2.parent_obj = so1.id
WHERE so1.[name] IN ('tab1', 'tab2')
SELECT sc.*, 'XXX' xxx, so.* FROM sysconstraints sc INNER JOIN sysobjects so
ON so.id = sc.id
DECLARE @.HasFK SYSNAME
SELECT @.HasFK = name
from sysobjects
where id in (
SELECT fkeyid
FROM sysreferences sr
WHERE constid IN (
SELECT so2.id
FROM sysobjects so1
INNER JOIN sysobjects so2 ON so2.parent_obj = so1.id
)
)
DECLARE @.References SYSNAME
SELECT @.References = name
from sysobjects
where id in (
SELECT rkeyid
FROM sysreferences sr
WHERE constid IN (
SELECT so2.id
FROM sysobjects so1
INNER JOIN sysobjects so2 ON so2.parent_obj = so1.id
)
)
SELECT @.HasFK + ' has FK to ' + @.References
"Sathiamoorthy" <someone@.microsoft.com> wrote in message
news:uhuElgT8HHA.4476@.TK2MSFTNGP06.phx.gbl...
>I hava a table, called Items which has 2 foriegn keys. i want to find the
> forign keys's primary key table through sql query.
> Please anybody help me.
> Regards,
> R.Sathiamoorthy
>|||See my post 'sqlschema'
"Jay" <spam@.nospam.org> wrote in message
news:%23gwFK8V8HHA.1208@.TK2MSFTNGP03.phx.gbl...
>I though this was an intresting question, so I worte a little code. Sorry I
>didn't have time to finish it, gotta go to work.
> -- Create and use a NEW, empty database.
> IF EXISTS (SELECT * FROM sys.objects WHERE object_id => OBJECT_ID(N'[dbo].[tab2]') AND type in (N'U'))
> DROP TABLE [dbo].[tab2]
> IF EXISTS (SELECT * FROM sys.objects WHERE object_id => OBJECT_ID(N'[dbo].[tab1]') AND type in (N'U'))
> DROP TABLE [dbo].[tab1]
> CREATE TABLE tab1 (col1 INT, CONSTRAINT PK_tab1 PRIMARY KEY(col1))
> CREATE TABLE tab2 (
> col1 INT,
> col2 INT,
> CONSTRAINT PK_tab2 PRIMARY KEY(col1, col2),
> CONSTRAINT FK_tab2_tab1 FOREIGN KEY (col1) REFERENCES tab1(col1)
> )
> SELECT so1.[name], so1.id, so2.name, so2.id, so2.xtype, so2.type, *
> FROM sysobjects so1
> INNER JOIN sysobjects so2 ON so2.parent_obj = so1.id
> WHERE so1.[name] IN ('tab1', 'tab2')
> SELECT sc.*, 'XXX' xxx, so.* FROM sysconstraints sc INNER JOIN sysobjects
> so ON so.id = sc.id
> DECLARE @.HasFK SYSNAME
> SELECT @.HasFK = name
> from sysobjects
> where id in (
> SELECT fkeyid
> FROM sysreferences sr
> WHERE constid IN (
> SELECT so2.id
> FROM sysobjects so1
> INNER JOIN sysobjects so2 ON so2.parent_obj = so1.id
> )
> )
> DECLARE @.References SYSNAME
> SELECT @.References = name
> from sysobjects
> where id in (
> SELECT rkeyid
> FROM sysreferences sr
> WHERE constid IN (
> SELECT so2.id
> FROM sysobjects so1
> INNER JOIN sysobjects so2 ON so2.parent_obj = so1.id
> )
> )
> SELECT @.HasFK + ' has FK to ' + @.References
>
> "Sathiamoorthy" <someone@.microsoft.com> wrote in message
> news:uhuElgT8HHA.4476@.TK2MSFTNGP06.phx.gbl...
>>I hava a table, called Items which has 2 foriegn keys. i want to find the
>> forign keys's primary key table through sql query.
>> Please anybody help me.
>> Regards,
>> R.Sathiamoorthy
>>
>

FIND THE FOREIGN KEY FOR A TABLE

Dear All,
I want to know, how to retrieve the foreign key for a table using T-SQL.
Regards,
SathiamoorthyHi
Look at Aaron's example
SELECT
FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN
(
SELECT
i1.TABLE_NAME, i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE PK.TABLE_NAME='Customers'
ORDER BY
1,2,3,4
"Sathiamoorthy" <someone@.microsoft.com> wrote in message
news:uIzkS2wKGHA.648@.TK2MSFTNGP14.phx.gbl...
> Dear All,
> I want to know, how to retrieve the foreign key for a table using T-SQL.
> Regards,
> Sathiamoorthy
>

Find tables w/o primary keys but w/ unique clustered indexes

Hi
I run SQL Server 2000 SP3 on Windows 2000 SP4.
I want to find out, which "user tables" in my DB don't have
a primary key but do have at least one unique index. I
couldn't find any documented way to do this straight away.
So I came up with the following query, which seems to work:
SELECT OBJECT_NAME(id)
FROM sysindexes
WHERE indid > 0 and indid < 255 and (status & 2048) = 0
and (status & 2) <> 0
Does this query seem to be correct and is there any better
(documented) way of doing what I'm trying to do?
--
Thanks,
OskHow about:
SELECT DISTINCT
table_name,
constraint_name
FROM information_schema.table_constraints c
WHERE constraint_type = 'UNIQUE'
AND NOT EXISTS
(SELECT *
FROM information_schema.table_constraints c1
WHERE C1.table_name = c.table_name
and c1.constraint_type = 'PRIMARY KEY')
AND INDEXPROPERTY(OBJECT_ID(c.table_name), c.constraint_name,
'IsClustered') = 1
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Osk" <anonymous@.discussions.microsoft.com> wrote in message
news:0c7701c5308e$d9b58b50$a401280a@.phx.gbl...
> Hi
> I run SQL Server 2000 SP3 on Windows 2000 SP4.
> I want to find out, which "user tables" in my DB don't have
> a primary key but do have at least one unique index. I
> couldn't find any documented way to do this straight away.
> So I came up with the following query, which seems to work:
> SELECT OBJECT_NAME(id)
> FROM sysindexes
> WHERE indid > 0 and indid < 255 and (status & 2048) = 0
> and (status & 2) <> 0
> Does this query seem to be correct and is there any better
> (documented) way of doing what I'm trying to do?
> --
> Thanks,
> Osk
>|||Try,
select distinct
object_name(a.[id])
from
sysindexes as a
left join
(
select
table_name
from
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where
constraint_type = 'PRIMARY KEY'
) as b
on object_name(a.[id]) = b.table_name
where
b.table_name is null
and indexproperty(a.[id], [name], 'IsUnique') = 1
and objectproperty(a.[id], 'IsMSShipped') = 0
go
AMB
"Osk" wrote:
> Hi
> I run SQL Server 2000 SP3 on Windows 2000 SP4.
> I want to find out, which "user tables" in my DB don't have
> a primary key but do have at least one unique index. I
> couldn't find any documented way to do this straight away.
> So I came up with the following query, which seems to work:
> SELECT OBJECT_NAME(id)
> FROM sysindexes
> WHERE indid > 0 and indid < 255 and (status & 2048) = 0
> and (status & 2) <> 0
> Does this query seem to be correct and is there any better
> (documented) way of doing what I'm trying to do?
> --
> Thanks,
> Osk
>|||Thanks!
Shame on me - IDEXPROPERTY was the way to go.
>--Original Message--
>Try,
>select distinct
> object_name(a.[id])
>from
> sysindexes as a
> left join
> (
> select
> table_name
> from
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS
> where
> constraint_type = 'PRIMARY KEY'
> ) as b
> on object_name(a.[id]) = b.table_name
>where
> b.table_name is null
> and indexproperty(a.[id], [name], 'IsUnique') = 1
> and objectproperty(a.[id], 'IsMSShipped') = 0
>go
>
>AMB
>"Osk" wrote:
>> Hi
>> I run SQL Server 2000 SP3 on Windows 2000 SP4.
>> I want to find out, which "user tables" in my DB don't have
>> a primary key but do have at least one unique index. I
>> couldn't find any documented way to do this straight away.
>> So I came up with the following query, which seems to work:
>> SELECT OBJECT_NAME(id)
>> FROM sysindexes
>> WHERE indid > 0 and indid < 255 and (status & 2048) = 0
>> and (status & 2) <> 0
>> Does this query seem to be correct and is there any better
>> (documented) way of doing what I'm trying to do?
>> --
>> Thanks,
>> Osk
>>
>.
>|||Thanks, Adam.
Still, I wasn't exactly looking for tables with UNIQUE
constraints but rather - with UNIQUE indexes.
>--Original Message--
>How about:
>SELECT DISTINCT
> table_name,
> constraint_name
>FROM information_schema.table_constraints c
>WHERE constraint_type = 'UNIQUE'
> AND NOT EXISTS
> (SELECT *
> FROM information_schema.table_constraints c1
> WHERE C1.table_name = c.table_name
> and c1.constraint_type = 'PRIMARY KEY')
> AND INDEXPROPERTY(OBJECT_ID(c.table_name), c.constraint_name,
>'IsClustered') = 1
>
>--
>Adam Machanic
>SQL Server MVP
>http://www.datamanipulation.net
>--
>
>"Osk" <anonymous@.discussions.microsoft.com> wrote in message
>news:0c7701c5308e$d9b58b50$a401280a@.phx.gbl...
>> Hi
>> I run SQL Server 2000 SP3 on Windows 2000 SP4.
>> I want to find out, which "user tables" in my DB don't have
>> a primary key but do have at least one unique index. I
>> couldn't find any documented way to do this straight away.
>> So I came up with the following query, which seems to work:
>> SELECT OBJECT_NAME(id)
>> FROM sysindexes
>> WHERE indid > 0 and indid < 255 and (status & 2048) = 0
>> and (status & 2) <> 0
>> Does this query seem to be correct and is there any better
>> (documented) way of doing what I'm trying to do?
>> --
>> Thanks,
>> Osk
>
>.
>|||Lookup the meta data function OBJECTPROPERTY and its various possible values
for the second argument in SQL Server Books Online. You can get the list
using:
SELECT name
FROM sysobjects
WHERE s1.xtype = 'U'
AND OBJECTPROPERTY( id, 'TableHasUniqueCnst' ) = 1
AND OBJECTPROPERTY( id, 'TableHasPrimaryKey' ) = 0 ;
Alternatively, you can also use INFORMATION_SCHEMA views instead of
accessing the system table directly.
--
Anith|||Yes, Anith, thanks!
Though initially I wasn't really interested in finding
tables with UNIQUE constraints but rather with UNIQUE
indexes, which is not the same, is it?
>--Original Message--
>Lookup the meta data function OBJECTPROPERTY and its
various possible values
>for the second argument in SQL Server Books Online. You
can get the list
>using:
>SELECT name
> FROM sysobjects
> WHERE s1.xtype = 'U'
> AND OBJECTPROPERTY( id, 'TableHasUniqueCnst' ) = 1
> AND OBJECTPROPERTY( id, 'TableHasPrimaryKey' ) = 0 ;
>Alternatively, you can also use INFORMATION_SCHEMA views
instead of
>accessing the system table directly.
>--
>Anith
>
>.
>

Find SQL Server Product key installed on server

Hello;

I am trying to get information of SQL Server licensed key which is installed on our servers. I am using SQL Server 2005 standard edition. Is there a way to find out the product key which is installed on servers. I did some searches and found some thirdparty software to get that information but I don't want to use them on production, is there a way which microsoft recommends.

ThanksHello;

I am trying to get information of SQL Server licensed key which is installed on our servers. I am using SQL Server 2005 standard edition. Is there a way to find out the product key which is installed on servers. I did some searches and found some thirdparty software to get that information but I don't want to use them on production, is there a way which microsoft recommends.

Thanks
mmmm. Do you really think that Micro$oft is going to recommend software that could be used by the unscrupulous to pirate their software? :confused:|||Microsoft has a tool that is normally used by bulk licensors of their software that is used for license reporting that does display this information. If you are using bulk licensing (where you deploy from a set of master images, then write a check directly to Microsoft for the copies you have in production use) then check with either your own license administrator (usually a domain admin) or with your Microsoft Sales Representative.

-PatP

Find primary key columns

I'm trying to find out some information about the schema of a database so that I can write a smarter table browser for my application's admin interface. I need to get the primary keys of a table, but I can't seem to get them in a simple manner. I've tried using ASP.NET functionality (like GetSchemaTable() for instance) but it doesn't get all the information I want. I tried INFORMATION_SCHEMA.xxx but those views don't give me everything I need either. Here's what I need:

Table Name, Column Name, Column Position, Data Type, Domain Name (user defined data type name), Length (in characters for nvarchar/nchar please), Is it nullable, Is it computed, Referenced Table Name, Referenced Column Name, Is it part of the primary key

I enforce that the schema allows a column to reference at most one column in one other table, so the "referenced" columns will either be null or contain at most one value. I've constructed this so far:

select o.name TableName
, c.name ColumnName
, fko.name ReferenceTableName
, fkc.name ReferenceColumnName
, COALESCE(t.name, t.name) ColumnType
, c.prec ColumnLength
, c.isnullable IsNullable
, c.iscomputed IsComputed
, c.colid ColumnOrder
from syscolumns c
inner join sysobjects o on c.id=o.id
inner join systypes t on c.xusertype=t.xusertype
left outer join sysforeignkeys fk on c.id=fk.fkeyid and c.colid=fk.fkey
left outer join sysobjects fko on fk.rkeyid=fko.id
left outer join syscolumns fkc on fk.rkeyid=fkc.id and fk.rkey=fkc.colid
where o.xtype='U'

Where can I find out if a column is a primary key?I don't see a way for you to get all of the information you are looking for out of the Information_Schema views, either.

I normally get the "is this a primary key?" information out of the Information_Schema.Key_Column_Usage view joined on the Information_Schema.Table_Constraints view. I am not certain of the underlying sys tables (althought perusal of those views will obviously tell you). Below is some incomplete code showing how I'd join the KCU and TC views:

SELECT
 Tables.Table_Name AS [Table Name],
 Columns.Column_Name AS [Column Name],
 Columns.Ordinal_Position AS [Column Position],
 CASE WHEN TC.Constraint_Name IS NULL Then 'No' Else 'Yes' END AS [Part of the Primary Key]
FROM
 information_schema.tables AS Tables
LEFT OUTER JOIN
 information_schema.columns AS Columns ON Tables.Table_Catalog = Columns.Table_Catalog AND Tables.Table_Schema = Columns.Table_Schema AND Tables.Table_Name = Columns.Table_name
LEFT OUTER JOIN
 information_schema.key_column_usage AS KCU ON Tables.Table_Catalog = KCU.Table_Catalog AND Tables.Table_Schema = KCU.Table_Schema AND Columns.Column_Name = KCU.Column_Name
LEFT OUTER JOIN
 information_schema.table_constraints AS TC ON Tables.Table_Catalog = TC.Table_Catalog AND Tables.Table_Schema = TC.Table_Schema AND KCU.Constraint_Name = TC.Constraint_Name AND TC.constraint_type = 'PRIMARY KEY'

Terri|||Thanks for the idea to look at the INFORMATION_SCHEMA.KEY_COLUMN_USAGE directly! You can execute "sp_helptext [INFORMATION_SCHEMA.KEY_COLUMN_USAGE]" if you want to see how this view is defined. It uses an undocumented table master.dbo.spt_values, but it did lead me to the sysindexkeys and sysindexes table as I was looking for documentation of the spt_values table.

Here is my solution, for anyone who is interested. I use only explicitly documented columns of sysXXX tables, so that hopefully it might work in SQL Server versions other than SQL Server 2000.

select o.name TableName
, c.name ColumnName
, t.name ColumnType
, c.prec ColumnLength
, CONVERT(bit, COALESCE(pko.id, 0)) IsKey
, CONVERT(bit, c.isnullable) IsNullable
, CONVERT(bit, c.iscomputed) IsComputed
, c.colid ColumnOrder
, fko.name ReferenceTableName
, fkc.name ReferenceColumnName
from syscolumns c
inner join sysobjects o on c.id=o.id
inner join systypes t on c.xusertype=t.xusertype
left outer join sysforeignkeys fk on c.id=fk.fkeyid and c.colid=fk.fkey
left outer join sysobjects fko on fk.rkeyid=fko.id
left outer join syscolumns fkc on fk.rkeyid=fkc.id and fk.rkey=fkc.colid
left outer join sysindexkeys ik on c.id=ik.id and c.colid=ik.colid
left outer join sysindexes i on ik.id=i.id and ik.indid=i.indid
left outer join sysobjects pko on c.id=pko.parent_obj and i.name=pko.name
where o.xtype='U'

This assumes that each column references at most one other column. If you have a column that references more than one column, you'll get a row for each referenced column due to the left outer joins. It is ok for more than one column to be part of a primary key. ColumnType is either the user defined data type or the system defined data type if the column was not specified with a user defined data type. ColumnLength is the length in characters for nvarchar and nchar, NULL for text, ntext and image, and the length in bytes for all other types. The IsKey, IsNullable and IsComputed columns are converted to bit type so that ASP.NET can automatically treat them as Boolean values.

find primary and foreign keys

I have a DB with 100 tables. I was wondering if anybody knows a quick way to list primary and foreign key with the column name for all the tables.

Your help would make my life a lot easier

thanks

Hi,

The below will list all PK's:

SELECT o.name AS 'TableName',
c.name AS 'PKColumnName'
FROM sysobjects o, sysindexes x, syscolumns c, sysindexkeys xk
WHERE o.type in ('U')
AND x.id = o.id
AND o.id = c.id
AND o.id = xk.id
AND x.indid = xk.indid
AND c.colid = xk.colid
AND xk.keyno <= x.keycnt
AND (x.status&32) = 0
AND convert(bit,(x.status & 0x800)/0x800) = 1
ORDER BY o.name, c.name

Cheers

Rob

|||Please use the INFORMATION_SCHEMA views instead of accesing system tables or using undocumented columns. The view INFORMATION_SCHEMA.KEY_COLUMN_USAGE will give the information you are looking for.

FIND PK across database?

Hi,
I wanted to know list of tables which are not having Primary key across
the database.How to get that list?
does any on ehave any script which tell us which are those tables don't
have primary keys across the database?
thx
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Hi,
Attached the procedure to identify the tables which do not have primary key
across databases. This procedure takes database name as parameter.
Craete proc pkey @.dbname varchar(30)
as
begin
set quoted_identifier off
declare @.sql varchar(1000)
EXEC ('use '+@.dbname+ ' select name as Table_name from sysobjects where name
not in(select object_name(parent_obj) from sysobjects where type=''K'') and
type =''u''')
end
How to execute
--
Exec pkey 'master'
Thanks
Hari
MCDBA
"Seena Blace" <sqlserverdb@.yahoo.com> wrote in message
news:ugrmQizAEHA.1560@.tk2msftngp13.phx.gbl...
> Hi,
> I wanted to know list of tables which are not having Primary key across
> the database.How to get that list?
> does any on ehave any script which tell us which are those tables don't
> have primary keys across the database?
> thx
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||not sure what you mean by 'across the database'.
anyway, here is a query that will return the names of the tables without pri
mary
key based on the defined database.
e.g.
declare @.db sysname
set @.db='Northwind'
exec('select name
from '+@.db+'..sysobjects o1
where xtype=''U''
and not exists(select *
from '+@.db+'..sysobjects
where xtype=''PK''
and parent_obj=o1.id)')
-oj
http://www.rac4sql.net
"Seena Blace" <sqlserverdb@.yahoo.com> wrote in message
news:ugrmQizAEHA.1560@.tk2msftngp13.phx.gbl...
> Hi,
> I wanted to know list of tables which are not having Primary key across
> the database.How to get that list?
> does any on ehave any script which tell us which are those tables don't
> have primary keys across the database?
> thx
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||Hi
I have a DB with 16 tables which doesnt have a PK (this db
is designed by someone else). What are the outcomes in
terms of query response etc and DB integrity , when a
client application uses these tables. Also pls let me know
does primary key plays any major role in data **retrival**
from a client application. Also when do I need not go for
a primary key for a Table?
Sorry if my query is foolish. I am learning things about
DB Design and I want to make sure that I am learning good
things.
Sincerely
Chip
>--Original Message--
>not sure what you mean by 'across the database'.
>anyway, here is a query that will return the names of the
tables without primary
>key based on the defined database.
>e.g.
>declare @.db sysname
>set @.db='Northwind'
>exec('select name
>from '+@.db+'..sysobjects o1
>where xtype=''U''
>and not exists(select *
>from '+@.db+'..sysobjects
>where xtype=''PK''
>and parent_obj=o1.id)')
>
>--
>-oj
>http://www.rac4sql.net
>
>"Seena Blace" <sqlserverdb@.yahoo.com> wrote in message
>news:ugrmQizAEHA.1560@.tk2msftngp13.phx.gbl...
Primary key across
those tables don't
***
>
>.
>

FIND PK across database?

Hi,
I wanted to know list of tables which are not having Primary key across
the database.How to get that list?
does any on ehave any script which tell us which are those tables don't
have primary keys across the database?
thx
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Hi,
Attached the procedure to identify the tables which do not have primary key
across databases. This procedure takes database name as parameter.
Craete proc pkey @.dbname varchar(30)
as
begin
set quoted_identifier off
declare @.sql varchar(1000)
EXEC ('use '+@.dbname+ ' select name as Table_name from sysobjects where name
not in(select object_name(parent_obj) from sysobjects where type=''K'') and
type =''u''')
end
How to execute
--
Exec pkey 'master'
Thanks
Hari
MCDBA
"Seena Blace" <sqlserverdb@.yahoo.com> wrote in message
news:ugrmQizAEHA.1560@.tk2msftngp13.phx.gbl...
> Hi,
> I wanted to know list of tables which are not having Primary key across
> the database.How to get that list?
> does any on ehave any script which tell us which are those tables don't
> have primary keys across the database?
> thx
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||not sure what you mean by 'across the database'.
anyway, here is a query that will return the names of the tables without primary
key based on the defined database.
e.g.
declare @.db sysname
set @.db='Northwind'
exec('select name
from '+@.db+'..sysobjects o1
where xtype=''U''
and not exists(select *
from '+@.db+'..sysobjects
where xtype=''PK''
and parent_obj=o1.id)')
-oj
http://www.rac4sql.net
"Seena Blace" <sqlserverdb@.yahoo.com> wrote in message
news:ugrmQizAEHA.1560@.tk2msftngp13.phx.gbl...
> Hi,
> I wanted to know list of tables which are not having Primary key across
> the database.How to get that list?
> does any on ehave any script which tell us which are those tables don't
> have primary keys across the database?
> thx
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Hi
I have a DB with 16 tables which doesnt have a PK (this db
is designed by someone else). What are the outcomes in
terms of query response etc and DB integrity , when a
client application uses these tables. Also pls let me know
does primary key plays any major role in data **retrival**
from a client application. Also when do I need not go for
a primary key for a Table?
Sorry if my query is foolish. I am learning things about
DB Design and I want to make sure that I am learning good
things.
Sincerely
Chip
>--Original Message--
>not sure what you mean by 'across the database'.
>anyway, here is a query that will return the names of the
tables without primary
>key based on the defined database.
>e.g.
>declare @.db sysname
>set @.db='Northwind'
>exec('select name
>from '+@.db+'..sysobjects o1
>where xtype=''U''
>and not exists(select *
>from '+@.db+'..sysobjects
>where xtype=''PK''
>and parent_obj=o1.id)')
>
>--
>-oj
>http://www.rac4sql.net
>
>"Seena Blace" <sqlserverdb@.yahoo.com> wrote in message
>news:ugrmQizAEHA.1560@.tk2msftngp13.phx.gbl...
>> Hi,
>> I wanted to know list of tables which are not having
Primary key across
>> the database.How to get that list?
>> does any on ehave any script which tell us which are
those tables don't
>> have primary keys across the database?
>> thx
>>
>> *** Sent via Developersdex http://www.developersdex.com
***
>> Don't just participate in USENET...get rewarded for it!
>
>.
>

Find differences in Two tables

I have a parent and a child table. When I was loading the
child table I ran into foreign key error. I need to find
the different values in the text file I am loading into
the child table (Parent table is already loaded). There
are 4 columns in the foreign key definition. I have loaded
the text data into another table(did not include the
foreign key but the indexes).
I have the following query that is taking forever in a
table with 88000 rows. Is there a quicker query (Both
tables have the necessary indexes)'.
Select * from another an
Join parent pr on
an.col1 <> pr.col1 AND an.col2 <> pr.col2
an.col3 <> pr.col3 AND an.col4 <> pr.col4
Thanks for any helpTry:
Select * from another an
left outer Join parent pr on
an.col1 = pr.col1 AND an.col2 = pr.col2
an.col3 = pr.col3 AND an.col4 = pr.col4
WHERE pr.col1 IS NULL
Unequality (<> ) is not sargeable, i.e. the Query optimizer can not use
indexes when trying to solve an unequality, but it can use indexes when
trying to solve equality operations.
Jacco Schalkwijk
SQL Server MVP
"Kavin" <anonymous@.discussions.microsoft.com> wrote in message
news:93e401c40522$eb548bb0$a101280a@.phx.gbl...
> I have a parent and a child table. When I was loading the
> child table I ran into foreign key error. I need to find
> the different values in the text file I am loading into
> the child table (Parent table is already loaded). There
> are 4 columns in the foreign key definition. I have loaded
> the text data into another table(did not include the
> foreign key but the indexes).
> I have the following query that is taking forever in a
> table with 88000 rows. Is there a quicker query (Both
> tables have the necessary indexes)'.
> Select * from another an
> Join parent pr on
> an.col1 <> pr.col1 AND an.col2 <> pr.col2
> an.col3 <> pr.col3 AND an.col4 <> pr.col4
> Thanks for any help
>|||That did it.......
Thanks.

>--Original Message--
>Try:
>Select * from another an
>left outer Join parent pr on
>an.col1 = pr.col1 AND an.col2 = pr.col2
>an.col3 = pr.col3 AND an.col4 = pr.col4
>WHERE pr.col1 IS NULL
>Unequality (<> ) is not sargeable, i.e. the Query
optimizer can not use
>indexes when trying to solve an unequality, but it can
use indexes when
>trying to solve equality operations.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Kavin" <anonymous@.discussions.microsoft.com> wrote in
message
>news:93e401c40522$eb548bb0$a101280a@.phx.gbl...
the
loaded
>
>.
>

Find Countries.. MDX

I want to get all the country names from a cube where another Key figure is NOT equal spaces or zeros. Please let me know if some one can help me.

My Cube is CFS_PERF/CFS_PERF_CFSPERF_GL
Want to get 0PAYER_COUNTRY where

[Measures].[3M1DTA8FO1HVYVOI6HUABEXHX] is NOT equal to zero

Please help !!!

Try this:

SELECT [Measures].[3M1DTA8FO1HVYVOI6HUABEXHX] ON 0,

NON EMPTY [OPAYER_COUNTRY].members ON 1

FROM CFS_PERF_CFSPERF_GL

Find and delete reversed key pairs.

Hi!

I have a table with only two columns, which contain some city codes in the form of AAA, BBB. What I need to do is to find reversed key pairs in the same table and get rid of them so only one pair stays. In example, I have:

AAA, BBB

CCC, DDD

BBB, AAA

EEE, FFF

FFF, EEE

DDD, CCC

AAA, BBB and BBB, AAA mean the same to me, so I would like to leave only one of them. Do you have any idea on how to achieve this? I tried to use a cursor (yeah, I know cursors are bad, but this is a task I will most likely run once or every few weeks), but the table has over 130k records and it looks like I need at least a few hours for the cursor to complete it's work. Here's what I did:

Code Snippet

declare
@.origCity char(3)
,@.destCity char(3)

declare crs_cities cursor local fast_forward for
select
origCity, destCity
from
dbo.airports
order by
origCity

open crs_cities

fetch next from crs_cities
into @.origCity, @.destCity

while @.@.fetch_status = 0
begin
-- Delete rows having the same city pair in reverse order.
delete from
dbo.airports
where
destCity = @.origCity
and
origCity = @.destCity

fetch next from crs_cities
into @.origCity, @.destCity
end

close crs_cities
deallocate crs_cities

I could also add an autoincrement column before the pairs and use a while loop instead of a cursor, but there must be some SQL query to do it the proper way. It may be a whole procedure, whatever.

Thanks.

delete from airports
where origCity>destCity
and exists (select * from airports a2
where a2.origCity=airports.destCity
and a2.destCity=airports.origCity)
|||You also need to adjust your insert code so that "duplicate" records do not get inserted in the first place.|||

I cannot believe it was so simple! x_x

If I may ask - why origCity>destCity and not something else. I don't get it.

Kent Waldrop 2007 Mar wrote:

You also need to adjust your insert code so that "duplicate" records do not get inserted in the first place.

This was some data imported from a CSV I got from a client, so it's not a case.

|||

pawel.krakowiak wrote:

If I may ask - why origCity>destCity and not something else. I don't get it.

You need to ensure that the operation only deletes one record of each pair (without that condition it would have deleted both records of the pair). The condition arbitrarily selects the records where origCity sorts after destCity to remove. Mark could equally well have used origCity < destCity and removed the other record of the pair.

|||Thought so... I never used such construct (I mean, it depends on the context) before, though. Good to know.