Anyone has the script to verify the constraints and indexes?

Anyone has the script to verify the constraints and
indexes? Thanks.
Bill
Can you provide some more details? What do you mean by verify?
Anith
|||Thanks for the reply. I mean how to find out the primary
key and foreign key information from a database.
|||For a single table you can use the system procedures sp_pkeys & sp_fkeys to
find out the primary keys & foriegn keys respectively.
To get the list of all the primary keys in a database, you can use the
INFORMATION_SCHEMA views or system tables, perhaps with a couple of
meta-data functions. For instance, to get the list of foriegn keys you could
do:
SELECT s4.name AS "FK Name",
s3.name AS "Referencing Table",
s6.name AS "Referencing Column",
s1.name AS "Referenced Table",
s5.name AS "Referenced Column"
FROM sysobjects s1
INNER JOIN sysforeignkeys s2
ON s1.id = s2.rkeyid
INNER JOIN sysobjects s3
ON s3.id = s2.fkeyid
INNER JOIN sysobjects s4
ON s4.id = s2.constid
INNER JOIN syscolumns s5
ON s5.id = s1.id AND s2.rkey = s5.colid
INNER JOIN syscolumns s6
ON s3.id = s6.id AND s2.fkey = s6.colid ;
A simpler approach woule be to use the system table sysforeignkeys like :
SELECT OBJECT_NAME( constid ) AS "FK Name",
OBJECT_NAME( fkeyid ) AS "Referencing table",
COL_NAME( fkeyid, fkey ) AS "Referencing column",
OBJECT_NAME( rkeyid ) AS "Referenced Table",
COL_NAME( rkeyid, rkey ) AS "Referenced Column"
FROM sysforeignkeys ;
Similarly to get the primary keys, you could do something like:
SELECT TABLE_NAME,
CONSTRAINT_NAME AS "PK Name",
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
-- AND TABLE_SCHEMA = 'dbo';
Or there are several ways to do this as well using the system tables
directly. You can download a copy of the system table map from :
http://www.microsoft.com/sql/techinf.../systables.asp
Also, you can get a copy of the INFORMATION_SCHEMA view maps from:
http://www.dbmaint.com/download/info_schema/
Anith