alter table MyTable with check check constraint all

Hi

Is it correct to use the following statement :

alter table MyTable with check check constraint all

to reenable all the foreign keys and check constraints on a table after a bulk load ?

best regards,

Thibaut Barrère

(follow-up from this on the ssis forum, but it's more a tsql question, hence my post here).Here goes nothing...

-- Disable all constraints for a given table
ALTER TABLE [Some_Table] NOCHECK CONSTRAINT ALL

-- Enable all constraints for a given table
ALTER TABLE [Some_Table] CHECK CONSTRAINT ALL

-- Reseed the identity of a table back to zero (next record will have auto ident set to 1)
DBCC CHECKIDENT ([Some_Table], RESEED, 0)

-- "Run" table constraints on a table to verify that everything is ok
DBCC CHECKCONSTRAINTS ('[Some_Table]')|||

yes.. You can do this..

Advantage for disabling the constraint on bulk load is to increase the performance..

But if any data conflicts with your constraint you can't reenable it again..

|||Hi

the SQL server destination in SSIS disables the constraints itself when the check constraints checkbox is disabled.

Running the alter with check check all seems to work just fine, and properly detects any error.

Thanks for your answers!

Thibautsql

all tablenames having a specific column

Hi
Is there a way to get a list of all tables having a specific tablename
Ex. Give me all tables names having a column name 'customer_id'
Kind Regards
RoelHi,
select object_name(id) , name from syscolumns where name = 'customer_id'
Thanks
Hari
MCDBA
"Roel" <rvdbrand@.tycoint.com> wrote in message
news:#nWOtpa6DHA.2472@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Hi
> Is there a way to get a list of all tables having a specific tablename
> Ex. Give me all tables names having a column name 'customer_id'
> Kind Regards
> Roel
>

---

|||SELECT c.table_name
FROM information_schema.columns c
INNER JOIN information_schema.tables t
ON c.table_name = t.table_name
WHERE t.table_type = 'BASE TABLE'
AND c.column_name = 'customer_id'
Jacco Schalkwijk
SQL Server MVP
"Roel" <rvdbrand@.tycoint.com> wrote in message
news:%23nWOtpa6DHA.2472@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Hi
> Is there a way to get a list of all tables having a specific tablename
> Ex. Give me all tables names having a column name 'customer_id'
> Kind Regards
> Roel
>

---

|||Syscolumns not only includes the columns in each table, but also columns for
views and functions and parameters of stored procedures and functions.
What you want is:
select object_name(id) AS table_name , name from syscolumns where name =
'customer_id'
AND OBJECTPROPERTY(id, 'IsUserTable') = 1
but using the information_schema views (see my other post) is simpler.
Jacco Schalkwijk
SQL Server MVP
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uPj021a6DHA.1664@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Hi,
> select object_name(id) , name from syscolumns where name = 'customer_id'
> Thanks
> Hari
> MCDBA
> "Roel" <rvdbrand@.tycoint.com> wrote in message
> news:#nWOtpa6DHA.2472@.TK2MSFTNGP10.phx.gbl...
>

---

|||SELECT SO.Name
FROM SysObjects SO INNER JOIN SysColumns SC
ON SO.ID = SC.ID
WHERE (
SO.XType = 'U'
AND SC.Name = 'YourColumnName'
)
Cheers,
James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures|||thx
all queyies have the same output
Regards
"Roel" <rvdbrand@.tycoint.com> wrote in message
news:#nWOtpa6DHA.2472@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Hi
> Is there a way to get a list of all tables having a specific tablename
> Ex. Give me all tables names having a column name 'customer_id'
> Kind Regards
> Roel
>

---