Fragmented array: primary

Showing posts with label primary. Show all posts

Friday, March 30, 2012

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

Thursday, March 29, 2012

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.

Wednesday, March 28, 2012

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

Wednesday, March 21, 2012

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 transaction that generated 4 Go in journal file

We are using SQL Server with logshipping.

I noticed in the last two days the presence of 2 bigs journal files
(about 2Go each, transfered from primary to secondary ).

I want to know if there is a way to see what was the operation that lead to have this to big 2 log files
(sql statement or transaction... : with Oracle for instance, if we have chance, we can find this kind of info in
a dynamic views by the name of : v$sqlarea...)...

Thanks in advanceI don't think you can do this without a third party tool, such as the Lumigent (http://www.lumigent.com/) log explorer. I'm not even sure what that can do with log shipping, but it is where I'd start.

-PatP

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
>>
>

Monday, March 19, 2012

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
>
>.
>

Monday, March 12, 2012

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!
>
>.
>

Sunday, February 19, 2012

filtering rows that already exist in the destination table

Hi All,

What is the most straighforward way of not importing rows that already exist in the destination table (as determined by a primary key value)? Thanks.

Regards,

Daniel

Method 2 here: http://www.sqlis.com/default.aspx?311

-Jamie

|||Excellent, thanks.

Friday, March 30, 2012

Finding Missing Records

Thursday, March 29, 2012

Finding duplicates in the table

Wednesday, March 28, 2012

Finding Correct Primary Key

Finding Correct Primary Key

Finding Correct Primary Key

Wednesday, March 21, 2012

find unique identifier through multiple tables

find transaction that generated 4 Go in journal file

Find the Forign keys's primary key table

Monday, March 19, 2012

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

Monday, March 12, 2012

Find primary key columns

find primary and foreign keys

FIND PK across database?

FIND PK across database?

Sunday, February 19, 2012

filtering rows that already exist in the destination table

Fragmented array

Blog Archive

About Me