Calling Database Guru's

[mtkr]

idi kuda wrkng







SELECT
kcu.TABLE_SCHEMA AS 'Schema',
kcu.TABLE_NAME AS 'Table',
kcu.CONSTRAINT_NAME AS 'Constraint Name',
kcu.COLUMN_NAME AS 'Column'
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE as kcu
ON kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEma
AND kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
AND kcu.TABLE_SCHEMA = tc.TABLE_SCHEMA
AND kcu.TABLE_NAME = tc.TABLE_NAME
WHERE
tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
ORDER BY
kcu.TABLE_SCHEMA, kcu.TABLE_NAME, kcu.CONSTRAINT_NAME

[Silver_mani]

[quote name='mtkr' timestamp='1339551534' post='1301958875']
SQL lo eee script wrkng mama......



SELECT i.name AS IndexName,
OBJECT_NAME(ic.OBJECT_ID) AS TableName,
COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.OBJECT_ID = ic.OBJECT_ID
AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1
[/quote]


kool mama .. nenu ade chesa db2 lo syscolumns ni sysindexes tho join cheste primary indexes istundi ..... i was looking for a easy one

[Silver_mani]

[quote name='mtkr' timestamp='1339551675' post='1301958882']
idi kuda wrkng







SELECT
kcu.TABLE_SCHEMA AS 'Schema',
kcu.TABLE_NAME AS 'Table',
kcu.CONSTRAINT_NAME AS 'Constraint Name',
kcu.COLUMN_NAME AS 'Column'
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE as kcu
ON kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEma
AND kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
AND kcu.TABLE_SCHEMA = tc.TABLE_SCHEMA
AND kcu.TABLE_NAME = tc.TABLE_NAME
WHERE
tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
ORDER BY
kcu.TABLE_SCHEMA, kcu.TABLE_NAME, kcu.CONSTRAINT_NAME
[/quote]


Gp mama

[mtkr]

[quote name='Silver_mani' timestamp='1339551725' post='1301958884']


kool mama .. nenu ade chesa db2 lo syscolumns ni sysindexes tho join cheste primary indexes istundi ..... i was looking for a easy one
[/quote]


idi


SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc IN ('FOREIGN_KEY_CONSTRAINT','PRIMARY_KEY_CONSTRAINT')
GO

[Silver_mani]

[quote name='mtkr' timestamp='1339551972' post='1301958894']


idi


SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc IN ('FOREIGN_KEY_CONSTRAINT','PRIMARY_KEY_CONSTRAINT')
GO
[/quote]


mama where condition lo 'FOREIGN_KEY_CONSTRAINT' enduku mama ... artham lakedu ?

[vissu]

[quote name='Silver_mani' timestamp='1339551770' post='1301958887']



Gp mama
[/quote]

You can query SYSCAT.TABCONST

SELECT *
FROM syscat.tabconst
WHERE type = 'P'

[Silver_mani]

[quote name='vissu' timestamp='1339552237' post='1301958909']
You can query SYSCAT.TABCONST

SELECT *
FROM syscat.tabconst
WHERE type = 'P'
[/quote]


idi endulo mama ? SQL or DB2 ?

[mtkr]

[quote name='Silver_mani' timestamp='1339552050' post='1301958900']


mama where condition lo 'FOREIGN_KEY_CONSTRAINT' enduku mama ... artham lakedu ?
[/quote]
foreign key kosam kuda searching mama e script thooo...

[vissu]

[quote name='Silver_mani' timestamp='1339552294' post='1301958914']



idi endulo mama ? SQL or DB2 ?
[/quote]
2

[mtkr]

[quote name='vissu' timestamp='1339552408' post='1301958922']
DB2
[/quote]

[Silver_mani]

[quote name='vissu' timestamp='1339552408' post='1301958922']
DB2
[/quote]
db2 naa ? ...thanks mama .. i ll try ?

[Silver_mani]

[quote name='mtkr' timestamp='1339552396' post='1301958920']
foreign key kosam kuda searching mama e script thooo...
[/quote]


adi avasaram ledu mama naaku

[Silver_mani]

[quote name='vissu' timestamp='1339552237' post='1301958909']
You can query SYSCAT.TABCONST

SELECT *
FROM syscat.tabconst
WHERE type = 'P'
[/quote]

i think its a view mama ...i dont think it will let me do an order by

[vissu]

> SELECT * FROM SYSCAT.COLUMNS where KeySeq = 1;

[mtkr]

mama ggle chesthe ivi dorikinaiii db2 ki...


[color=#333333][font=arial][size=3]Primary key [/size][/font][/color]

[color=#333333][font=arial][size=3]SELECT NAME [/size][/font][/color]
[color=#333333][font=arial][size=3]FROM [/size][/font][/color]
[color=#333333][font=arial][size=3]SYSIBM.SYSCOLUMNS [/size][/font][/color]
[color=#333333][font=arial][size=3]WHERE TBNAME = 'table name' [/size][/font][/color]
[color=#333333][font=arial][size=3]AND TBCREATOR = 'creator' [/size][/font][/color]
[color=#333333][font=arial][size=3]AND KEYSEQ > 0 [/size][/font][/color]
[color=#333333][font=arial][size=3]ORDER BY KEYSEQ ASC [/size][/font][/color]

[color=#333333][font=arial][size=3]Foreign Key [/size][/font][/color]

[color=#333333][font=arial][size=3]SELECT B.REFTBNAME AS PARENTTABLE,COLNAME [/size][/font][/color]
[color=#333333][font=arial][size=3]FROM [/size][/font][/color]
[color=#333333][font=arial][size=3]SYSIBM.SYSFOREIGNKEYS A, [/size][/font][/color]
[color=#333333][font=arial][size=3]SYSIBM.SYSRELS B [/size][/font][/color]
[color=#333333][font=arial][size=3]WHERE A.RELNAME = B.RELNAME [/size][/font][/color]
[color=#333333][font=arial][size=3]AND B.TBNAME = 'table name' [/size][/font][/color]
[color=#333333][font=arial][size=3]AND B.REFTBCREATOR = 'creator' [/size][/font][/color]
[color=#333333][font=arial][size=3]AND B.REFTBCREATOR = A.CREATOR [/size][/font][/color]



[color=#333333][font=arial][size=3]select tbname ,pkcolnames,fkcolnames from sysibm.sysrels [/size][/font][/color]