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

Anyone has the script to verify the constraints and indexes?

Anyone has the script to verify the constraints and
indexes? Thanks.
BillCan 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/techinfo/productdoc/2000/systables.asp
Also, you can get a copy of the INFORMATION_SCHEMA view maps from:
http://www.dbmaint.com/download/info_schema/
--
Anith

any way to check for out of date stats or indexes

Is there any way to check for stats or indexes being out of date ? Using SQL
server 2000STATS_DATE
Returns the date that the statistics for the specified index were last
updated.
Syntax
STATS_DATE ( table_id , index_id )
Also DBCC SHOW_STATISTICS ( table , target )
See BOL for details
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O8pAMwRcDHA.616@.TK2MSFTNGP11.phx.gbl...
Is there any way to check for stats or indexes being out of date ? Using SQL
server 2000

Any special way to create an index

Hi All,
I am not very experienced in creating indexes. Is there a preferred way
one should follow. I created come indexes using EM. When I do that, and
hit save on the table, does the SQL server at that point generate the
indexes?
Thanks for your comments/help.
*** Sent via Developersdex http://www.examnotes.net ***yes.
Or you can run a script from the query analyzer.
CREATE INDEX MyNewIndex ON TableName (ColumnName)
this is a very simple example. Look up "Create Index" in books on line (BOL)
for more detailed explanation
Greg Jackson
PDX, Oregon|||For what it's worth, within the Enterprise Manager's table designer, after
adding entries for your indexes, you can click the "save change script"
button instead of the "save" button, and it will display a dialog with the
SQL scripts that would be applied. You may want to save off the script, if
you are going to re-apply the indexes on other occasions.
"Vik Mohindra" <vikmohindra@.hotmail.com> wrote in message
news:Oq6yebVXFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I am not very experienced in creating indexes. Is there a preferred way
> one should follow. I created come indexes using EM. When I do that, and
> hit save on the table, does the SQL server at that point generate the
> indexes?
> Thanks for your comments/help.
> *** Sent via Developersdex http://www.examnotes.net ***