CODEWITHSUNDEEP
sql-serverviewnolock
Matthew Martel
Matthew Martel

Reputation: 11

T-SQL Create a view with no lock for all tables in a database

I am looking for a script that I can run on my server to create views with no locking for all of my tables within my database. Thanks!

Upvotes: 0

Views: 1884

Answers (1)

square_particle
square_particle

Reputation: 524

Although I agree that this is a bad idea, there still may be something to be gained/learned by showing some examples. But -- yes -- this is potentially a really bad idea, but in some world this may make sense.

If this is a one-time thing, you can do something like this, which will simply create the DDL for the views with (nolock). You simply need to copy-paste and execute. If this needs to be dynamically generated, then a bit more needs to be harnessed.

One more caveat about the below -- it uses select * in the view without schema binding. Be extraordinarily cautious with these types of views as they do not automatically update if the underlying table structure changes. It's advisable and good practice to fully qualify columns in views unless you have other safeguards.

select '
    create view ' + name + 'MayBeABadIdea as select * from ' + name + ' (nolock);
    go'
from sys.objects
where type = 'U'
order by name;

Upvotes: 1

Related Questions