SQL Server | <jco-blog/>

originally posted 2014-03-24

1	originally posted 2014-03-24

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[System]') AND type in (N'U'))
DROP TABLE [dbo].[System]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fIsLocked]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fIsLocked]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fGetLockProgress]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fGetLockProgress]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fGenerateLockName]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fGenerateLockName]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spTest]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spTest]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spSetProgress]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spSetProgress]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spSetLock]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spSetLock]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spLongRunningQuery]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spLongRunningQuery]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spIsLockedWait]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spIsLockedWait]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spClearLock2]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spClearLock2]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spClearLock]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spClearLock]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spCancel]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spCancel]
GO

CREATE TABLE [dbo].[System](
  [Id] [uniqueidentifier] NOT NULL default newsequentialid(),
  [Lock] [nvarchar](100) NULL,
  [LockDateTime] [datetime] NULL,
  [Progress] [int] NULL,
  [Cancel] [bit] NOT NULL default 0,
  [LockWaitTimeoutSeconds] [int] NOT NULL default 10,
  [LockExpireMinutes] [int] NOT NULL default 5,
 CONSTRAINT [PK_System] PRIMARY KEY CLUSTERED 
(
  [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

TRUNCATE TABLE [dbo].[System]
GO

INSERT [dbo].[System] ([Progress]) values (null)
GO

create procedure [dbo].[spCancel]
as
begin
  if([dbo].[fIsLocked]() = 1) update [dbo].[System] set [Cancel] = 1
end
GO

create procedure [dbo].[spClearLock](@lockname nvarchar(100) = null) as
begin
  update [dbo].[System] 
  set [Lock] = null
  where [Lock] like @lockname
end
GO

create procedure [dbo].[spClearLock2] as
begin
  update [dbo].[System] set [Lock] = null
end
GO

create procedure [dbo].[spIsLockedWait] (@waitForSeconds int)
as
begin
  --if @waitForSeconds &amp;gt; 0 then wait for @waitForSeconds for the lock to clear
  --if @waitForSeconds = 0 then don't wait for the lock to clear
  --if @waitForSeconds is null then use system setting
  --if @waitForSeconds &amp;lt; 0 then wait until the is cleared.
  --returns 1 if locked, else 0
  if (@waitForSeconds is null) select top 1 @waitForSeconds = [LockWaitTimeoutSeconds] from [dbo].[System] with (nolock)
  
  declare @dt datetime = getdate()
  declare @timedout bit = 0

  while([dbo].[fIsLocked]()=1)
  begin
    if(@waitForSeconds=0 or (@waitForSeconds &amp;gt; 0 and datediff(s,@dt,getdate())&amp;gt;@waitForSeconds))
    begin
      set @timedout = 1
      break;
    end
    waitfor delay '0:00:01';
  end

  if(@timedout=1) return [dbo].[fIsLocked]();
  return 0;
end
GO

create procedure [dbo].[spSetLock](@lockname nvarchar(100), @waitSeconds int) as
begin
  --if @waitForSeconds &amp;gt; 0 then wait for @waitForSeconds for the lock to clear
  --if @waitForSeconds = 0 or null then don't wait for the lock to clear
  --if @waitForSeconds is null then use System.[LockTimeoutMinutes] value
  --if @waitForSeconds &amp;lt; 0 then wait until the is cleared.
  
  if ([dbo].[fIsLocked]()=0)
  begin
    --set lock
    update [dbo].[System] set [Lock] = @lockname, [LockDateTime] = getdate();
    return 0;
  end
  else
  begin
    --lock already set
    declare @lockExpireMinutes int
    declare @LockDateTime datetime

    select top 1 
    @lockExpireMinutes = [LockExpireMinutes] 
    ,@LockDateTime = [LockDateTime]
    from [dbo].[System] with (nolock)

    if((select datediff(n, @LockDateTime, getdate())) &amp;lt; @lockExpireMinutes)
    begin
      --not within the automatic clear time of @lockExpireMinutes

      --wait for lock for @waitSeconds
      declare @islocked bit = null;
      exec @islocked = [dbo].[spIsLockedWait] @waitSeconds
      if(@islocked = 0)
      begin
        --the existing lock cleared in time, set a new lock
        update [dbo].[System] set [Lock] = @lockname, [LockDateTime] = getdate();
      end
      else
      begin
        declare @msg nvarchar(max) = 'The system is currently running a locking process ''' + (select top 1 [Lock] from [dbo].[System]) + ''' that has locked the requested resources. You may try again once the running process completes.'
        raiserror(@msg,11,100);
        return -1;
      end
    end
    else
    begin
      --automatically clear the existing lock after @lockTimeoutMinutes and set a new lock
      update [dbo].[System] set [Lock] = @lockname, [LockDateTime] = getdate();
      return 0;
    end
  end
end
GO

create procedure [dbo].[spSetProgress](@percent int) as
begin
  declare @isLocked bit = [dbo].[fIsLocked]();
  if(@percent &amp;lt;= 0) set @percent = 1;
  if(@percent is null or @percent &amp;gt; 100 or @isLocked = 0) set @percent = 100;

  update [dbo].[System] set [Progress] = @percent

  if(@isLocked = 1) 
  begin
    declare @cancel bit = 0
    select top 1 @cancel = [Cancel] from [dbo].[System] with (nolock)
    if(@cancel = 1) 
    begin
      update [dbo].[System] set [Cancel] = 0 --clear cancel flag 
      raiserror(' cancel requested.',11,100);
      return -1;
    end
  end
end
GO

create function  [dbo].[fGenerateLockName](@seedname nvarchar(50) = null)
returns nvarchar(100)
as
begin
  declare @name nvarchar(100) = convert(nvarchar(25),datediff(s,'1970-1-1',sysutcdatetime()));
  if(@seedname is not null) set @name = @seedname + '_' + @name;
  return @name;
end
GO

create function [dbo].[fGetLockProgress]() returns nvarchar(100)
as
begin
  declare @progress int = 100;
  if([dbo].[fIsLocked]()=1)
  begin
    select @progress = [Progress]
    from [dbo].[System] with (nolock)
  end
  return @progress;
end
GO

create function [dbo].[fIsLocked]() returns bit 
as
begin
  if exists(select top 1 [Lock] 
  from [dbo].[System] with (nolock) 
  where [Lock] is not null and len(ltrim(rtrim([Lock])))&amp;gt;0) return 1;
  return 0;
end
GO

create procedure [dbo].[spLongRunningQuery]
as
begin
  declare @errored bit = 0;
  declare @msg nvarchar(4000)
  declare @intrans bit = 0;
  declare @lock_result int = null;
  declare @lock nvarchar(100) = [dbo].[fGenerateLockName]('spLongRunningQuery');
  declare @count int = 0;

  begin try
    --acquire lock
    exec @lock_result = [dbo].[spSetLock] @lock, null

    exec [dbo].[spSetProgress] 10

    begin transaction
    set @intrans = 1

    while(@count&amp;lt;100)
    begin
        exec [dbo].[spSetProgress] @count
      set @msg = convert(nvarchar(10),@count)
      raiserror (@msg, 0, 1) with nowait --print immediately
      waitfor delay '0:00:01'; --pause for a second
      set @count = @count + 10
    end

    commit transaction

  end try
  begin catch
    --handle error
    set @errored = 1;
    set @msg = 'Error : ' + isnull(error_message(),'');
    
    if (@intrans = 1) 
    begin
      rollback transaction
    end
  end catch

  --finalize
  if(@lock_result = 0) 
  begin
    --last progress update, ignore any error thrown
    begin try
      exec [dbo].[spSetProgress] 100
    end try
    begin catch
      dummy:
    end catch

    --release lock
    exec [dbo].[spClearLock] @lock
  end

  if(@errored=1) 
  begin
    raiserror(@msg,11,100);
    return -1
  end 
  return 0;
end
GO

create procedure [dbo].[spTest]
as
begin
  --TEST QUERY
  --STEP 1 : exec [spLongRunningQuery]
  --STEP 2 : Run this test query
  --NOTE: execute [spCancel] to cancel and rollback changes of [spLongRunningQuery]

  declare @msg nvarchar(300)
  declare @percent int = ''
  declare @lpercent int = ''

  while(1=1)
  begin
    if([dbo].[fIsLocked]()=1)
    begin
      set @percent = [dbo].[fGetLockProgress]() 
      set @msg = 'Progress : ' +  convert(nvarchar(100),@percent) + ' percent'
      if(@lpercent&amp;lt;&amp;gt;@percent) raiserror (@msg, 0, 1) with nowait --print immediately
      set @lpercent = @percent
    end
    else break;

    waitfor delay '0:00:00';
  end
  raiserror ('done.', 0, 1) with nowait --print immediately
end
GO

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[System]') AND type in (N'U'))

DROP TABLE [dbo].[System]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fIsLocked]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION [dbo].[fIsLocked]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fGetLockProgress]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION [dbo].[fGetLockProgress]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fGenerateLockName]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION [dbo].[fGenerateLockName]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spTest]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[spTest]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spSetProgress]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[spSetProgress]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spSetLock]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[spSetLock]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spLongRunningQuery]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[spLongRunningQuery]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spIsLockedWait]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[spIsLockedWait]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spClearLock2]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[spClearLock2]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spClearLock]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[spClearLock]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spCancel]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[spCancel]

CREATE TABLE [dbo].[System](

[Id] [uniqueidentifier] NOT NULL default newsequentialid(),

[Lock] [nvarchar](100) NULL,

[LockDateTime] [datetime] NULL,

[Progress] [int] NULL,

[Cancel] [bit] NOT NULL default 0,

[LockWaitTimeoutSeconds] [int] NOT NULL default 10,

[LockExpireMinutes] [int] NOT NULL default 5,

CONSTRAINT [PK_System] PRIMARY KEY CLUSTERED

(

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

TRUNCATE TABLE [dbo].[System]

INSERT [dbo].[System] ([Progress]) values (null)

create procedure [dbo].[spCancel]

begin

if([dbo].[fIsLocked]() = 1) update [dbo].[System] set [Cancel] = 1

end

create procedure [dbo].[spClearLock](@lockname nvarchar(100) = null) as

begin

update [dbo].[System]

set [Lock] = null

where [Lock] like @lockname

end

create procedure [dbo].[spClearLock2] as

begin

update [dbo].[System] set [Lock] = null

end

create procedure [dbo].[spIsLockedWait] (@waitForSeconds int)

begin

--if @waitForSeconds &gt; 0 then wait for @waitForSeconds for the lock to clear

--if @waitForSeconds = 0 then don't wait for the lock to clear

--if @waitForSeconds is null then use system setting

--if @waitForSeconds &lt; 0 then wait until the is cleared.

--returns 1 if locked, else 0

if (@waitForSeconds is null) select top 1 @waitForSeconds = [LockWaitTimeoutSeconds] from [dbo].[System] with (nolock)

declare @dt datetime = getdate()

declare @timedout bit = 0

while([dbo].[fIsLocked]()=1)

begin

if(@waitForSeconds=0 or (@waitForSeconds &gt; 0 and datediff(s,@dt,getdate())&gt;@waitForSeconds))

begin

set @timedout = 1

break;

end

waitfor delay '0:00:01';

end

if(@timedout=1) return [dbo].[fIsLocked]();

return 0;

end

create procedure [dbo].[spSetLock](@lockname nvarchar(100), @waitSeconds int) as

begin

--if @waitForSeconds &gt; 0 then wait for @waitForSeconds for the lock to clear

--if @waitForSeconds = 0 or null then don't wait for the lock to clear

--if @waitForSeconds is null then use System.[LockTimeoutMinutes] value

--if @waitForSeconds &lt; 0 then wait until the is cleared.

if ([dbo].[fIsLocked]()=0)

begin

--set lock

update [dbo].[System] set [Lock] = @lockname, [LockDateTime] = getdate();

return 0;

end

else

begin

--lock already set

declare @lockExpireMinutes int

declare @LockDateTime datetime

select top 1

@lockExpireMinutes = [LockExpireMinutes]

,@LockDateTime = [LockDateTime]

from [dbo].[System] with (nolock)

if((select datediff(n, @LockDateTime, getdate())) &lt; @lockExpireMinutes)

begin

--not within the automatic clear time of @lockExpireMinutes

--wait for lock for @waitSeconds

declare @islocked bit = null;

exec @islocked = [dbo].[spIsLockedWait] @waitSeconds

if(@islocked = 0)

begin

--the existing lock cleared in time, set a new lock

update [dbo].[System] set [Lock] = @lockname, [LockDateTime] = getdate();

end

else

begin

declare @msg nvarchar(max) = 'The system is currently running a locking process ''' + (select top 1 [Lock] from [dbo].[System]) + ''' that has locked the requested resources. You may try again once the running process completes.'

raiserror(@msg,11,100);

return -1;

end

else

begin

--automatically clear the existing lock after @lockTimeoutMinutes and set a new lock

update [dbo].[System] set [Lock] = @lockname, [LockDateTime] = getdate();

return 0;

end

create procedure [dbo].[spSetProgress](@percent int) as

begin

declare @isLocked bit = [dbo].[fIsLocked]();

if(@percent &lt;= 0) set @percent = 1;

if(@percent is null or @percent &gt; 100 or @isLocked = 0) set @percent = 100;

update [dbo].[System] set [Progress] = @percent

if(@isLocked = 1)

begin

declare @cancel bit = 0

select top 1 @cancel = [Cancel] from [dbo].[System] with (nolock)

if(@cancel = 1)

begin

update [dbo].[System] set [Cancel] = 0 --clear cancel flag

raiserror(' cancel requested.',11,100);

return -1;

end

create function [dbo].[fGenerateLockName](@seedname nvarchar(50) = null)

returns nvarchar(100)

begin

declare @name nvarchar(100) = convert(nvarchar(25),datediff(s,'1970-1-1',sysutcdatetime()));

if(@seedname is not null) set @name = @seedname + '_' + @name;

return @name;

end

create function [dbo].[fGetLockProgress]() returns nvarchar(100)

begin

declare @progress int = 100;

if([dbo].[fIsLocked]()=1)

begin

select @progress = [Progress]

from [dbo].[System] with (nolock)

end

return @progress;

end

create function [dbo].[fIsLocked]() returns bit

begin

if exists(select top 1 [Lock]

from [dbo].[System] with (nolock)

where [Lock] is not null and len(ltrim(rtrim([Lock])))&gt;0) return 1;

return 0;

end

create procedure [dbo].[spLongRunningQuery]

begin

declare @errored bit = 0;

declare @msg nvarchar(4000)

declare @intrans bit = 0;

declare @lock_result int = null;

declare @lock nvarchar(100) = [dbo].[fGenerateLockName]('spLongRunningQuery');

declare @count int = 0;

begin try

--acquire lock

exec @lock_result = [dbo].[spSetLock] @lock, null

exec [dbo].[spSetProgress] 10

begin transaction

set @intrans = 1

while(@count&lt;100)

begin

exec [dbo].[spSetProgress] @count

set @msg = convert(nvarchar(10),@count)

raiserror (@msg, 0, 1) with nowait --print immediately

waitfor delay '0:00:01'; --pause for a second

set @count = @count + 10

end

commit transaction

end try

begin catch

--handle error

set @errored = 1;

set @msg = 'Error : ' + isnull(error_message(),'');

if (@intrans = 1)

begin

rollback transaction

end

end catch

--finalize

if(@lock_result = 0)

begin

--last progress update, ignore any error thrown

begin try

exec [dbo].[spSetProgress] 100

end try

begin catch

dummy:

end catch

--release lock

exec [dbo].[spClearLock] @lock

end

if(@errored=1)

begin

raiserror(@msg,11,100);

return -1

end

return 0;

end

create procedure [dbo].[spTest]

begin

--TEST QUERY

--STEP 1 : exec [spLongRunningQuery]

--STEP 2 : Run this test query

--NOTE: execute [spCancel] to cancel and rollback changes of [spLongRunningQuery]

declare @msg nvarchar(300)

declare @percent int = ''

declare @lpercent int = ''

while(1=1)

begin

if([dbo].[fIsLocked]()=1)

begin

set @percent = [dbo].[fGetLockProgress]()

set @msg = 'Progress : ' + convert(nvarchar(100),@percent) + ' percent'

if(@lpercent&lt;&gt;@percent) raiserror (@msg, 0, 1) with nowait --print immediately

set @lpercent = @percent

end

else break;

waitfor delay '0:00:00';

end

raiserror ('done.', 0, 1) with nowait --print immediately

end

originally posted 2014-12-22

1	originally posted 2014-12-22

Using setvar in T-SQL

This example changes the value of the [Namespace] field for all records of all tables where the column is found.

:setvar DB "foo"
:setvar OLDNAMESPACE "foo"
:setvar NEWNAMESPACE "bar"

use $(DB)
GO
 
ALTER DATABASE $(DB) SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
select 'set database to single-user mode'
GO
 
exec sp_MSforeachtable '
    alter table ? disable trigger all;
    alter table ? nocheck constraint all;'
select 'disable triggers and constraints'
GO
 
begin try
    begin transaction
    print 'begin transaction'
 
    declare @sql nvarchar(max)
    select top 1000000
    @sql = substring(
    (
    select 
    --'select distinct [Table] = ''' + t.[Name] + ''',[Namespace] from [' + t.[Name] + '];'
    'update [' + t.[Name] + '] set [Namespace] = ''$(NEWNAMESPACE)'' where [Namespace] = ''$(OLDNAMESPACE)'';'
    from sys.tables t
    inner join sys.columns c  on c.object_id = t.object_id 
    where t.type = 'U'
    and c.[Name] = 'namespace'
    order by t.name
    for xml path('')
    )
    ,1,1000000)
 
    --select @sql
    exec(@sql);
     
    commit transaction
    select 'commit transaction'
end try
begin catch
    rollback transaction
    print 'rollback transaction'
    select 'error : ' + error_message()
end catch
 
--enable triggers and constraints for the db
exec sp_MSforeachtable '
    alter table ? enable trigger all;
    alter table ? check constraint all;'
GO
select 'enable triggers and constraints'
 
ALTER DATABASE $(DB) SET MULTI_USER;
select 'set to multi-user'
GO

:setvar DB "foo"

:setvar OLDNAMESPACE "foo"

:setvar NEWNAMESPACE "bar"

use $(DB)

ALTER DATABASE $(DB) SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

select 'set database to single-user mode'

exec sp_MSforeachtable '

alter table ? disable trigger all;

alter table ? nocheck constraint all;'

select 'disable triggers and constraints'

begin try

begin transaction

print 'begin transaction'

declare @sql nvarchar(max)

select top 1000000

@sql = substring(

(

select

--'select distinct [Table] = ''' + t.[Name] + ''',[Namespace] from [' + t.[Name] + '];'

'update [' + t.[Name] + '] set [Namespace] = ''$(NEWNAMESPACE)'' where [Namespace] = ''$(OLDNAMESPACE)'';'

from sys.tables t

inner join sys.columns c on c.object_id = t.object_id

where t.type = 'U'

and c.[Name] = 'namespace'

order by t.name

for xml path('')

)

,1,1000000)

--select @sql

exec(@sql);

commit transaction

select 'commit transaction'

end try

begin catch

rollback transaction

print 'rollback transaction'

select 'error : ' + error_message()

end catch

--enable triggers and constraints for the db

exec sp_MSforeachtable '

alter table ? enable trigger all;

alter table ? check constraint all;'

select 'enable triggers and constraints'

ALTER DATABASE $(DB) SET MULTI_USER;

select 'set to multi-user'

<jco-blog/>

Close All Connections and Drop Database

Simple Application-Level Locking and Progress Reporting for Long Running Queries

Change value of a given field for all records in all tables

Problems with Floats as keys.

Tag Archives: SQL Server