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 solid good backup tools for SQL Server 2000

Hi,
Anyone have use any good solid backup tools for SQL Server 2000 that
01) support SQL server 2005
02) able to encypt the backup file
03) verify that the backup file is okay as native sql backup cannot verify
the backup file that I encountered.
04) able to backup database that is online without offline the db.
05) fast in backup and restore
06) reliable
07) user friend and ease of use
Please advise and comments, thank
You may want to look at LiteSpeed:
http://www.imceda.com/LiteSpeed_Description.htm
-Sue
On Tue, 12 Jul 2005 18:09:27 +0800, "Vladimir Sim"
<fengchun@.newsgroup.nospam> wrote:

>Hi,
>Anyone have use any good solid backup tools for SQL Server 2000 that
>01) support SQL server 2005
>02) able to encypt the backup file
>03) verify that the backup file is okay as native sql backup cannot verify
>the backup file that I encountered.
>04) able to backup database that is online without offline the db.
>05) fast in backup and restore
>06) reliable
>07) user friend and ease of use
>
>Please advise and comments, thank
>
|||The native SQL Server backup command does most of that already with the
exception of 2 and to some extent 3. Number 3 will be solved in 2005 with
the use of Checksums but there are no plans to do native encryption on the
backups that I know of. There are several 3rd party tools that can do what
you want.
www.imceda.com
www.red-gate.com
www.idera.com
Andrew J. Kelly SQL MVP
"Vladimir Sim" <fengchun@.newsgroup.nospam> wrote in message
news:OMXComshFHA.1048@.tk2msftngp13.phx.gbl...
> Hi,
> Anyone have use any good solid backup tools for SQL Server 2000 that
> 01) support SQL server 2005
> 02) able to encypt the backup file
> 03) verify that the backup file is okay as native sql backup cannot verify
> the backup file that I encountered.
> 04) able to backup database that is online without offline the db.
> 05) fast in backup and restore
> 06) reliable
> 07) user friend and ease of use
>
> Please advise and comments, thank
>
|||Hi
You left out one question:
In 7 years time when you have a query from the auditors or industry
regulators, can you restore the database?
Microsoft should be able to help you, but can the 3rd party vendor promise
the same?
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Vladimir Sim" <fengchun@.newsgroup.nospam> wrote in message
news:OMXComshFHA.1048@.tk2msftngp13.phx.gbl...
> Hi,
> Anyone have use any good solid backup tools for SQL Server 2000 that
> 01) support SQL server 2005
> 02) able to encypt the backup file
> 03) verify that the backup file is okay as native sql backup cannot verify
> the backup file that I encountered.
> 04) able to backup database that is online without offline the db.
> 05) fast in backup and restore
> 06) reliable
> 07) user friend and ease of use
>
> Please advise and comments, thank
>
|||I support a tool from http://www.databk.com
the download link:
http://www.databk.com/download/ssbk.zip
|||Mike Epprecht (SQL MVP) wrote:
> Hi
> You left out one question:
> In 7 years time when you have a query from the auditors or industry
> regulators, can you restore the database?
> Microsoft should be able to help you, but can the 3rd party vendor
> promise the same?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
>
I can vouch for Quest. LiteSpeed includes a free conversion utility to
change a LiteSpeed backup to a native backup, if necessary. Not that we
don't plan on being here in 7 years...
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||What are the advantages over the built-in capabilities? We have a database
program that uses SQL2000. *That* database is what is, to me, a critical
backup. Can your program backup such a db? Can it be run remotely (from a
client workstation)?
If you want to continue a discussion off line, e-mail me and I'll give you
my work e-mail address...
Regards,
Hank Arnold
<info@.shareseek.com> wrote in message
news:1121241508.928497.235640@.g49g2000cwa.googlegr oups.com...
>I support a tool from http://www.databk.com
> the download link:
> http://www.databk.com/download/ssbk.zip
>
|||> LiteSpeed includes a free conversion utility to change a LiteSpeed backup to a native backup,
That is a smart move, David. This is one of the things I always bring up in classes when talking
about 3:rd party backup programs.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:uvcwmI8hFHA.576@.tk2msftngp13.phx.gbl...
> Mike Epprecht (SQL MVP) wrote:
>
> I can vouch for Quest. LiteSpeed includes a free conversion utility to change a LiteSpeed backup
> to a native backup, if necessary. Not that we don't plan on being here in 7 years...
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
|||Hank,
This is not intended to diss the product but from what I can see it doesn't
do anything that you can't do with the native backup command or using
Enterprise Manager. The interface may or may not be a little easier than EM
but that is usually a personal preference.
Andrew J. Kelly SQL MVP
"Hank Arnold" <rasilon@.aol.com> wrote in message
news:ehjxreEiFHA.2484@.TK2MSFTNGP15.phx.gbl...
> What are the advantages over the built-in capabilities? We have a database
> program that uses SQL2000. *That* database is what is, to me, a critical
> backup. Can your program backup such a db? Can it be run remotely (from a
> client workstation)?
> If you want to continue a discussion off line, e-mail me and I'll give you
> my work e-mail address...
> --
> Regards,
> Hank Arnold
> <info@.shareseek.com> wrote in message
> news:1121241508.928497.235640@.g49g2000cwa.googlegr oups.com...
>
|||Me neither. I was just looking at it and couldn't see what "special"
features it brought to the table. For a lot of people, making an interface
easier to use and understand can be a critical "must have" feature.
I can vouch that it took me a while (with considerable angst, I can assure
you) to get the hang of the simple task of backing up (and restoring) the
database using Enterprise Manager..... Thank God for development
servers.... ;-)
Regards,
Hank Arnold
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ehPqR6GiFHA.2412@.TK2MSFTNGP10.phx.gbl...
> Hank,
> This is not intended to diss the product but from what I can see it
> doesn't do anything that you can't do with the native backup command or
> using Enterprise Manager. The interface may or may not be a little easier
> than EM but that is usually a personal preference.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Hank Arnold" <rasilon@.aol.com> wrote in message
> news:ehjxreEiFHA.2484@.TK2MSFTNGP15.phx.gbl...
>