need to get non primary key column names.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

need to get non primary key column names.

Author

Topic

demausdauth
Starting Member

17 Posts

Posted - 2008-07-02 : 12:41:19

I am making a utility for our program and i need to have the primary keys of a table separate from the other fields in a table. Now I have found and tweaked a bit of sql to get the primary keys...


SELECT     PKT.name, PKT.TName, COL.DATA_TYPE, COL.CHARACTER_MAXIMUM_LENGTH
FROM         (SELECT     SC.name, SO.name AS TName
                       FROM          sys.sysobjects AS SO INNER JOIN
                                              sys.sysobjects AS SPK ON SO.id = SPK.parent_obj INNER JOIN
                                              sys.sysindexes AS SI ON SO.id = SI.id AND SPK.name = SI.name INNER JOIN
                                              sys.sysindexkeys AS SIK ON SO.id = SIK.id AND SI.indid = SIK.indid INNER JOIN
                                              sys.syscolumns AS SC ON SO.id = SC.id AND SIK.colid = SC.colid
                       WHERE      (SO.name = 'Doc') AND (SO.xtype = 'U') AND (SPK.xtype = 'PK')) AS PKT INNER JOIN
                      INFORMATION_SCHEMA.COLUMNS AS COL ON PKT.TName = COL.TABLE_NAME AND PKT.name = COL.COLUMN_NAME

This works beautifully to get my prmiary keys of whatever table I need, what i want now is to get the other fields of the table and their datatypes and max lengths. I thought I could join it back on the information_schema.columns table and 'remove' the primary key columns that I already know about.


SELECT     COL2.TABLE_NAME, COL2.COLUMN_NAME, COL2.DATA_TYPE, COL2.CHARACTER_MAXIMUM_LENGTH
FROM         INFORMATION_SCHEMA.COLUMNS AS COL2 LEFT OUTER JOIN
                          (SELECT     PKT.name, PKT.TName, COL.DATA_TYPE, COL.CHARACTER_MAXIMUM_LENGTH
                            FROM          (SELECT     SC.name, SO.name AS TName
                                                    FROM          sys.sysobjects AS SO INNER JOIN
                                                                           sys.sysobjects AS SPK ON SO.id = SPK.parent_obj INNER JOIN
                                                                           sys.sysindexes AS SI ON SO.id = SI.id AND SPK.name = SI.name INNER JOIN
                                                                           sys.sysindexkeys AS SIK ON SO.id = SIK.id AND SI.indid = SIK.indid INNER JOIN
                                                                           sys.syscolumns AS SC ON SO.id = SC.id AND SIK.colid = SC.colid
                                                    WHERE      (SO.name = 'Doc') AND (SO.xtype = 'U') AND (SPK.xtype = 'PK')) AS PKT INNER JOIN
                                                   INFORMATION_SCHEMA.COLUMNS AS COL ON PKT.TName = COL.TABLE_NAME AND PKT.name = COL.COLUMN_NAME) 
                      AS PrimeKeys ON PrimeKeys.name <> COL2.COLUMN_NAME
WHERE     (COL2.TABLE_NAME = PrimeKeys.TName)

But this produces results that are definitely not correct. I have tried right and left joins and they produce the same results for this table, and inner join produces results very off.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-02 : 12:57:16

See if this helps

select c.* from information_schema.columns c where not exists
(select * from information_schema.key_column_usage where table_name=c.table_name and column_name=c.column_name)
and table_name='table_name'

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-02 : 13:09:01

Wait you may need to use this


select c.* from information_schema.columns c where not exists
(
	select * from information_schema.key_column_usage k inner join information_schema.table_constraints t 
	on k.table_name=t.table_name and k.constraint_name=t.constraint_name and t.constraint_type='PRIMARY KEY'
	where k.table_name=c.table_name and k.column_name=c.column_name)
and c.table_name='table_name'

Madhivanan

Failing to plan is Planning to fail

demausdauth
Starting Member

17 Posts

Posted - 2008-07-02 : 13:10:34

Ok madhivanan, that works.
now i got another question would it be better to use the informatation_schema.key_column_usage table to get my primary keys or go with the other way i found? Using the key_column_usage table would be much easier to understand when going back to look at this later on. This is only going to be used on sql2005 if that makes a difference.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-02 : 13:11:53

quote:
Originally posted by demausdauth

Ok madhivanan, that works.
now i got another question would it be better to use the informatation_schema.key_column_usage table to get my primary keys or go with the other way i found? Using the key_column_usage table would be much easier to understand when going back to look at this later on. This is only going to be used on sql2005 if that makes a difference.

Yes. Read my second reply
I think you didnt notice it

Madhivanan

Failing to plan is Planning to fail

demausdauth
Starting Member

17 Posts

Posted - 2008-07-02 : 13:20:04

ok that works,

Does the information_schema.key_column_usage table contain all the table keys? And if so would it be better to use that to get my keys versus the other method?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources