How to force CRUDs be handled through Stored Proc.

Hi,
If I want no one to be able to use then native select, delete, update etc..
and rather force the user to use stored procedure that I have included in th
e
server. how to do that?
Give:
the database has 2- accounts...one limited privileges account for the users
to use and one for me the owner. I want to deny usage of stored procedures.
plus I want to encrypt the procedure listing so now one can see the inside
Thank youDon't give the users permissions directly on the tables, only the stored pro
cedures. As for
encryption, create the procs using WITH ENCRYPTION (however, if someone want
s to, they can Google
for decryption and find it within a minute).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
message
news:76285543-FE12-4D61-80F9-E07A6BFDEABD@.microsoft.com...
> Hi,
> If I want no one to be able to use then native select, delete, update etc.
.
> and rather force the user to use stored procedure that I have included in
the
> server. how to do that?
> Give:
> the database has 2- accounts...one limited privileges account for the user
s
> to use and one for me the owner. I want to deny usage of stored procedures
.
> plus I want to encrypt the procedure listing so now one can see the inside
> Thank you|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:edPqteKSFHA.3788@.tk2msftngp13.phx.gbl...
> Don't give the users permissions directly on the tables, only the stored
> procedures. As for encryption, create the procs using WITH ENCRYPTION
> (however, if someone wants to, they can Google for decryption and find it
> within a minute).
Yeah, WITH ENCRYPTION tends to keep "honest people honest"|||Then how do i protect my stored procedures for listing?
"Michael C#" wrote:

> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:edPqteKSFHA.3788@.tk2msftngp13.phx.gbl...
> Yeah, WITH ENCRYPTION tends to keep "honest people honest"
>
>|||You can't.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
message
news:2505B075-0207-48EB-BA08-87C57FE3088D@.microsoft.com...
> Then how do i protect my stored procedures for listing?
> "Michael C#" wrote:
>|||Thank you all for the help,
I want to highlight that the user account is secret as well. the users must
use an application to access the db. the application has an obfuscated user
account and password that is authorized to do cruds through stored procedure
s
(assuming I denied direct access to the table) so this user account is the
only way a user can access the db. For a user to log to the SQl sever he mus
t
guess the account and password. which as securely saved/protected PKI model.
But assume he guessed the account (limited privileges) and password, and he
is now on the server. he will not be able to use the stored procedures
because I designed the procedures to take a parameter that is secret and
saved again within the application that user must use to access the
database... so the user can see the stored procedure signature I presume bu
t
have to guess the key. he is not the owner so he cant delete, and it was
saved WITH ENCRYPTION so it's encrypted and he can't see the listing and
hence see the" IF ELSE" where I check for the secret key value passed to the
procedure. Now the nightmare is that he decrypts the stored procedure...so
I have 3 questions
1- How can I protect him from opening the stored procedures?
2- Can I program the stored procedure to include check such if else
etc,,(obviously I'm novice to T-Sql)
3- can I use the e-mail mechanism from within a stored procedure to notify
me of suspicious attempts. such as when the key entered was bad. based on my
closed model on failed key attempt is too many and would trigger a
notification email.
Thank you so very much
"Tibor Karaszi" wrote:

> Don't give the users permissions directly on the tables, only the stored p
rocedures. As for
> encryption, create the procs using WITH ENCRYPTION (however, if someone wa
nts to, they can Google
> for decryption and find it within a minute).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote i
n message
> news:76285543-FE12-4D61-80F9-E07A6BFDEABD@.microsoft.com...
>
>|||1. You can't.
2. Yes. There are procedural constructs in TSQL. See for instance IF..ELSE i
n Books Online.
3. You could use xp_sendmail or xp_smtp_sendmail (better, doesn't use MAPI,
but you need to download
and install from www.sqldev.net). Or put enough info in a table and have an
outside process (like
SQL Server Agent job) regularly read this table and send emails. Or use Noti
fication Services (free
download from MS).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
message
news:CC563474-6A2B-4792-86DC-5AE5CFC0F352@.microsoft.com...
> Thank you all for the help,
> I want to highlight that the user account is secret as well. the users mus
t
> use an application to access the db. the application has an obfuscated use
r
> account and password that is authorized to do cruds through stored procedu
res
> (assuming I denied direct access to the table) so this user account is the
> only way a user can access the db. For a user to log to the SQl sever he m
ust
> guess the account and password. which as securely saved/protected PKI mode
l.
> But assume he guessed the account (limited privileges) and password, and h
e
> is now on the server. he will not be able to use the stored procedures
> because I designed the procedures to take a parameter that is secret and
> saved again within the application that user must use to access the
> database... so the user can see the stored procedure signature I presume
but
> have to guess the key. he is not the owner so he cant delete, and it was
> saved WITH ENCRYPTION so it's encrypted and he can't see the listing and
> hence see the" IF ELSE" where I check for the secret key value passed to t
he
> procedure. Now the nightmare is that he decrypts the stored procedure...so
> I have 3 questions
> 1- How can I protect him from opening the stored procedures?
> 2- Can I program the stored procedure to include check such if else
> etc,,(obviously I'm novice to T-Sql)
> 3- can I use the e-mail mechanism from within a stored procedure to notify
> me of suspicious attempts. such as when the key entered was bad. based on
my
> closed model on failed key attempt is too many and would trigger a
> notification email.
> Thank you so very much
> "Tibor Karaszi" wrote:
>|||If i cant prevemt decrymption, then my only lien of defence the is embeded
acount and password. and hope the user will never abe able to guess.
Do you have better suggestions?
"Tibor Karaszi" wrote:

> 1. You can't.
> 2. Yes. There are procedural constructs in TSQL. See for instance IF..ELSE
in Books Online.
> 3. You could use xp_sendmail or xp_smtp_sendmail (better, doesn't use MAPI
, but you need to download
> and install from www.sqldev.net). Or put enough info in a table and have a
n outside process (like
> SQL Server Agent job) regularly read this table and send emails. Or use No
tification Services (free
> download from MS).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote i
n message
> news:CC563474-6A2B-4792-86DC-5AE5CFC0F352@.microsoft.com...
>
>|||> 1- How can I protect him from opening the stored procedures?
You can't easily. The better way to get the level of security you are reques
ting
here would be to encrypt the actual data and have the stored procs merely
provide CRUD services. Thus, even if the user sees the stored proc, without
the
ability to decrypt the data itself, the stored proc by itself would useless.

> 2- Can I program the stored procedure to include check such if else
> etc,,(obviously I'm novice to T-Sql)
Not sure what you mean here.

> 3- can I use the e-mail mechanism from within a stored procedure to notify
> me of suspicious attempts. such as when the key entered was bad. based on
my
> closed model on failed key attempt is too many and would trigger a
> notification email.
> Thank you so very much
Yes but it might be trickier than you think. This can be done in the stored
procs themselves and/or in your middle layer code.
Thomas|||"Lottoman2000 NEWBE" <Lottoman2000NEWBE@.discussions.microsoft.com> wrote in
message news:7EB4CFB4-BCC6-4B7D-9204-B17B16C916E6@.microsoft.com...
> If i cant prevemt decrymption, then my only lien of defence the is embeded
> acount and password. and hope the user will never abe able to guess.
> Do you have better suggestions?
WITH ENCRYPTION keeps "honest people honest", and prevents novices from
hacking into your code, and it's not all that secure. There are just too
many tools available to decrypt SP's. Another idea might be to store your
queries internally to your application in an encrypted format and decrypt
right before execution, instead of using SP's. You'll take a performance
hit on this, however, which may or may not be negligible. This brings you
back full-circle to your original question, however, about forcing access to
tables only via SP's...sql

how to force a commit in a sp

I've a complex stored procedure, that makes a lot of insert, update,
delete and so on.

I would like to make some commits durint this sp, but of course they
are not "real" commit because who call the sp could decide for a
rollback.

But I know that this commit has to be real. In fact, the transaction
log grows really too much during the execution.

Is there a way to force a commit durint a sp ?

thank you very much!Alberto (iltrex@.libero.it) writes:
> I've a complex stored procedure, that makes a lot of insert, update,
> delete and so on.
> I would like to make some commits durint this sp, but of course they
> are not "real" commit because who call the sp could decide for a
> rollback.
> But I know that this commit has to be real. In fact, the transaction
> log grows really too much during the execution.
> Is there a way to force a commit durint a sp ?

WHILE @.@.trancount > 1
COMMIT TRANSACTION

But it would be a really bad thing to do. If the caller has started a
trasaction, he would get an error when you exit the procedure. (Unless
you are so deceivious that perform equally many BEGIN TRANSACTION.

A much better approach is to add to the beginning of the procedure:

IF @.@.trancount > 0
BEGIN
RAISERROR ('This procedure must not be called within a transaction',
16, 1)
RETURN 1
END

That assumes of course that there are no business requirements that
calls for your procedure being part of a transaction. If there is,
you will have to find other ways to address the transaction log growth.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> WHILE @.@.trancount > 1
> COMMIT TRANSACTION
> But it would be a really bad thing to do. If the caller has started a

I know. But the sp calculates data for a olap cube, and it does the
calculation in an incremental way (it can be interrupted at any time
without losing data). So you solution should be the one I'm looking
for. Now I'm going to try it!

thank you!|||Alberto (iltrex@.libero.it) writes:
>> WHILE @.@.trancount > 1
>> COMMIT TRANSACTION
>>
>> But it would be a really bad thing to do. If the caller has started a
> I know. But the sp calculates data for a olap cube, and it does the
> calculation in an incremental way (it can be interrupted at any time
> without losing data). So you solution should be the one I'm looking
> for. Now I'm going to try it!

Yeah, but the caller might have done something which cannot be
committed half-way. So I really recommend the other way:

IF @.@.trancount > 0
BEGIN
RAISERROR ('This procedure must not be called within a transaction',
16, 1)
RETURN 1
END

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

how to fire triggers during log shipping

Hi,
We are trying to impliment log shipping. During the log shipping the target database will have insert, update or delete trigger on some of it's table.
Can some one let me know will these trigger get fired during log shipping. if not the is there some way to fire these triggers.

Thanks,
manojTriggers on primary database will have no issues while Log shipping process is on, anyway the secondary server database will be in read-only mode so no affect.|||It means these triggers will never fire on secondary database.
is there any way I can make them fire.

Thanks

Originally posted by Satya
Triggers on primary database will have no issues while Log shipping process is on, anyway the secondary server database will be in read-only mode so no affect.|||Why do you want fire triggers on secondary database, as LS process will restore the transactions from primary database.|||Hi satya,

I need to explain you the scenario

We have two system with two seperate production database on two physicaly seperate servers. one of the production database is search intensive and the other is transaction intensive. There are few common tables in these two databases.

As the data in transaction intensive database changes we want to move this data to the search intensive database to keep in sync.

The client don't want replication as solution.

client is planning to implement the runtime Log shifting for failover database of Transaction intensive database.

So we want to take this opportunity to run triggers on this failover database to move data to search database. as this we think will keep the down time to zero.

any suggestions?

Regards
Manoj

Originally posted by Satya
Why do you want fire triggers on secondary database, as LS process will restore the transactions from primary database.

How to find the dependency object of a particular table

Hi all,

I m using SQL Server 2000. I want to know the Dependencies of a particular table in my database. I want to know the details inorder to delete the records which are not referenced from other related tables.

(e.g) Customer, CustomerTax, Invoice tables are there. CustomerID is primary key in Customer table. CustomerID is Foreign keys in CustomerTax and Invoice tables.

When i create new customer i enter customer tax also. So both Customer and Customertax details are stored in the tables. Once i created the customer details it not yet reference in invoice table i can deactivate(later i can again activate the customer) the customer, because it is not refered in Invoice table(eventhough it is refered in CustomerTax table). In my view Customer and CustomerTax are very close association with them.(Without client the client tax not exists).

So whenever i want to deactivate a particular customer i have to check which tables that customer ID is refereneced. If it is referenced any other tables except CustomerTax table then i will intimate the user that customer ID is already referenced by other tables(other than CustomerTax).

For this purpose i want to check the dependencies of the table and decide to deactivate the customer record or not. How can i achieve this in SQL Server 2000?

I am in urgent need for this problem. Can anyone please suggest for this problem? If so i am very greatfull for you.

Thanks in advance
cssjm

use the sysforeignkeys table itll give u the fkid, ok table and fk table...

have a look at this..will help u...

http://blogs.msdn.com/sqltips/archive/2005/07/05/435882.aspx

how to find records in database using select

I need to be able to find certain data as the user has submitted that data twice and delete one record,except that have hundreds of tables and don't know the table where she would have submitted the data, but I have some other key info that I can start with.

My question is,how do i select a record from the database if I don't know the table it comes from?

Could somebody give me an expamle please?How did they submit the data? Do you know the data submitted? You have to have something to go on.|||Can you pose the question exactly as the teacher wrote it in your assignment? Just type it verbatim into a posting, and you'll get a much better quality answer.

-PatP

How to find my rdl filesn the sql server?

Hello.
I have a few reports that I've published to my reportserver.
Now, my rdl files have been delete from my local computer.
Is there a way to find the rdl files on the server so I can download
them to my local pc so I can continue working on them?
I have full access to the report server of course (and the the sql
server itself).
I'm useing rs2005.
Thanks.RS stores the rdl in the database. There is no rdl file on the server.
However, you can get the rdl file via report manager out of the database.
Not real intuitive on how to do it however. Go to report manager, click on
the report, then the properties tab. There is an Edit link under Report
Definition. Click on that, give it a directory on your local PC. Then open
up (or create) a report project. Right click on report and add an existing
item (this is from memory). Anyway there is a right mouse click menu for
adding an existing item. It will copy it over from wherever you put it into
the directory for your project.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"nicknack" <roezohar@.gmail.com> wrote in message
news:1194968402.018507.270610@.19g2000hsx.googlegroups.com...
> Hello.
> I have a few reports that I've published to my reportserver.
> Now, my rdl files have been delete from my local computer.
> Is there a way to find the rdl files on the server so I can download
> them to my local pc so I can continue working on them?
> I have full access to the report server of course (and the the sql
> server itself).
> I'm useing rs2005.
> Thanks.
>|||You can also get the rdl file from SQL Server Management Studio. Log on to
the Reporting Services service. Go to te folde where your report is
deployed. Right-click on the report and select Edit. You will prompted to
save the rdl file somewhere on your disk.
--
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"nicknack" <roezohar@.gmail.com> wrote in message
news:1194968402.018507.270610@.19g2000hsx.googlegroups.com...
> Hello.
> I have a few reports that I've published to my reportserver.
> Now, my rdl files have been delete from my local computer.
> Is there a way to find the rdl files on the server so I can download
> them to my local pc so I can continue working on them?
> I have full access to the report server of course (and the the sql
> server itself).
> I'm useing rs2005.
> Thanks.
>

How to find my rdl filesn the sql server?

Hello.
I have a few reports that I've published to my reportserver.
Now, my rdl files have been delete from my local computer.

Is there a way to find the rdl files on the server so I can download them to my local pc so I can continue working on them?

I have full access to the report server of course (and the the sql server itself).
I'm useing rs2005.

Thanks.

Hi there - this isnt documented very well, but you can actually just re-download the report directly from the report server

browse tohttp://reportingserver/reports

replace reportingserver with the name of your server.

click a folder - then a report to view it (like you were going to run it), then click the properties tab at the top.

About half way down the page, there will be an "edit" linkbutton, under the "Report Deffinition" heading, clicking that will give you the option to download the RDL

|||

That's great to know, because the rdl files do not exist on the reports server. They are in the database. I spent hours and hours trying to figure that one out. :(

|||

Hi Freakyuno.

Thanks for the reply. I get that question in a few other forum so I guss thats the way to do it :)

I also found a nice tool to do this (from the msdn ssrs forum):
http://sqldbatips.com/showarticle.asp?ID=62

Still didn't try it out my self but I read about it and it sound like another way.

Thanks.

How to find and delete orphan users in all db on server?

Hi
I need help ;)
How to find orphan users in all databases on sql server 2000 and delete
them in one script or in any automated or semi automated way ?
I try do it using sp_MSforeachdb sp_change_users_login 'Report' but i
did't succeded. Probably i'm not experienced enough to complete this
task by myself.
Maybe you know sites with such a usfull scripts ?
Thanks
M.
See, if the queries from my article help:
http://vyaskn.tripod.com/troubleshoo...phan_users.htm
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"marta" <grupy_d@.go2.pl> wrote in message
news:1123684712.782296.155920@.g47g2000cwa.googlegr oups.com...
> Hi
> I need help ;)
> How to find orphan users in all databases on sql server 2000 and delete
> them in one script or in any automated or semi automated way ?
> I try do it using sp_MSforeachdb sp_change_users_login 'Report' but i
> did't succeded. Probably i'm not experienced enough to complete this
> task by myself.
> Maybe you know sites with such a usfull scripts ?
> Thanks
> M.
>

How to find and delete orphan users in all db on server?

Hi
I need help ;)
How to find orphan users in all databases on sql server 2000 and delete
them in one script or in any automated or semi automated way ?
I try do it using sp_MSforeachdb sp_change_users_login 'Report' but i
did't succeded. Probably i'm not experienced enough to complete this
task by myself.
Maybe you know sites with such a usfull scripts ?
Thanks
M.See, if the queries from my article help:
http://vyaskn.tripod.com/troubleshooting_orphan_users.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"marta" <grupy_d@.go2.pl> wrote in message
news:1123684712.782296.155920@.g47g2000cwa.googlegroups.com...
> Hi
> I need help ;)
> How to find orphan users in all databases on sql server 2000 and delete
> them in one script or in any automated or semi automated way ?
> I try do it using sp_MSforeachdb sp_change_users_login 'Report' but i
> did't succeded. Probably i'm not experienced enough to complete this
> task by myself.
> Maybe you know sites with such a usfull scripts ?
> Thanks
> M.
>

How to find and delete orphan users in all db on server?

Hi
I need help ;)
How to find orphan users in all databases on sql server 2000 and delete
them in one script or in any automated or semi automated way ?
I try do it using sp_MSforeachdb sp_change_users_login 'Report' but i
did't succeded. Probably i'm not experienced enough to complete this
task by myself.
Maybe you know sites with such a usfull scripts ?
Thanks
M.See, if the queries from my article help:
http://vyaskn.tripod.com/troublesho...rphan_users.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"marta" <grupy_d@.go2.pl> wrote in message
news:1123684712.782296.155920@.g47g2000cwa.googlegroups.com...
> Hi
> I need help ;)
> How to find orphan users in all databases on sql server 2000 and delete
> them in one script or in any automated or semi automated way ?
> I try do it using sp_MSforeachdb sp_change_users_login 'Report' but i
> did't succeded. Probably i'm not experienced enough to complete this
> task by myself.
> Maybe you know sites with such a usfull scripts ?
> Thanks
> M.
>

how to find all the FKs of a table?

I need to delete all the rows of a table, but the table has FK and I get this
error:
Cannot truncate table 'xxx' because it is being referenced by a FOREIGN KEY
constraint.
So first I want to delete all the FKs. How can I know which FKs were set in
this table? I have found something in the Diagram section, but can I list all
the FKs with a command?
Thanks
Hi
You can look at the INFORMATION_SCHEMA views such as
select *
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE C
WHERE C.TABLE_SCHEMA = 'dbo'
AND C.TABLE_NAME = 'MyTable'
John
"Francesco" <Francesco@.discussions.microsoft.com> wrote in message
news:6DEDF425-3C07-4125-9FAB-465A46CFE60B@.microsoft.com...
>I need to delete all the rows of a table, but the table has FK and I get
>this
> error:
> Cannot truncate table 'xxx' because it is being referenced by a FOREIGN
> KEY
> constraint.
> So first I want to delete all the FKs. How can I know which FKs were set
> in
> this table? I have found something in the Diagram section, but can I list
> all
> the FKs with a command?
> Thanks
|||from an old post:
if object_id('usp_findreferences'X,'p') is not null
drop proc usp_findreferences
go
create procedure usp_findreferences
/*****************************X******************** **********X**********/
/* Purpose: A quick & dirty way to find ref. objects for a[ll] table[s] */
/* Author: OJ Ngo */
/* Date: 02/28/2002 */
/*****************************X******************** **********X**********/
@.tbname sysname=null
as
set nocount on
Print 'Referenced:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referenced_parent_table=c2.tabXle_name,
c2.column_name
from information_schema.constraint_Xcolumn_usage c1 join
information_schema.referentialX_constraints r on
c1.constraint_name=r.constrainXt_name
join information_schema.constraint_Xcolumn_usage c2 on
r.unique_constraint_name=c2.coXnstraint_name
where c1.table_name=coalesce(@.tbnameX,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
print ''
print 'Referencing:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referencing_child_table=c2.tabXle_name,
c2.column_name
from information_schema.constraint_Xcolumn_usage c1 join
information_schema.referentialX_constraints r on
c1.constraint_name=r.unique_coXnstraint_name
join information_schema.constraint_Xcolumn_usage c2 on
r.constraint_name=c2.constrainXt_name
where c1.table_name=coalesce(@.tbnameX,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
go
--test run
exec usp_findreferences 'Orders'
-oj
"Francesco" <Francesco@.discussions.microsoft.com> wrote in message
news:6DEDF425-3C07-4125-9FAB-465A46CFE60B@.microsoft.com...
>I need to delete all the rows of a table, but the table has FK and I get
>this
> error:
> Cannot truncate table 'xxx' because it is being referenced by a FOREIGN
> KEY
> constraint.
> So first I want to delete all the FKs. How can I know which FKs were set
> in
> this table? I have found something in the Diagram section, but can I list
> all
> the FKs with a command?
> Thanks

how to find all the FKs of a table?

I need to delete all the rows of a table, but the table has FK and I get this
error:
Cannot truncate table 'xxx' because it is being referenced by a FOREIGN KEY
constraint.
So first I want to delete all the FKs. How can I know which FKs were set in
this table? I have found something in the Diagram section, but can I list all
the FKs with a command?
ThanksHi
You can look at the INFORMATION_SCHEMA views such as
select *
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE C
WHERE C.TABLE_SCHEMA = 'dbo'
AND C.TABLE_NAME = 'MyTable'
John
"Francesco" <Francesco@.discussions.microsoft.com> wrote in message
news:6DEDF425-3C07-4125-9FAB-465A46CFE60B@.microsoft.com...
>I need to delete all the rows of a table, but the table has FK and I get
>this
> error:
> Cannot truncate table 'xxx' because it is being referenced by a FOREIGN
> KEY
> constraint.
> So first I want to delete all the FKs. How can I know which FKs were set
> in
> this table? I have found something in the Diagram section, but can I list
> all
> the FKs with a command?
> Thanks|||from an old post:
if object_id('usp_findreferences'­,'p') is not null
drop proc usp_findreferences
go
create procedure usp_findreferences
/*****************************­******************************­**********/
/* Purpose: A quick & dirty way to find ref. objects for a[ll] table[s] */
/* Author: OJ Ngo */
/* Date: 02/28/2002 */
/*****************************­******************************­**********/
@.tbname sysname=null
as
set nocount on
Print 'Referenced:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referenced_parent_table=c2.tab­le_name,
c2.column_name
from information_schema.constraint_­column_usage c1 join
information_schema.referential­_constraints r on
c1.constraint_name=r.constrain­t_name
join information_schema.constraint_­column_usage c2 on
r.unique_constraint_name=c2.co­nstraint_name
where c1.table_name=coalesce(@.tbname­,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
print ''
print 'Referencing:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referencing_child_table=c2.tab­le_name,
c2.column_name
from information_schema.constraint_­column_usage c1 join
information_schema.referential­_constraints r on
c1.constraint_name=r.unique_co­nstraint_name
join information_schema.constraint_­column_usage c2 on
r.constraint_name=c2.constrain­t_name
where c1.table_name=coalesce(@.tbname­,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
go
--test run
exec usp_findreferences 'Orders'
-oj
"Francesco" <Francesco@.discussions.microsoft.com> wrote in message
news:6DEDF425-3C07-4125-9FAB-465A46CFE60B@.microsoft.com...
>I need to delete all the rows of a table, but the table has FK and I get
>this
> error:
> Cannot truncate table 'xxx' because it is being referenced by a FOREIGN
> KEY
> constraint.
> So first I want to delete all the FKs. How can I know which FKs were set
> in
> this table? I have found something in the Diagram section, but can I list
> all
> the FKs with a command?
> Thanks

how to find all the FKs of a table?

I need to delete all the rows of a table, but the table has FK and I get thi
s
error:
Cannot truncate table 'xxx' because it is being referenced by a FOREIGN KEY
constraint.
So first I want to delete all the FKs. How can I know which FKs were set in
this table? I have found something in the Diagram section, but can I list al
l
the FKs with a command?
ThanksHi
You can look at the INFORMATION_SCHEMA views such as
select *
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE C
WHERE C.TABLE_SCHEMA = 'dbo'
AND C.TABLE_NAME = 'MyTable'
John
"Francesco" <Francesco@.discussions.microsoft.com> wrote in message
news:6DEDF425-3C07-4125-9FAB-465A46CFE60B@.microsoft.com...
>I need to delete all the rows of a table, but the table has FK and I get
>this
> error:
> Cannot truncate table 'xxx' because it is being referenced by a FOREIGN
> KEY
> constraint.
> So first I want to delete all the FKs. How can I know which FKs were set
> in
> this table? I have found something in the Diagram section, but can I list
> all
> the FKs with a command?
> Thanks|||from an old post:
if object_id('usp_findreferences'_,'p') is not null
drop proc usp_findreferences
go
create procedure usp_findreferences
/ *****************************_**********
********************_**********/
/* Purpose: A quick & dirty way to find ref. objects for a[ll] table
1;s] */
/* Author: OJ Ngo */
/* Date: 02/28/2002 */
/ *****************************_**********
********************_**********/
@.tbname sysname=null
as
set nocount on
Print 'Referenced:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referenced_parent_table=c2.tab_le_name,
c2.column_name
from information_schema.constraint__column_usage c1 join
information_schema.referential__constraints r on
c1.constraint_name=r.constrain_t_name
join information_schema.constraint__column_usage c2 on
r.unique_constraint_name=c2.co_nstraint_name
where c1.table_name=coalesce(@.tbname_,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
print ''
print 'Referencing:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referencing_child_table=c2.tab_le_name,
c2.column_name
from information_schema.constraint__column_usage c1 join
information_schema.referential__constraints r on
c1.constraint_name=r.unique_co_nstraint_name
join information_schema.constraint__column_usage c2 on
r.constraint_name=c2.constrain_t_name
where c1.table_name=coalesce(@.tbname_,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
go
--test run
exec usp_findreferences 'Orders'
-oj
"Francesco" <Francesco@.discussions.microsoft.com> wrote in message
news:6DEDF425-3C07-4125-9FAB-465A46CFE60B@.microsoft.com...
>I need to delete all the rows of a table, but the table has FK and I get
>this
> error:
> Cannot truncate table 'xxx' because it is being referenced by a FOREIGN
> KEY
> constraint.
> So first I want to delete all the FKs. How can I know which FKs were set
> in
> this table? I have found something in the Diagram section, but can I list
> all
> the FKs with a command?
> Thanks