May 28, 2011 at 10:05 pm
this is my first cursor script and it's almost working, but it breaks at the point where I try to execute the following SQL statement:
ALTER TABLE @Table_Name DROP @default
the purpose of this script is to retrieve a known list of tables from sys.sysobjects, and using a cursor, cycle through each table to drop its' default constraint. However, the script is failing where the ALTER statement begins, with the following error:
Msg 102, Level 15, State 1, Line 57
Incorrect syntax near '@Table_Name'.
If I uncomment out the print @default statement, it returns a list of default constraints, so i know the problem is with the ALTER statement. Please advise. Thanks.
Script below:
DECLARE @TableName varchar(max)
DECLARE getTableName CURSOR FOR
select ss.name + '.' + o.name as table_name
from sys.objects o
join sys.schemas ss on ss.schema_id = o.schema_id
where type = 'u' and
ss.name + '.' + o.name in
('csmr.Exec'
,'csmr.LOL'
,'csmr.MedSub'
,'csmr.MedType'
,'csmr.Offer'
,'csmr.Prod'
,'csmr.ProdGroup'
,'csmr.ProdSub'
,'csmr.SupLevel'
,'css.Exec'
,'css.LOL'
,'css.MedSub'
,'css.Med'
,'css.Offer'
,'css.Prod'
,'css.ProdGroup'
,'css.ProdSubtype'
,'css.SupLevel'
,'ops.ActDetails'
,'ops.CatProgram'
,'ops.Location'
,'ops.IType'
,'ops.LOB'
,'ops.Region'
,'ops.Role'
,'ops.SS');
OPEN getTableName
FETCH NEXT
FROM getTableName INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
--print @TableName
declare @default varchar(max)
declare @table_Name varchar(max)
declare @columnname varchar(max)
set @table_name = @TableName
set @columnname = 'IsActive'
SELECT @default= s.name --default name
FROM sys.sysobjects s
join sys.syscolumns c ON s.parent_obj = c.id
WHERE s.xtype = 'd'
and c.cdefault = s.id
and parent_obj= OBJECT_ID(@TableName)
and c.name =@columnname
---print @default
ALTER TABLE @Table_Name DROP @default
FETCH NEXT
FROM getTableName INTO @TableName
END
CLOSE getTableName
DEALLOCATE getTableName
May 28, 2011 at 10:55 pm
You need to use dynamic sql. Start by looking that up in Books Online.
May 28, 2011 at 11:08 pm
That's a pretty broad topic, can you be a little more specific about what I'm looking for? It would be helpful also, if you explained whether that will help me specifically, or whether i will have to throw out the whole script and start again, etc.
May 29, 2011 at 12:12 am
Start by reading about dynamic sql. See how it is written and how it can be used. Once you understand the concepts you can then ask better questions as to what is next.
Actually, you are very close to what you need.
May 29, 2011 at 5:25 am
hxkresl (5/28/2011)
That's a pretty broad topic, can you be a little more specific about what I'm looking for? It would be helpful also, if you explained whether that will help me specifically, or whether i will have to throw out the whole script and start again, etc.
You'll want to use sp_executesql.
It will help you specifically.
You'll have to modify one line, and add two others.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 29, 2011 at 5:28 am
BTW, this (an administrative task) is actually one of the few good uses of a cursor. If you find yourself going to a cursor for other data retrieval operations, STOP. Ask for help with doing a set-based solution for what you're doing. Essentially, just forget that T-SQL has support for the WHILE statement.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 29, 2011 at 6:26 am
Just got this. Appreciate it. I have read that performance-wise, because cursors operate row by row, that cursor based solutions should be used with care, yet because it is an administrative task, involving cutting and pasting the default constraint for each of the 22 tables, I wanted to make it easier, and I wanted to learn programmatic logic....so thank you for pointing me in a specific direction. It is very helpful for me to start learning about a broad topic from a specific focal point and then apply the learning iteratively to more and more situations.
Thank you!!!!
May 30, 2011 at 3:38 am
check this....
-----------------
DECLARE @TableName varchar(max)
DECLARE getTableName CURSOR FOR
select ss.name + '.' + o.name as table_name
from sys.objects o
join sys.schemas ss on ss.schema_id = o.schema_id
where type = 'u' and
ss.name + '.' + o.name in
('csmr.Exec'
,'csmr.LOL'
,'csmr.MedSub'
,'csmr.MedType'
,'csmr.Offer'
,'csmr.Prod'
,'csmr.ProdGroup'
,'csmr.ProdSub'
,'csmr.SupLevel'
,'css.Exec'
,'css.LOL'
,'css.MedSub'
,'css.Med'
,'css.Offer'
,'css.Prod'
,'css.ProdGroup'
,'css.ProdSubtype'
,'css.SupLevel'
,'ops.ActDetails'
,'ops.CatProgram'
,'ops.Location'
,'ops.IType'
,'ops.LOB'
,'ops.Region'
,'ops.Role'
,'ops.SS');
OPEN getTableName
FETCH NEXT
FROM getTableName INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
--print @TableName
declare @default varchar(max)
declare @table_Name varchar(max)
declare @columnname varchar(max)
-- added
declare @sql_command varchar(max)
set @table_name = @TableName
set @columnname = 'IsActive'
SELECT @default= s.name --default name
FROM sys.sysobjects s
join sys.syscolumns c ON s.parent_obj = c.id
WHERE s.xtype = 'd'
and c.cdefault = s.id
and parent_obj= OBJECT_ID(@TableName)
and c.name =@columnname
---print @default
--added
set @sql_command = 'ALTER TABLE '+ @Table_Name+ ' DROP constraint '+@default
execute @sql_command
-- added
FETCH NEXT
FROM getTableName INTO @TableName
END
CLOSE getTableName
DEALLOCATE getTableName
Thanks & Regards
Siva Kumar J.
May 30, 2011 at 10:02 am
Thank you Siva! I had been working on it and reading about Dynamic SQL for hours yesterday. I'm sure that there will be benefits from doing so but I still hadn't gotten my script working.
Looking at this, I see quotes around the SQL Clauses which I never did....I am going to try this out in a few minutes. Will let you know how it went. Really appreciate it.
May 30, 2011 at 11:01 am
So, my statement was very similar, although I was using sp_executesql. (I believe this is interchangeable with EXEC). Also, I believe the word CONSTRAINT is not needed in SQL Server 2008. What I had not done, was put the SQL portion of the statement into quotes, which I can see is improving the outcome.
Now:
DECLARE @sql nvarchar(4000)
SET @sql = 'ALTER TABLE ' + @Table_Name + 'DROP ' + @default
EXEC (@sql)
The error I am getting now suggests that there persists a syntactical error. The @default variable is retrieving all the default constraints as expected but not dropping, getting following error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'DF_ProductGR_IsAct_432K32K'.....and so on for each of the 22 Default constraints.
Can you sneak a suggestion? Thanks.
May 30, 2011 at 11:07 am
ooops, I didn't put a space after the quote that preceded the clause DROP. Now I am getting different error that has to do with the fact that one of the tables doesn't have a constraint to drop, so I need to be able to handle that.
Thanks.
May 30, 2011 at 11:35 am
Yees, that's it.
Am I still dealing with Dynamic SQL when I need to add conditional logic so that
1. after the default constraint is dropped, a second ALTER statement changes the datatype
2. after the datatype is changed, a third ALTER statement re adds the default constraint.
Ie.
DECLARE @sql1 nvarchar(4000)
SET @sql1 = 'ALTER TABLE '+ @Table_Name + ' DROP ' + @default
EXEC(@sql)
DECLARE @sql2 nvarchar(4000)
SET @sql2 = 'ALTER TABLE '+ @Table_Name + ' ALTER COLUMN IsActive bit not null'
EXEC(@sql2)
DECLARE @sql3 nvarchar(4000)
SET @sql3 = 'ALTER TABLE '+ @Table_Name + ' ADD DEFAULT ((1)) FOR IsActive'
EXEC(@sql3)
At this point the task will be done. Is the solution just a matter of finding which data flow construct to use to complete the Dynamic SQL statement?
May 30, 2011 at 11:38 am
Not sure I understand the question. From here on out it's just a matter of knowing which tables / columns need to be changed.
Is that where you need help?
May 30, 2011 at 12:08 pm
I have to accomplish 2 more things once the constraints are dropped.
2. change the datatype for all 22 tables
3. readd a default constraint for all 22 tables.
IE, I do not want to go to step 2 until I have completed step one on all 22 tables. Do I use a control flow construct like Break....Continue between three dynamic sql statements to ensure the complete execution of each step?
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply