July 29, 2009 at 9:21 am
hi guys,
I have this query and often, I have to run it then review the results of the code, the result is what I then run on the database.
Is there any way in which I can run the code by itself ?
SELECT distinct 'Alter Table [' + A.TABLE_NAME + '] Drop Constraint [' + A.Constraint_Name + ']'
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE A JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS B
ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND A.TABLE_NAME =
B.TABLE_NAME
WHERE B.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
OBJECTPROPERTY(OBJECT_ID
(A.TABLE_SCHEMA +
N'.' +
A.TABLE_NAME), 'IsMSShipped') = 0
and A.TABLE_NAME in
(Select TABLE_NAME from INFORMATION_SCHEMA.COLUMNS
where COLLATION_NAME like '%_CS%'
)
July 29, 2009 at 9:44 am
You could write the output into a table variable and then pull the rows out one-by-one using a CURSOR (I know, I know), or some cursor-less loop. When you get the row you put it into a variable
DECLARE @cmdtext varchar(8000)
SELECT @cmdtext = FieldName FROM @TableName WHERE RecID = @RecordId
Then execute it:
EXEC ( @cmdtext )
Does that basically fit what you were asking for?
CEWII
July 29, 2009 at 9:48 am
Elliott W (7/29/2009)
You could write the output into a table variable and then pull the rows out one-by-one using a CURSOR (I know, I know)...
At the risk of starting an RBAR flame war, I'd say that there's no problem whatsoever with using a CURSOR here. In fact, this is a scenario in which a CURSOR is the most appropriate solution.
July 29, 2009 at 9:57 am
Thanks Guys,
Still dont understand the table variable approach, the cursor approach would just involve loading the select query into a cursor variable right ?
July 29, 2009 at 12:31 pm
Dean Jones (7/29/2009)
Thanks Guys,Still dont understand the table variable approach, the cursor approach would just involve loading the select query into a cursor variable right ?
The table value approach is basically doing an insert from your select statement into a table variable and then building a cursor or other loop structure on the table variable.
You could also build a cursor directly on the select statement.
I also don't want to get into a flame war on cursors, but it is an option..
CEWII
July 29, 2009 at 12:37 pm
Or append it in a single variable using FOR XML PATH('') with semicolumns as separators. But everything will be run in the same batch, don't know if that is an issue for you.
Cheers,
J-F
July 29, 2009 at 12:44 pm
J-F Bergeron (7/29/2009)
Or append it in a single variable using FOR XML PATH('') with semicolumns as separators. But everything will be run in the same batch, don't know if that is an issue for you.
You would probably need to specify the variable as varchar(max).. Hold on this is SQL 7 and 2000.. Then this method might be problematic since the variable can't be any bigger than varchar(8000)..
CEWII
July 29, 2009 at 12:48 pm
Elliott W (7/29/2009)
J-F Bergeron (7/29/2009)
Or append it in a single variable using FOR XML PATH('') with semicolumns as separators. But everything will be run in the same batch, don't know if that is an issue for you.You would probably need to specify the variable as varchar(max).. Hold on this is SQL 7 and 2000.. Then this method might be problematic since the variable can't be any bigger than varchar(8000)..
CEWII
Damn, even more problematic since FOR XML does not exist in 2000, sorry about the wrong answer!
Cheers,
J-F
July 29, 2009 at 12:55 pm
I hate it when that happens..
CEWII
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply