What's wrong with this query ? Why am I not getting the correct output

Question

What's wrong with this query ? Why am I not getting the correct output

mw_sql_developer

SSCoach

Points: 19445
More actions
October 22, 2015 at 10:57 am

#322082

Why is the name of the column BABY_DOB not appear in the output ?
I am simply trying to get a listing of table_name , col_name for those tables that have a primary key ( The primary key can have more than one column )
But see the output below
IF object_id('DELIVERY_IN', 'U') IS NOT NULL DROP TABLE DELIVERY_IN
Print 'Creating table DELIVERY_IN...'
Create table DELIVERY_IN
(
[MOM_MEM_NBR] [varchar] (75)NOT NULL,
[BABY_MEM_NBR] [varchar] (75),
[MOM_DOB] [datetime]NOT NULL,
[BABY_DOB] [datetime]NOT NULL,
[EDD] [datetime],
[GEST_AGE] [float],
[FILE_ID] [float]NOT NULLDEFAULT 11000
PRIMARY KEY ([MOM_MEM_NBR],[BABY_DOB] ),
FOREIGN KEY ([FILE_ID]) REFERENCES FILE_ID_IN([FILE_ID])
) ON [PRIMARY]
GO
Select object_name(SI.object_id) as tab_name,
( Select c.name from sys.columns c where object_id = SI.object_id AND c.column_id = index_column_id ) as [index_col_name]
from
sys.indexes SI
inner join sys.index_columns SIC on ( SIC.object_id = SI.object_id and (SI.is_primary_key =1) )
where SI.is_primary_key =1
ORDER BY 1,2
OUTPUT
tab_nameindex_col_name
DELIVERY_INBABY_MEM_NBR
DELIVERY_INMOM_MEM_NBR

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

mw112009 (10/22/2015)
Why is the name of the column BABY_DOB not appear in the output ?
I am simply trying to get a listing of table_name , col_name for those tables that have a primary key ( The primary key can have more than one column )
But see the output below
IF object_id('DELIVERY_IN', 'U') IS NOT NULL DROP TABLE DELIVERY_IN
Print 'Creating table DELIVERY_IN...'
Create table DELIVERY_IN
(
[MOM_MEM_NBR] [varchar] (75)NOT NULL,
[BABY_MEM_NBR] [varchar] (75),
[MOM_DOB] [datetime]NOT NULL,
[BABY_DOB] [datetime]NOT NULL,
[EDD] [datetime],
[GEST_AGE] [float],
[FILE_ID] [float]NOT NULLDEFAULT 11000
PRIMARY KEY ([MOM_MEM_NBR],[BABY_DOB] ),
FOREIGN KEY ([FILE_ID]) REFERENCES FILE_ID_IN([FILE_ID])
) ON [PRIMARY]
GO
Select object_name(SI.object_id) as tab_name,
( Select c.name from sys.columns c where object_id = SI.object_id AND c.column_id = index_column_id ) as [index_col_name]
from
sys.indexes SI
inner join sys.index_columns SIC on ( SIC.object_id = SI.object_id and (SI.is_primary_key =1) )
where SI.is_primary_key =1
ORDER BY 1,2
OUTPUT
tab_nameindex_col_name
DELIVERY_INBABY_MEM_NBR
DELIVERY_INMOM_MEM_NBR

Quick suggestion, alter your DDL and prefix the constraints with the constraint keyword.

😎

mw_sql_developer SSCoach Points: 19445 More actions · Answer 2

FOUND THE MISTAKE.. ALL Set ....

Select object_name(SI.object_id) as tab_name,

( Select c.name from sys.columns c where object_id = SI.object_id AND c.column_id = SIC.column_id ) as [index_col_name]

from

sys.indexes SI

inner join sys.index_columns SIC on ( SIC.object_id = SI.object_id and (SI.is_primary_key =1) )

where SI.is_primary_key =1

ORDER BY 1,2

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 3

select * from sys.columns where column_id in (

select column_id from sys.index_columns where index_id =(

select index_id from sys.indexes

where object_name(object_id)='DELIVERY_IN')

and object_name(object_id)='DELIVERY_IN'

)

and object_name(object_id)='DELIVERY_IN'

Jayanth Kurup[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

mw112009 (10/22/2015)
FOUND THE MISTAKE.. ALL Set ....
Select object_name(SI.object_id) as tab_name,
( Select c.name from sys.columns c where object_id = SI.object_id AND c.column_id = SIC.column_id ) as [index_col_name]
from
sys.indexes SI
inner join sys.index_columns SIC on ( SIC.object_id = SI.object_id and (SI.is_primary_key =1) )
where SI.is_primary_key =1
ORDER BY 1,2

Going to say it again. You really should not ORDER BY using ordinal position. You should ORDER BY using column names. What happens if the columns you are sorting on change position? Also, using column names makes your queries easier to understand.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

Lynn Pettis (10/22/2015)
mw112009 (10/22/2015)
FOUND THE MISTAKE.. ALL Set ....
Select object_name(SI.object_id) as tab_name,
( Select c.name from sys.columns c where object_id = SI.object_id AND c.column_id = SIC.column_id ) as [index_col_name]
from
sys.indexes SI
inner join sys.index_columns SIC on ( SIC.object_id = SI.object_id and (SI.is_primary_key =1) )
where SI.is_primary_key =1
ORDER BY 1,2
Going to say it again. You really should not ORDER BY using ordinal position. You should ORDER BY using column names. What happens if the columns you are sorting on change position? Also, using column names makes your queries easier to understand.

Just to make it more clear, it can be column names or column alias. The query could be rewritten like this:

Select object_name(SI.object_id) as tab_name,

( Select c.name

from sys.columns c

where object_id = SI.object_id

AND c.column_id = SIC.column_id ) as index_col_name

from sys.indexes SI

inner join sys.index_columns SIC on SIC.object_id = SI.object_id and SI.is_primary_key =1

where SI.is_primary_key =1

ORDER BY tab_name, index_col_name

Although, my personal preference would be like this:

SELECT OBJECT_SCHEMA_NAME( SI.object_id) schema_name,

object_name(SI.object_id) as tab_name,

c.name as index_col_name

FROM sys.indexes SI

JOIN sys.index_columns SIC ON SIC.object_id = SI.object_id

AND SI.index_id = SIC.index_id --You're missing this join condition.

JOIN sys.columns c ON c.object_id = SI.object_id

AND c.column_id = SIC.column_id

WHERE SI.is_primary_key =1

AND SI.is_primary_key =1

ORDER BY tab_name, index_col_name

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2