March 3, 2010 at 3:43 am
Hi folks,
I need write script that can be drop constraints from particular table. Afterwards doing some operations from that table I need to reapply those constraints. In fact I doesn't know what are constraints at present including index.
Any body can help me.. The offered solution should be compatible with SQL 2000 also.
Thanks in advance
Cheers,
Asan
March 3, 2010 at 4:07 am
If it is for particular table simplest way is-
1. Generate the script of the table which includes all keys and constraints. also include DROP statements while generating the script.
2. Drop all constraints using the drop statements from this script.
3. Make whatever changes to want to make to table and create the constraints and keys again from the create statements for the same from the scripts file
March 3, 2010 at 4:36 am
Thank u.. vidya_pande
Yes it is a particular table.
Could you tell me, how we can generate the particular table structure including the constraints through T_SQL.
cheers,
Asan
March 3, 2010 at 5:16 am
Personally, I've put PowerShell to work on this problem. Generating scripts from within TSQL can be a bit problematic. Using SMO, which is how the Management Studio works, is much easier and more powerful. I have an example where I used PowerShell to generate drops and recreates of all primary keys in a database on my blog[/url]. You can modify this pretty easily to drop all foreign keys as well.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 3, 2010 at 11:05 pm
Grant Fritchey (3/3/2010)
Personally, I've put PowerShell to work on this problem. Generating scripts from within TSQL can be a bit problematic. Using SMO, which is how the Management Studio works, is much easier and more powerful. I have an example where I used PowerShell to generate drops and recreates of all primary keys in a database on my blog[/url]. You can modify this pretty easily to drop all foreign keys as well.
thank u Grant..
But I need to do through SQL only..
Is there any way to retrieve the constraints value that has been associasted with particular column..
just hava look :
Use Northwind
go
declare @tab_name varchar(200)
set @tab_name='orders'
select
TABLE_NAME= t_obj.name,
COLUMN_NAME= c_obj.name,
CONSTRAINT_NAME = OBJECT_NAME(cons_obj.constid),
CONSTRAINT_TYPE = CASE (cons_obj.status & 0xf)
WHEN 1 THEN'PRIMARY KEY'
WHEN 2 THEN 'UNIQUE KEY'
WHEN 3 THEN 'FOREIGN KEY'
WHEN 4 THEN 'CHECK'
WHEN 5 THEN 'DEFAULT'
END,
IS_NULLABLE= c_obj.isnullable
from
sysobjects t_obj, syscolumns c_obj, sysconstraints cons_obj
where
t_obj.id=c_obj.id
and t_obj.id=cons_obj.id and c_obj.colid=cons_obj.colid
and t_obj.Id=OBJECT_ID(@tab_name)
cheers,
Asan
March 5, 2010 at 6:36 am
If you're trying to simply identify the objects, sure you can use straight TSQL, but if you're trying to generate scripts, you really need to look at some method of SMO. Phil Factor just published a chapter from an upcoming book. You might want to check it out. It could be prove helpful[/url].
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply