Drop Table

Support Forum for database administrators and web based access to important newsgroups related to databases
Register on Database Support Forum Edit your profileCalendarFind other Database Support forum membersFrequently Asked QuestionsSearch this forum -> 
For Database admins: Free Database-related Magazines Now Free shipping to Texas


Post New Thread










Thread
Author

Another primary key. schema question
I have reviewed previous posts about finding primary key, table schema
information, etc...
I have got quite far but am now getting duplicate records. Not sure
what else to add here to fix this.
If I use this query, I get various column information and the Primary
key is identified by my PK_Col column correctly:

SELECT cols.COLUMN_NAME,
DATA_TYPE,
Length = case
when  CHARACTER_MAXIMUM_LE
NGTH is null
then st.length
else  CHARACTER_MAXIMUM_LE
NGTH END,
 CHARACTER_MAXIMUM_LE
NGTH,
PK_COL = case when K.COLUMN_NAME = cols.COLUMN_NAME
then 'PK' else '' END,
T.CONSTRAINT_NAME,
IS_NULLABLE
FROM (Test.INFORMATION_SCHEMA.COLUMNS cols
left join systypes st on cols.DATA_TYPE = st.name)
left join
(INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
ON T.CONSTRAINT_NAME = K.CONSTRAINT_NAME)
on T.TABLE_NAME = cols.TABLE_NAME

WHERE cols.TABLE_NAME = 'Tester'
and T.CONSTRAINT_TYPE = 'PRIMARY KEY'
ORDER BY cols.COLUMN_NAME

The table "tester" has one PK (first column) and one foreign key (2nd
column). The above query returns one row for each column in table
tester. So this is correct results:

COL_1   varchar   26    26    PK   PK_Tester   NO
COL_2     Int        4       4              PK_Tester   NO
etc...

Now I also would like to see if a column is a foreign key. However,
when I change the WHERE clause like this :
WHERE cols.TABLE_NAME = 'Tester'
and (T.CONSTRAINT_TYPE = 'PRIMARY KEY'
or T.CONSTRAINT_TYPE = 'FOREIGN KEY')

I now get 2 records for every table column represented. The name of
the PK and the name of the FK are each displayed for every table
column row of data I get back;
For expl: This is what I should get:
COL_1   varchar   26    26    PK   PK_Tester   NO
COL_2     Int        4       4      PK   FK_Tester    NO
etc...

But this is what I am getting:
COL_1   varchar   26    26    PK   PK_Tester   NO
COL_1   varchar   26    26            FK_Tester   NO
COL_2     Int        4       4      PK   FK_Tester   NO
COL_2     Int        4       4              PK_Tester   NO

Note, I didnt set up code to display FK yet, in the PK_Col column.
That will come after I resolve the duplicate result rows problem.

Do I need to add more criteria to a JOIN clause or a WHERE clause and
if so, any aidea what?

Thanks
Jeff

Report this thread to moderator Post Follow-up to this message
Old Post
Jeff User
12-28-05 01:23 AM


Sponsored Links





Last Thread Next Thread
Post New Thread

MS SQL Server archive

Show a Printable Version Email This Page to Someone! Receive updates to this thread
Microsoft SQL Server
Access database support
PostgreSQL Replication
SQL Server ODBC
FoxPro Support
PostgreSQL pgAdmin
SQL Server Clustering
MySQL ODBC
Web Applications with dBASE
SQL Server CE
MySQL++
Sybase Database Support
MS SQL Full Text Search
PostgreSQL Administration
SQL Anywhere support
DB2 UDB Database
Paradox Database Support
Filemaker Database
Berkley DB
SQL 2000/2000i database
ASE Database
Forum Jump:
All times are GMT. The time now is 04:22 AM.

 
Mobile devices forum | Database support forum archive




Copyrights DropTable.com Database Support Forum 2004 - 2006