物件的 ansi_nulls 和 quoted_identifer 設定

要查詢 view,sp 或 function 建立時,session 的 ansi_nulls 和 quoted_identifer 設定,可以查詢 sys.sql_modules

當要透過 Linked Server 存取時 ansi_nulls 和 ansi_warnings 要保持一致(預設為 ON)

依朋友需求,簡單寫了一個將預存程序執行時採取 ansi_nulls off 的改成 ansi_nulls on

–剔除加密、不是微軟提供的 SP 才要修改

use tempdb

/* –建立測試資料

set ansi_nulls off

go

create proc sp

as

select ‘sp’,ansi_nulls from sys.dm_exec_sessions

where session_id=@@spid

go

set ansi_nulls on

go

create proc sp2

as

select ‘sp2’,ansi_nulls from sys.dm_exec_sessions

where session_id=@@spid

go

set ansi_nulls off

go

create proc sp3

as

select ‘sp3’,ansi_nulls from sys.dm_exec_sessions

where session_id=@@spid

go

exec sp

exec sp2

exec sp3

*/

 

set ansi_nulls on

 

DECLARE cur CURSOR FOR

SELECT object_name(m.object_id) ObjName,m.definition FROM sys.sql_modules m

join sys.objects o on m.object_id=o.object_id

where (m.uses_ansi_nulls=0 or m.uses_quoted_identifier=0)

and o.type in (‘P’)

and o.is_ms_shipped=0

and m.object_id not in (select id from sys.syscomments where encrypted=1)

–and OBJECT_NAME(m.object_id)=’pr_NE_SaveOutput_SANDBERG’

 

declare @sql nvarchar(max),@name sysname

 

open cur

fetch cur into @name,@sql

while @@FETCH_STATUS = 0

begin

     set @sql=replace(@sql,’create proc’,’alter proc’)

     set @sql=replace(@sql,’create procedure’,’alter proc’)

     set @sql=replace(@sql,’CREATE    PROCEDURE’,’alter proc’)

     set @sql=replace(@sql,’CREATE   procedure’,’alter proc’)    

     set @sql=replace(@sql,’CREATE  PROCEDURE’,’alter proc’)

     set @sql=replace(@sql,’CREATE PROCEDURE’,’alter proc’)   

     –select @sql

     exec(@sql)

     print @name

     fetch cur into @name,@sql

end

close cur

deallocate cur

/* –執行予刪除測試資料

exec sp

exec sp2

exec sp3

drop proc sp

drop proc sp2

drop proc sp3

*/

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: