Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
DTs
Starting Member
2 Posts |
Posted - 2013-01-19 : 10:15:55
|
Hi. I have this querySELECT 'ALTER TABLE DOC_INVS_1 DROP CONSTRAINT ' + CONSTRAINT_NAMEFROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'DOC_INVS_1'and CONSTRAINT_NAME LIKE 'FK__DOC_INVS___kntr%' Which returns a query like this:ALTER TABLE DOC_INVS_1 DROP CONSTRAINT FK__DOC_INVS___kntr___2018A105 How to execute the returned query? I need to do it with another query (copy and paste to new query window is not an option)I thought something likeEXEC (<the above query>) But I guess there's some problems with the quotation marks or something else ? I've seen somewhere the prefix N` which I guess is also used for escaping the quotation marks.Thanks for the replies! |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-19 : 11:59:24
|
Wouldn't the query return multiple rows? You cannot pass a record set to EXEC. If the objective is to execute all the strings returned by the query, the following might be an option. I have not tested this on any of my tables; the goal is to concatenate all the rows with a semi-colon separating them, which can then be executed. If you expect only one row to be returned, then you don't need the FOR XML PATH('') clauseDECLARE @sql NVARCHAR(MAX);SELECT @sql = cFROM( SELECT 'ALTER TABLE DOC_INVS_1 DROP CONSTRAINT ' + CONSTRAINT_NAME + '; ' FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'DOC_INVS_1' and CONSTRAINT_NAME LIKE 'FK__DOC_INVS___kntr%' FOR XML PATH('')) T(c);EXEC(@sql); All the usual warnings and concerns about dynamic SQL apply. |
|
|
DTs
Starting Member
2 Posts |
Posted - 2013-01-19 : 12:11:51
|
Yes, I expect only one row to be returned because there's no more than one constraint like the above in each table. Anyway, your code works like charm! Thanks a lot! |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-19 : 15:03:26
|
You are very welcome - glad to be of help. |
|
|
|
|
|