Trying To Find Tabes Without Primary Keys

May 29, 2008

I wrote a query that when run, will give me a listing of all tables in a database, and whether or not those table have primary keys.

Here's the query:




Code Snippet
select so.name, si.name from sysobjects so
left outer JOIN sysindexes si on so.id = si.id
where si.status = '2066' and so.type = 'u'
order by so.name asc


It works for the most part save for one small (big) problem. It gives me a list of tables that have a corresponding primary key, but doesn't list those without one. For example, in one database I run this query. I know for a fact that there are 26 tables in this database, and my query returns 16 rows (one for each table with a PK). I'd like to see those as well as those without one, with a NULL in the NAME column for the PK.

I've tried every join combo under the sun and still can't get the desired results.

Any insight would be appreciated!

Thanks!

View 11 Replies


ADVERTISEMENT

Using SQL To Find Primary Keys

Jul 20, 2005

What query do I use to list the primary key for each user table, i.e.TABLE | PRIMARY_KEY |Regards,Alan*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 4 Replies View Related

Trying To Find Non Identity Primary Keys

Jan 26, 2006

This ain't working

SELECT T.TABLE_NAME,C.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLES T
JOIN INFORMATION_SCHEMA.COLUMNS C
ON T.TABLE_NAME = C.TABLE_NAME
WHERE OBJECTPROPERTY(OBJECT_ID(T.TABLE_NAME),
'TableHasIdentity') = 0
AND T.TABLE_TYPE = 'BASE TABLE'
AND OBJECTPROPERTY(OBJECT_ID(C.COLUMN_NAME),'IsPrimary Key') = 1
ORDER BY T.TABLE_NAME,C.COLUMN_NAME

This is giving me bogus results...

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE OBJECTPROPERTY(OBJECT_ID(COLUMN_NAME),'IsPrimaryKe y') = 1

I have PK's all over the place. What gives? Too many cocktails with lunch?

View 1 Replies View Related

Script: Find Tables Without Primary Keys

Aug 3, 2002

...got tired of looking at them by hand.

Cheers
-b

DECLARE @vcDB varchar(20),@vcSchema varchar(20),@vcTable varchar(200)

Select @vcDB='mydb',@vcSchema='dbo'

DECLARE cLoop cursor for
select TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where TABLE_CATALOG=@vcDB
and TABLE_SCHEMA=@vcSchema
order by TABLE_NAME ASC

open cLoop

FETCH NEXT FROM cLoop INTO @vcTable
WHILE @@FETCH_STATUS=0
BEGIN
if not exists (SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = @vcSchema
AND TABLE_NAME = @vcTable
AND CONSTRAINT_TYPE = 'PRIMARY KEY')
print @vcTable + ' does not have a primary key'

FETCH NEXT FROM cLoop INTO @vcTable
END
Close cLoop
DEALLOCATE cLoop

View 2 Replies View Related

Transact SQL :: Find Column ADRSCODE Used In Indexes Or Primary Keys?

Oct 12, 2015

I wanted to find all occurrences of ADRSCODE in a Database where ADRSCODE is in either an Index or a Primary Key.

I know how to get all of the occurences of ADRSCODE in a database and the table associated with it, I just want to tack on the Index and/or primary key.

SELECTOBJECT_NAME(object_id)FROMsys.columns
WHEREname
='foo'

How can I get the other bit of information ?

View 2 Replies View Related

How To Find Missing Records From Tables Involving Composite Primary Keys

Nov 23, 2006

Table 1







     Code
    Quarter

500002
26

500002
27

500002
28

500002
28.5

500002
29

 

Table 2







     Code
           Qtr

500002
26

500002
27

 

I have these two identical tables with the columns CODE & Qtr being COMPOSITE PRIMARY KEYS

Can anybody help me with how to compare the two tables to find the records not present in Table 2

That is i need this result







    Code
   Quarter

500002
28

500002
28.5

500002
29

I have come up with this solution

select scrip_cd,Qtr,scrip_cd+Qtr from Table1 where
scrip_cd+Qtr not in (select scrip_cd+qtr as 'con' from Table2)

i need to know if there is some other way of doing the same

Thanks in Advance

Jacx

View 3 Replies View Related

