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 suggesstions please

I am passing a XML string from client, In the back end I
am verifying the data with constraints of the columns.
Exec sp_xml_preparedocument @.hdoc OUTPUT,@.xml
select * into #tempApps
from openxml(@.hdoc, N'//application',2)
with AP_Applications --This is the physical table
EXEC sp_xml_removedocument @.hdoc
The system will create an ApplicationId at the time of
inserting a new record. Before inserting a record I am
checking the data.
When I run the above code I am getting the following
error.
Unexpected NULL value returned for
column '[OpenXML].AppId' from the OLE DB
provider 'OpenXML'. This column cannot be NULL.
OLE DB error trace [Non-interface error: Unexpected NULL
value returned for the column: ProviderName='OpenXML',
TableName='[OpenXML]', ColumnName='AppId'].
I will appreciate you can give some suggestions.
Thanks in Advance
You cannot use a table name with an identity column in the with clause. You
have to give the with clause (without the Identity column) explicit.
Best regards
Michael
<anonymous@.discussions.microsoft.com> wrote in message
news:784a01c43121$a8d09420$a501280a@.phx.gbl...
>I am passing a XML string from client, In the back end I
> am verifying the data with constraints of the columns.
> Exec sp_xml_preparedocument @.hdoc OUTPUT,@.xml
> select * into #tempApps
> from openxml(@.hdoc, N'//application',2)
> with AP_Applications --This is the physical table
>
>
> EXEC sp_xml_removedocument @.hdoc
>
> The system will create an ApplicationId at the time of
> inserting a new record. Before inserting a record I am
> checking the data.
> When I run the above code I am getting the following
> error.
> Unexpected NULL value returned for
> column '[OpenXML].AppId' from the OLE DB
> provider 'OpenXML'. This column cannot be NULL.
> OLE DB error trace [Non-interface error: Unexpected NULL
> value returned for the column: ProviderName='OpenXML',
> TableName='[OpenXML]', ColumnName='AppId'].
> I will appreciate you can give some suggestions.
>
> Thanks in Advance
>