September 6, 2022 at 5:58 am
2) I have few cols with columns in my database where collations is specified, i want to remove them in one go,
how both things can be done with few stmts?
September 6, 2022 at 6:38 am
This was removed by the editor as SPAM
September 6, 2022 at 11:35 am
This may be a start
SELECT object_schema_name(object_id) As ObjSchema, object_name(object_id) As ObjName, replace (T.definition, 'WITH SCHEMABINDING', '/*WITH SCHEMABINDING*/') as NewDefinition
FROM sys.sql_modules T
where is_schema_bound = 1
order by ObjSchema, ObjName
TEST IT BEFORE YOU RUN IT IN PRODUCTION
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 6, 2022 at 2:47 pm
- I want to remove the schema binding from all my functions, then want to apply but in one go, how can it be done?
2) I have few cols with columns in my database where collations is specified, i want to remove them in one go,
how both things can be done with few stmts?
While I do feel your pain with schemabinding on functions, this is actually a pretty dangerous thing to do. Even removing collations might make code run substantially slower because a lot of folks understand the performance benefit of using a Binary collation in functions that compare strings. There are also many places where removing schema binding on certain functions is going to break other stuff. You really need to do a dependency search for each object to see what might get broken.
Also, if you have nested functions along with functions that have been used in persisted computed columns, you're in for one hellava ride.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 16, 2022 at 2:25 pm
Since the OP never came back on this, let me be a bit more succinct... it's STUPID to do this without knowing the ramifications it will have for each function/view.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 16, 2022 at 7:53 pm
Since the OP never came back on this, let me be a bit more succinct... it's STUPID to do this without knowing the ramifications it will have for each function/view.
100% Agree but it would be nice if schema binding could be easily toggled on/off for things like table truncation.
September 16, 2022 at 8:02 pm
Jeff Moden wrote:Since the OP never came back on this, let me be a bit more succinct... it's STUPID to do this without knowing the ramifications it will have for each function/view.
100% Agree but it would be nice if schema binding could be easily toggled on/off for things like table truncation.
You'll get no argument from me there. Kinda like the "IsUnique" column on an index. No reason why they couldn't have an "IsWithSchemaBinding" column for programmable objects like Functions and Views.
It's still not going to help with functions that necessarily call functions (like a function that makes use of an fnTally function, for example) and you need to change one of the base functions. 🙁 Perhaps, someday they change it to providing a warning saying "if you change this, you'll need to make sure the following list of items still works". 😀 They do that in the designer (mostly).
--Jeff Moden
Change is inevitable... Change for the better is not.
September 17, 2022 at 1:34 am
You'll get no argument from me there. Kinda like the "IsUnique" column on an index. No reason why they couldn't have an "IsWithSchemaBinding" column for programmable objects like Functions and Views.
There is the is_schema_bound column in the sys.sql_modules system view. Unfortunately there is no way (that I am aware of) to update the value with a simple SQL command.
It's still not going to help with functions that necessarily call functions (like a function that makes use of an fnTally function, for example) and you need to change one of the base functions. 🙁 Perhaps, someday they change it to providing a warning saying "if you change this, you'll need to make sure the following list of items still works". 😀 They do that in the designer (mostly).
September 19, 2022 at 2:38 am
2. That kinda makes me want to build a query that recursively uses sys.sql_expression_dependencies joined sys.sql_modules to find all schema binding objects that are binding an object and the order in which you'd have to alter them. Hmmm...
That's exactly what I've had to do in the past. At the time, I made the mistake of thinking that I wouldn't need to do such a thing often and so I didn't formalize and save the script. It's one of those things where you don't need to do it often (if you done other things correctly) but it does come in handy when you need it... especially if someone add a function that calls a another function that's used by another function in computed columns of more the one table ( major face-palm, head-desk, head-desk, head-desk ).
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply