how to remove schemabinding from all functions and view in one go.

Question

how to remove schemabinding from all functions and view in one go.

rajemessage 14195

Hall of Fame

Points: 3315
More actions
September 6, 2022 at 5:58 am

#4084213
1. 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?
September 6, 2022 at 6:38 am

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

rajemessage 14195 wrote:

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 4

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.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

Jason A. Long wrote:

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 6

Jeff Moden wrote:

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).

That's one of my many sales pitches for not referencing views & functions in other views & functions. Specifically those that reference user tables (and functions like fnTally being an obvious exception).
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...

This reply was modified 2 years, 2 months ago by Jason A. Long.
This reply was modified 2 years, 2 months ago by Jason A. Long.
This reply was modified 2 years, 2 months ago by Jason A. Long.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

Jason A. Long wrote:

Jeff Moden wrote:

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)