Auto Incremented Integer Primary Keys Vs Varchar Primary Keys

Aug 13, 2007

Hi,

I have recently been looking at a database and wondered if anyone can tell me what the advantages are supporting a unique collumn, which can essentially be seen as the primary key, with an identity seed integer primary key.

For example:

id [unique integer auto incremented primary key - not null],
ClientCode [unique index varchar - not null],
name [varchar null],
surname [varchar null]

isn't it just better to use ClientCode as the primary key straight of because when one references the above table, it can be done easier with the ClientCode since you dont have to do a lookup on the ClientCode everytime.

Regards
Mike

View 7 Replies View Related

Creating Inter-table Relationships Using Primary Keys/Foreign Keys Problem

Apr 11, 2006

Hello again,

I'm going through my tables and rewriting them so that I can create relationship-based constraints and create foreign keys among my tables. I didn't have a problem with a few of the tables but I seem to have come across a slightly confusing hiccup.

Here's the query for my Classes table:

Code:

CREATE TABLE Classes
(
class_id
INT
IDENTITY
PRIMARY KEY
NOT NULL,

teacher_id
INT
NOT NULL,

class_title
VARCHAR(50)
NOT NULL,

class_grade
SMALLINT
NOT NULL
DEFAULT 6,

class_tardies
SMALLINT
NOT NULL
DEFAULT 0,

class_absences
SMALLINT
NOT NULL
DEFAULT 0,

CONSTRAINT Teacher_instructs_ClassFKIndex1 FOREIGN KEY (teacher_id)
REFERENCES Users (user_id)
)

This statement runs without problems and I Create the relationship with my Users table just fine, having renamed it to teacher_id. I have a 1:n relationship between users and tables AND an n:m relationship because a user can be a student or a teacher, the difference is one field, user_type, which denotes what type of user a person is. In any case, the relationship that's 1:n from users to classes is that of the teacher instructing the class. The problem exists when I run my query for the intermediary table between the class and the gradebook:

Code:

CREATE TABLE Classes_have_Grades
(
class_id
INT
PRIMARY KEY
NOT NULL,

teacher_id
INT
NOT NULL,

grade_id
INT
NOT NULL,

CONSTRAINT Grades_for_ClassesFKIndex1 FOREIGN KEY (grade_id)
REFERENCES Grades (grade_id),

CONSTRAINT Classes_have_gradesFKIndex2 FOREIGN KEY (class_id, teacher_id)
REFERENCES Classes (class_id, teacher_id)
)

Query Analyzer spits out: Quote: Originally Posted by Query Analyzer There are no primary or candidate keys in the referenced table 'Classes' that match the referencing column list in the foreign key 'Classes_have_gradesFKIndex2'. Now, I know in SQL Server 2000 you can only have one primary key. Does that mean I can have a multi-columned Primary key (which is in fact what I would like) or does that mean that just one field can be a primary key and that a table can have only the one primary key?

In addition, what is a "candidate" key? Will making the other fields "Candidate" keys solve my problem?

Thank you for your assistance.

View 1 Replies View Related

Creating Indexes On Columns That Are Foreign Keys To Primary Keys Of Other Tables

Jul 16, 2014

what the best practice is for creating indexes on columns that are foreign keys to the primary keys of other tables. For example:

[Schools] [Students]
---------------- -----------------
| SchoolId PK|<-. | StudentId PK|
| SchoolName | '--| SchoolId |
---------------- | StudentName |
-----------------

The foreign key above is as:

ALTER TABLE [Students] WITH CHECK ADD CONSTRAINT [FK_Students_Schools]
FOREIGN KEY([SchoolId]) REFERENCES [Schools] ([SchoolId])

What kind of index would ensure best performance for INSERTs/UPDATEs, so that SQL Server can most efficiently check the FK constraints? Would it be simply:

CREATE INDEX IX_Students_SchlId ON Students (SchoolId)
Or
CREATE INDEX IX_Students_SchlId ON Students (SchoolId, StudentId)

In other words, what's best practice for adding an index which best supports a Foreign Key constraint?

View 4 Replies View Related

Generate Script For Primary Keys And Foreing Keys

May 16, 2008



Pls let me know How I generate script for All primary keys and foreign keys in a table. Thereafter that can be used to add primary keys and foreign keys in another databse with same structure.

Also how I script default and other constraints of a table?

View 2 Replies View Related

Urgent !!!!! Nee Explanation On Primary Keys And FK Keys

Jul 15, 2002

Can somebody explain to me how to best do inserts where you have primary keys and foreign keys.l'm battling.

Is there an article on primary keys/Pk ?

View 1 Replies View Related

Primary Keys

Nov 15, 2006

"Violation of PRIMARY KEY of restriction 'PK_Approve_Overtime'. The overlapping key cannot be inserted in object 'Dbo.Approve_Overtime'. The statement was ended." 
can soemone explain to me why i have this kind of error?
i have this two tables. approve_overtime table has a primary key id_no and application_input table with a primary key of id_no!
all the values from of application_input will be stored also in approve_overtime.
sometimes the datas can be stored.sometimes it cannot and produces an error!  
 
what do u think?
 
hmmm pls help!

View 1 Replies View Related

Primary Keys

May 16, 2002

Using SQL Svr 7.0. It appears that primary keys are created as nonclustered
unique indexes. Is there a configuration setting I can use to make them be
created as clustered unique indexes?

View 1 Replies View Related

Primary Keys

Apr 4, 2001

If a table has a column defined as Int, Identity(1,1) which is to be used as the primary key, should that index be defined as clustered or non-clustered? In Enterprise manager when you create a PK on a table it defaults to being a clustered index. I am sure the answer depends on the other index requirements and columns in the table but I'd like to see what other ppl think about this.

Thanks!

View 4 Replies View Related

Two Primary Keys???

Mar 14, 2000

I have read that SQL Server tables can't have more than one primary key. I know in Access two keys are allowed. Why can't there be two primary keys in a single table in SQL Server 7.

Thanks

View 1 Replies View Related

Primary Keys

Jun 15, 2004

Hi everyone,
Does someone knows how can I drop a primary key (that I don't know the name) from a table in one sql statement.

Thanks,
Fady

View 3 Replies View Related

Primary Keys

Feb 19, 2004

HI ,

I accidently removed the primary keys from my table by mistake. Is there anyway ,That i can get the PK's back to what is used to be. Need Help pls...... When I try "resetting" the PK I kep getting this error:



'table_name' table
- Unable to create index 'PK_tablename'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is '1'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint. See previous errors.
[Microsoft][ODBC SQL Server Driver][SQL Server]Warning: The table 'tDetail' has been created but its maximum row size (12521) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.

View 4 Replies View Related

Two Primary Keys

May 17, 2004

In access you can have two fields that are primary keys with one or the other repeating as long as the combination is not repeated.
i.e.
key1 key2
200410 12345
200410 12346
200410 12588
etc for all 200410 there can not be a repeat of any value of key 2

is there a way to have this dual key in MSSQL :confused:

View 1 Replies View Related

Primary Keys

Feb 13, 2006

Hi

How can 2 columns be clubbed together to form a primary key , i mean I have 2 columns job & Batch , need to club them together to form the primary key

How is it done I mean in the design , how to define that both the columns togethter form a primary key ,

Cause when I go to the table , it allows me to create a PK through the EM only for one column

Please help

View 3 Replies View Related

Primary Keys

Oct 17, 2007

Hi there,

is there any way I can use INSERT so if I try to copy duplicate primary key data, it automatically skips the task and moves on to the next data?

View 11 Replies View Related

Primary Keys

May 15, 2008

I have declared a table with a single column to be a Primary key. If I write 2 records, the first comprsing "A" and the second "a" then I get an exception about violating the uniqueness of the key. Is there a way that I can define the key to be case sensitive.

Many thanks

View 1 Replies View Related

Help With Primary Keys

May 4, 2007

I have two tables with similar primary keys, table a and table b, and I want to find out all the key values that are disimilar between the tables. Can this be done with a select? if so what would it be.

View 5 Replies View Related

Primary, Forgein Keys

Dec 29, 2007

I am not entirly sure what a forigen key is, is it a unique ID which is the same as the primary key? If adding a foreign key to a table that already has data, will it update each row with a unique ID or will it only create a unique ID on newly inserted records (for the foreign key)? If the foreign key is the same as the primary key then why do we need to even add a foreign key at all?For example a table like: Table Name : Customers--CustomerID uniqueidentity (primary Key)--FirstName nchar  Table Name : Orders --OrderID int--CustomerID int  Obviuously CustomerID are going to be the same in both tables, so why would you need to add a foreign key on 'CustomerID' to the 'Order' table, can't SQL match the customerID in each table any way? Cheers 

View 3 Replies View Related

Rest Primary Keys?

Mar 20, 2008

HiI have a database and I been inserting some dummy data into it but now I want to upload it to my website but I want to delete all the dummy data and start the PK back at 1. I truancted all the data but it still keeps counting from the last one.So how do I reset it? 

View 6 Replies View Related

Primary Keys Question

May 15, 2001

Hi,
I have a question on pk?
I have a db which has tables which do not have pk?I want to create Pk on each one of them - infact,I want to alter the table , add one more field and make it the pk .
My question is - do I will have to go to each table,alter it and then create the pk on it or do someone have any script or better way to do it?
Any help is appreciated.

View 1 Replies View Related

Replication - Primary Keys

May 17, 2001

Hi,
Just a continuation to my earlier queries on replication.
I have a db which I want to replicate - it do not have pk?
I do not want to create pk on existing columns - so I thought of creating one more column in all the tables and make them pk .
Any one has any idea if this will work fine or may give any problem which I should be prepare for.
Any thought appreciated -
pvd

View 1 Replies View Related

Indexes In Primary Keys

May 6, 2005

I have a 3rd party app which had a primary key with about 5 fields. The last field of this was a trantype. This app had a posting process which uses this in it's sql. Ran rather slow. We added an individual index to this field and cut processing down 90%. It almost seemded like sl server was ignoring this index. Is this because it was the last field in the primary key index?

View 1 Replies View Related

BCP And Duplicate Primary Keys

Sep 10, 1998

Hi All,
I`m using BCP to import ASCII data text into a table that already has many records. BCP failed because of `Duplicate primary key`.
Now, is there any way using BCP to know precisely which record whose primary key caused that `violation of inserting duplicate key`.
I already used the option -O to output error to a `error.log`, but it doesn`t help much, because that error log contains the same error message mentioned above without telling me exactly which record so that I can pull that `duplicate record` out of my import data file.
TIA and you have a great day.
David Nguyen.

View 3 Replies View Related

Composite Primary Keys

Jun 25, 2002

Newbie question...

I have two tables (categories & listings) which create a many-to-many relationship.

I have created an interim table with the primary keys from each table as a composite primary key...(cat_id & list_id).

How does the interim table get populated with the id's?

When I do an insert statement to insert data into the categories table, the cat_id field is automatically generated...same with the listings table, but when (and how) does the primary key data get into the interim table.

Thanks in advance for the assistance.

View 1 Replies View Related

Missing Primary Keys

Jun 24, 2004

HI Folks ,

I have a problem with the primary keys in my main Db , I want to setup replication and looks like someone tampered with my database by removing the primary keys.and in order to setup replication i need this table to have primary keys .THere are duplicates in that table but they are nessecary...and for this reason the primary keys do not want to "stick" when i try and specify them.Can anybody help

Burner

View 3 Replies View Related

Forcing Primary Keys

Jun 1, 2006

Hi all,

As our DB has no primary keys or indexes ive taken a copy of all populated tables and tried to force primary keys within a new DB.

the problem is all off the tables have multiple datasets within them, a dataset for each year. This causes all instances of ID numbers to not be unique as they are replicated for every year they are active.

Its a school database so a student who has been here for 3 years will have 3 instances of his ID number, one for each years' data set.

So how do i force primary keys if there is no unique identifier? ive been highlighting both data set and ID columns and setting that combination as the primary key.

Essentially i need to analyse the relationships between the tabls in a diagram and also run some speed tests to see how fast the db works when it has indexes and primary keys.

the reason im writing is that ive done this on ten tables and with another 160 to do im just checking im doing the right thing?



greg

View 14 Replies View Related

How To Use Multiple Keys As Primary Key

Jun 18, 2008

Hi Guys,

Can someone tell me what is the best way to check 2 tables lets say tb1 and tb2, to find those customers that are in tb1 but not in tb2 using four fields(cardno,spend,date,refno) as the primary key. Duplicates fields are in tb1

View 3 Replies View Related

Wide Primary Keys

Jul 23, 2005

I'm working on a system that is very address-centric and detection ofduplicate addresses is very important. As a result we have brokenaddresses down into many parts (DDL below, but I've left out somereference tables for conciseness), these being state, locality, street,street number, and address. The breakdown is roughly consistent withAustralian addressing standards, we're working on finalising this.Because we carry the primary key down each of the levels, this hasresulted in our address table having a very wide primary key (around170 characters). We refer to addresses from a number of other tablesand although my instinct is to use this natural key in the other tablesI wonder if we should just put a unique index on the natural key,create a surrogate primary key and use it in the other table. Anythoughts?CREATE TABLE dbo.States (StateID varchar (3) NOT NULL ,StateName varchar (50) NOT NULL ,CONSTRAINT PK_AddressStates PRIMARY KEY NONCLUSTERED(StateID))CREATE TABLE dbo.Localities (Locality varchar (46) NOT NULL ,StateID varchar (3) NOT NULL ,Postcode char (4) NOT NULL ,CONSTRAINT PK_Localities PRIMARY KEY NONCLUSTERED(Locality,StateID,Postcode),CONSTRAINT FK_AddressLocalities_AddressStates FOREIGN KEY(StateID) REFERENCES dbo.States (StateID))CREATE TABLE dbo.Streets (StreetName varchar (35) NOT NULL ,StreetTypeID varchar (10) NOT NULL ,StreetDirectionID varchar (2) NOT NULL ,Locality varchar (46) NOT NULL ,StateID varchar (3) NOT NULL ,Postcode char (4) NOT NULL ,CONSTRAINT PK_Streets PRIMARY KEY CLUSTERED(StreetName,StreetTypeID,StreetDirectionID,Locality,StateID,Postcode),CONSTRAINT FK_Streets_Localities FOREIGN KEY(Postcode,Locality,StateID) REFERENCES dbo.Localities (Postcode,Locality,StateID))CREATE TABLE dbo.StreetNumbers (StreetName varchar (35) NOT NULL ,StreetTypeID varchar (10) NOT NULL ,StreetDirectionID varchar (2) NOT NULL ,Locality varchar (46) NOT NULL ,StateID varchar (3) NOT NULL ,Postcode char (4) NOT NULL ,StreetNumber varchar (15) NOT NULL ,BuildingName varchar (100) NOT NULL ,CONSTRAINT PK_StreetNumbers PRIMARY KEY CLUSTERED(StreetName,StreetTypeID,StreetDirectionID,Locality,StateID,Postcode,StreetNumber),CONSTRAINT FK_StreetNumbers_Streets FOREIGN KEY(StreetName,StreetTypeID,StreetDirectionID,Locality,StateID,Postcode) REFERENCES dbo.Streets (StreetName,StreetTypeID,StreetDirectionID,Locality,StateID,Postcode))CREATE TABLE dbo.Addresses (StreetName varchar (35) NOT NULL ,StreetTypeID varchar (10) NOT NULL ,StreetDirectionID varchar (2) NOT NULL ,Locality varchar (46) NOT NULL ,StateID varchar (3) NOT NULL ,Postcode char (4) NOT NULL ,StreetNumber varchar (15) NOT NULL ,AddressTypeID varchar (6) NOT NULL ,AddressName varchar (20) NOT NULL ,CONSTRAINT PK_StreetNumberPrefixes PRIMARY KEY CLUSTERED(StreetName,StreetTypeID,StreetDirectionID,Locality,StateID,Postcode,StreetNumber,AddressTypeID,AddressName),CONSTRAINT FK_Addresses_StreetNumbers FOREIGN KEY(StreetName,StreetTypeID,StreetDirectionID,Locality,StateID,Postcode,StreetNumber) REFERENCES dbo.StreetNumbers (StreetName,StreetTypeID,StreetDirectionID,Locality,StateID,Postcode,StreetNumber))

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved