November 1, 2005 at 4:18 am
Hi,
Can someone help me with the following:
I was querying the system tabeles to retrieve information aboet the primary key and the foreign keys.
What I want is something like:
pk_name, table_name, pk_column
----------------------------------------
PK_Order, Order, OrderId
PK_OrderDetail, OrderDetail, OrderId
PK_OrderDetail, OrderDetail, OrderLineId
And same for foreign key:
fk_name, table_name_1, column_name_1, table_name_2, column_name_2
-----------------------------------------
FK_OrderShippingAddress, Order, OrderShippingAddress, Address, AddressId
cheers,
Jan
November 1, 2005 at 5:52 am
Have you tried sp_pkeys and sp_fkeys? You could easily insert the output into a temp table and do anything you wish from there. The output is very useful as it is. You can find more information in Books On Line.
Good Luck
November 1, 2005 at 7:47 am
Thanks,
I knew about those sp's but let me give you a little bit more background information:
For our web application we want to build some web pages to view information about the db (first step is tables, columns and keys).
What is important for our customers is that they want to compare the current db with older versions.
So I need to store all the data definition data in tables including a version number. So I can not use the sp's mentioned...
Jan
November 8, 2005 at 8:59 am
Something like this ??
select g.name, d.name as 'IndexName', e.indid as 'IndexID', e.keyno as 'KeyFldSeq', f.name as 'FieldName'
from TI..sysindexes d
join TI..sysindexkeys e on d.id = e.id and d.indid = e.indid
join TI..syscolumns f on f.id = d.id and f.colid = e.colid
join sysobjects g on g.id = d.id
--where g.name = 'TableName' -- << ------- Optional TABLE NAME HERE
and d.name not like '_WA_Sys%'
order by g.name, e.indid, e.keyno
(This is the SQL 2005 forum, but I've only used this on SQL 2000)
November 8, 2005 at 10:47 am
For Primary Keys,
SELECT COLS.CONSTRAINT_NAME,COLS.COLUMN_NAME,COLS.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS COLS
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS CONS ON
COLS.CONSTRAINT_NAME = CONS.CONSTRAINT_NAME
WHERE COLS.CONSTRAINT_CATALOG = DB_NAME()
AND COLS.TABLE_NAME = 'XXXX'
AND CONS.CONSTRAINT_TYPE = 'PRIMARY KEY'
ORDER BY COLS.CONSTRAINT_NAME, COLS.ORDINAL_POSITION
GO
For Foreign keys, the columns association etc.:
SELECT (CASE
WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'ENABLED'
ELSE 'DISABLED'
END) AS STATUS,
OBJECT_NAME(CONSTID) AS CONSTRAINT_NAME,
OBJECT_NAME(FKEYID) AS TABLE_NAME,
COL_NAME(FKEYID, FKEY) AS COLUMN_NAME,
OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME,
COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME
FROM SYSFOREIGNKEYS
ORDER BY TABLE_NAME, CONSTRAINT_NAME,REFERENCED_TABLE_NAME, KEYNO
GO
For a list of tables, columns, data-types, Nullability checks:
SELECT A.TABLE_NAME, B.COLUMN_NAME, B.DATA_TYPE, B.IS_NULLABLE
FROM INFORMATION_SCHEMA.TABLES A, INFORMATION_SCHEMA.COLUMNS B
WHERE A.TABLE_NAME = B.TABLE_NAME
ORDER BY A.TABLE_NAME
GO
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply