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-04-13

1	originally posted 2014-04-13

I have a query for which the keys must appear distinctly, but they are duplicated in the resultset. I want to keep only one of the duplicated rows. That is, if I find a record with a duplicated key, I want to keep only one of the records and all of its other data. I don’t care which of the rows get dumped. Admittedly, this is a strange and rare situation (all the more reason to document it).

if (object_id('tempdb..#temp') is not null) drop table #temp
create table #temp ([Number] int,[State] nvarchar(100) default 'OK')
insert #temp ([Number]) values (1),(2),(2),(2),(3)

select [Comment] = 'The idea is to remove all but one of the rows where the value for the [Number] column appears more than once.'

select [Comment] = 'original table',* from #temp

if (object_id('tempdb..#temp2') is not null) drop table #temp2
select 
*
,[DuplicateRowNumber] = row_number() over (partition by [Number] order by [Number]) 
into #temp2
from #temp

select [Comment] = 'copy table with the dupe finder column',* from #temp2

update #temp2
set [State] = 'DUPLICATED!'
from
(
  select distinct 
  [Number]
  from #temp2
  where [DuplicateRowNumber] &amp;gt; 1
) as dupes
where #temp2.[Number] = dupes.[Number]

select [Comment] = 'copy table with [State] field marked with a comment for the duplicated rows (optional)',* from #temp2

delete from #temp2 where [DuplicateRowNumber] &amp;gt; 1

select [Comment] = 'copy table all but one of the duplicated rows removed',* from #temp2

if (object_id('tempdb..#temp') is not null) drop table #temp

create table #temp ([Number] int,[State] nvarchar(100) default 'OK')

insert #temp ([Number]) values (1),(2),(2),(2),(3)

select [Comment] = 'The idea is to remove all but one of the rows where the value for the [Number] column appears more than once.'

select [Comment] = 'original table',* from #temp

if (object_id('tempdb..#temp2') is not null) drop table #temp2

select

,[DuplicateRowNumber] = row_number() over (partition by [Number] order by [Number])

into #temp2

from #temp

select [Comment] = 'copy table with the dupe finder column',* from #temp2

update #temp2

set [State] = 'DUPLICATED!'

from

(

select distinct

[Number]

from #temp2

where [DuplicateRowNumber] &gt; 1

) as dupes

where #temp2.[Number] = dupes.[Number]

select [Comment] = 'copy table with [State] field marked with a comment for the duplicated rows (optional)',* from #temp2

delete from #temp2 where [DuplicateRowNumber] &gt; 1

select [Comment] = 'copy table all but one of the duplicated rows removed',* from #temp2

originally posted 2014-04-22

1	originally posted 2014-04-22

--delete all records that contain a specified value for a given key field...
--... for all tables that have the key field and that conform to a certain name pattern

--concatenate a list of IDs into a delimited string for use in an IN statement
declare @ids nvarchar(max)
declare @sql nvarchar(max)

;with cteIds as
(
  --source table for the key values for the deletion
  select [DistributionCodeId] 
  from [SIP_NPT_Posted]
)

select
@ids = stuff(
(
    select ',''' + cte.[DistributionCodeId] + ''''
    from cteIds cte
    order by cte.[DistributionCodeId]
    for xml path('')
),1,1,'')

--the concatenated IDs 
select @ids

--create a delete script for each table that conforms to the name pattern using the concatenated IDs for the IN statement of the WHERE clause
;with cteTables as
(
  select [sql] = 'delete from [' + t.[name] + '] where [DistributionCodeId] in (' + isnull(@ids,'''''') + ')'
  from sys.tables t
  inner join sys.columns c on t.object_id = c.object_id
  where t.[type] = 'U'
  and t.[name] like 'SIP_NPT_POST%' -- table name pattern
  and c.[name] = 'DistributionCodeId' --column name 
  and t.name &amp;lt;&amp;gt; 'SIP_NPT_Posted' --exclude the [SIP_NPT_Posted] table b/c deletion for it is handled elsewhere.
)

--concatenate the delete scripts into a single string of SQL
select
@sql = stuff(
(
    select ';' + cte.[sql]
    from cteTables cte
    order by cte.[sql]
    for xml path('')
),1,1,'')

select [@sql] = @sql

--execute the SQL
--exec(@sql); --UNCOMMENT ME TO DELETE!

/*

[OUTPUT]

@ids contains this...

'100009-010','100222-010'


@sql contains this...

delete from [SIP_NPT_POST_Benefits] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_Earnings] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_EnDocuments] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_EnSessions] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_EnTransactions] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_FedTaxes_Benefits] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_FedTaxes_Earnings] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_JobPaySchedules] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_JobPriorExpenses] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_JobRemainingPayPeriods] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_JobTotalPayPeriods] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_Payees] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_PaySummary] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_PostPaySchedules] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_PostTotalPayPeriods] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_Projects] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_StateTaxes_Benefits] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_StateTaxes_Earnings] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_POST_WorkersCompensation] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_PostedPayee] where [DistributionCodeId] in ('100009-010','100222-010');
delete from [SIP_NPT_PostedTran] where [DistributionCodeId] in ('100009-010','100222-010')

*/

--delete all records that contain a specified value for a given key field...

--... for all tables that have the key field and that conform to a certain name pattern

--concatenate a list of IDs into a delimited string for use in an IN statement

declare @ids nvarchar(max)

declare @sql nvarchar(max)

;with cteIds as

(

--source table for the key values for the deletion

select [DistributionCodeId]

from [SIP_NPT_Posted]

)

select

@ids = stuff(

(

select ',''' + cte.[DistributionCodeId] + ''''

from cteIds cte

order by cte.[DistributionCodeId]

for xml path('')

),1,1,'')

--the concatenated IDs

select @ids

--create a delete script for each table that conforms to the name pattern using the concatenated IDs for the IN statement of the WHERE clause

;with cteTables as

(

select [sql] = 'delete from [' + t.[name] + '] where [DistributionCodeId] in (' + isnull(@ids,'''''') + ')'

from sys.tables t

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

where t.[type] = 'U'

and t.[name] like 'SIP_NPT_POST%' -- table name pattern

and c.[name] = 'DistributionCodeId' --column name

and t.name &lt;&gt; 'SIP_NPT_Posted' --exclude the [SIP_NPT_Posted] table b/c deletion for it is handled elsewhere.

)

--concatenate the delete scripts into a single string of SQL

select

@sql = stuff(

(

select ';' + cte.[sql]

from cteTables cte

order by cte.[sql]

for xml path('')

),1,1,'')

select [@sql] = @sql

--execute the SQL

--exec(@sql); --UNCOMMENT ME TO DELETE!

[OUTPUT]

@ids contains this...

'100009-010','100222-010'

@sql contains this...

delete from [SIP_NPT_POST_Benefits] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_Earnings] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_EnDocuments] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_EnSessions] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_EnTransactions] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_FedTaxes_Benefits] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_FedTaxes_Earnings] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_JobPaySchedules] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_JobPriorExpenses] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_JobRemainingPayPeriods] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_JobTotalPayPeriods] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_Payees] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_PaySummary] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_PostPaySchedules] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_PostTotalPayPeriods] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_Projects] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_StateTaxes_Benefits] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_StateTaxes_Earnings] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_POST_WorkersCompensation] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_PostedPayee] where [DistributionCodeId] in ('100009-010','100222-010');

delete from [SIP_NPT_PostedTran] where [DistributionCodeId] in ('100009-010','100222-010')

originally posted 2014-06-05

1	originally posted 2014-06-05

create procedure [dbo].[spGetLockedSqlObjects]
as
begin
  select
  [SessionId] = [lock].[request_session_id]
  ,[LockedObjectName] = [object].[Name]
  ,[Duration] = datediff(second,activetrans.[transaction_begin_time], getdate())
  ,[TransactionBeginTime] = activetrans.[transaction_begin_time]
  ,[Locks] = count(*)
  from sys.dm_tran_locks [lock] with (nolock)
  join sys.partitions [partition] with (nolock) on [partition].[hobt_id] = [lock].[resource_associated_entity_id]
  join sys.objects [object] with (nolock) on [object].[object_id] = [partition].[object_id]
  join sys.dm_exec_sessions execsession with (nolock) on execsession.[session_id] = [lock].[request_session_id]
  join sys.dm_tran_session_transactions transession with (nolock) on transession.[session_id] = execsession.[session_id]
  join sys.dm_tran_active_transactions activetrans with (nolock) on activetrans.[transaction_id] = transession.[transaction_id]
  where [resource_database_id] = db_id() and [object].[type] = 'U'
  group by activetrans.[transaction_begin_time]
  ,[lock].[request_session_id]
  ,[object].[name]
end

create procedure [dbo].[spGetLockedSqlObjects]

begin

select

[SessionId] = [lock].[request_session_id]

,[LockedObjectName] = [object].[Name]

,[Duration] = datediff(second,activetrans.[transaction_begin_time], getdate())

,[TransactionBeginTime] = activetrans.[transaction_begin_time]

,[Locks] = count(*)

from sys.dm_tran_locks [lock] with (nolock)

join sys.partitions [partition] with (nolock) on [partition].[hobt_id] = [lock].[resource_associated_entity_id]

join sys.objects [object] with (nolock) on [object].[object_id] = [partition].[object_id]

join sys.dm_exec_sessions execsession with (nolock) on execsession.[session_id] = [lock].[request_session_id]

join sys.dm_tran_session_transactions transession with (nolock) on transession.[session_id] = execsession.[session_id]

join sys.dm_tran_active_transactions activetrans with (nolock) on activetrans.[transaction_id] = transession.[transaction_id]

where [resource_database_id] = db_id() and [object].[type] = 'U'

group by activetrans.[transaction_begin_time]

,[lock].[request_session_id]

,[object].[name]

end

originally posted 2014-12-16

1	originally posted 2014-12-16

Note: This T-SQL uses SQLCMD variables which can be enabled in SQL Server Management Studio.
Enable SQLCMD mode : Tools -&gt; Options -&gt; Query Execution -&gt; Check the box "By default, open new queries in SQLCMD mode".

1 2	Note: This T-SQL uses SQLCMD variables which can be enabled in SQL Server Management Studio. Enable SQLCMD mode : Tools -> Options -> Query Execution -> Check the box "By default, open new queries in SQLCMD mode".

:setvar DB "FOO"
begin try
  ALTER DATABASE $(DB) SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

  BACKUP DATABASE [$(DB)] TO  DISK = N'c:\temp\$(DB).bak' WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N'$(DB)', SKIP, NOREWIND, NOUNLOAD,  STATS = 10, CHECKSUM

  declare @backupSetId as int
  select @backupSetId = position 
  from msdb..backupset 
  where database_name=N'$(DB)' 
  and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'$(DB)' )

  if @backupSetId is null 
  begin 
    raiserror(N'Verify failed. Backup information for database ''$(DB)'' not found.', 16, 1) 
  end

  RESTORE VERIFYONLY FROM  DISK = N'c:\temp\$(DB).bak' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND

  ALTER DATABASE $(DB) SET MULTI_USER;
  
end try
begin catch
  ALTER DATABASE $(DB) SET MULTI_USER;
  declare @err nvarchar(max) = @@ERROR;
  raiserror(@err,11,100)
end catch

:setvar DB "FOO"

begin try

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

BACKUP DATABASE [$(DB)] TO DISK = N'c:\temp\$(DB).bak' WITH COPY_ONLY, NOFORMAT, NOINIT, NAME = N'$(DB)', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM

declare @backupSetId as int

select @backupSetId = position

from msdb..backupset

where database_name=N'$(DB)'

and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'$(DB)' )

if @backupSetId is null

begin

raiserror(N'Verify failed. Backup information for database ''$(DB)'' not found.', 16, 1)

end

RESTORE VERIFYONLY FROM DISK = N'c:\temp\$(DB).bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND

ALTER DATABASE $(DB) SET MULTI_USER;

end try

begin catch

ALTER DATABASE $(DB) SET MULTI_USER;

declare @err nvarchar(max) = @@ERROR;

raiserror(@err,11,100)

end catch

originally posted 2014-06-26

1	originally posted 2014-06-26

Adapted from “Unique Indexes Are Code; Non-Unique Indexes Are Data” By Daniel White, 2014/06/26

create view [dbo].[vMissingIndexes]
as

/*
This view shows the server's suggestions for missing indexes that
should be created to improve performance based on actual
queries executed.
The formula for potential_user_benefit came straight from the
SQL Server Books Online documentation for the index-related
dynamic management view sys.dm_db_missing_index_group_stats:
http://msdn.microsoft.com/en-us/library/ms345421.aspx
I don't completely understand it, but it seems to work.
*/

select 
[IndexHandle] = mid.[index_handle]
,[DatabaseId] = mid.[database_id]
,[ObjectName] = mid.[statement]
,[EqualityColumns] = mid.[equality_columns]
,[InequalityColumns] = mid.[inequality_columns]
,[IncludedColumns] = mid.[included_columns]
,[UserSeeks] = migs.[user_seeks]
,[UserScans] = migs.[user_scans]
,[AvgTotalUserCost] = migs.[avg_total_user_cost]
,[AvgUserImpact] = migs.[avg_user_impact]
,[PotentialUserBenefit] = migs.[avg_total_user_cost] * migs.[avg_user_impact] * (migs.[user_seeks] + migs.[user_scans])
from sys.dm_db_missing_index_details mid
inner join sys.dm_db_missing_index_groups mig on mid.[index_handle] = mig.[index_handle]
inner join sys.dm_db_missing_index_group_stats migs on mig.[index_group_handle] = migs.[group_handle]
where (mid.[database_id] = db_id())
GO

-- return the maximum of 3 dates
create function [dbo].[fGetMax3DateTimes]
(
 @value1 datetime,
 @value2 datetime,
 @value3 datetime
)
returns datetime
as
begin
  declare @value datetime;
  select @value = MAX(value)
  from 
  (
    select @value1 value
    union
    select @value2 value
    union
    select @value3 value
  ) as allvalues;
  return @value;
end

/*
Indexes are selected for dropping by considering how long it has
been since they were last read and by comparing the number of
reads to the number of writes.
*/
create view [dbo].[vDroppableIndexes]
as
select [SchemaName] = [schemas].name
,[ObjectName] = [tables].name
,[IndexName] = [indexes].name
,[Usefulness] = isnull(ius.[user_seeks] + ius.[user_scans] + ius.[user_lookups] - ius.[user_updates], 0)
,[LastUserRead] = [dbo].[fGetMax3DateTimes](ius.[last_user_seek],ius.[last_user_scan],ius.[last_user_lookup])
,[LastUserUpdate] = ius.[last_user_update]
from sys.schemas [schemas]
inner join sys.tables [tables] on [schemas].[schema_id] = [tables].[schema_id]
inner join sys.indexes [indexes] on [tables].[object_id] = [indexes].[object_id]
left outer join sys.dm_db_index_usage_stats ius on ius.[database_id] = db_id() 
  and [indexes].[object_id] = ius.[object_id] 
  and [indexes].[index_id] = ius.[index_id]
where ([indexes].[is_unique] = 0)
GO

create function [dbo].[fGetMissingIndexName](@indexHandle int)
returns sysname
as
begin
  declare @indexName sysname;
  
  select @indexName = 'IX_' + obj.[name]
  from sys.objects obj
  inner join sys.dm_db_missing_index_details mid on obj.[object_id] = mid.[object_id]
  where mid.[index_handle] = @indexHandle
  and mid.[database_id] = db_id()

  select @indexName = @indexName + 
    case when [column_usage] = 'INCLUDE' then '#'
    else '_'
    end + [column_name]
  from sys.dm_db_missing_index_columns(@indexHandle);

  return @indexName;
end
GO

create procedure [dbo].[spCreateIndexes](@fullyQualifiedTableNamePattern nvarchar(255))
as
begin

  if (object_id('tempdb..#CreateIndexesTemp') is not null) drop table #CreateIndexesTemp
  if(@fullyQualifiedTableNamePattern is null or len(@fullyQualifiedTableNamePattern) = 0) set @fullyQualifiedTableNamePattern = '%'

  select 
  [ObjectName]
  ,[IndexName] = [dbo].[fGetMissingIndexName]([IndexHandle])
  ,[EqualityColumns]
  ,[InequalityColumns]
  ,[IncludedColumns]
  ,[PotentialUserBenefit]
  into #CreateIndexesTemp
  from [dbo].[vMissingIndexes]
  where [ObjectName] like @fullyQualifiedTableNamePattern

  declare @objectName sysname
  ,@indexName sysname
  ,@equalityColumns nvarchar(4000)
  ,@inequalityColumns nvarchar(4000)
  ,@includedColumns nvarchar(4000)
  ,@sql nvarchar(4000)

  while(1=1)
  begin
    set @indexName = null;

    select top 1
    @objectName = [ObjectName]
    ,@indexName = [IndexName]
    ,@equalityColumns = [EqualityColumns]
    ,@inequalityColumns = [InequalityColumns]
    ,@includedColumns = [IncludedColumns]
    from #CreateIndexesTemp
    order by [PotentialUserBenefit] desc;

    if(@indexName is not null)
    begin
      select @sql = 'create index ' + quotename(@indexName) +
        ' on ' + @objectName + ' (' +
        coalesce(@equalityColumns + ', ' +
            @inequalityColumns, @equalityColumns,
            @inequalityColumns) + ')' +
        isnull(' include (' + @includedColumns + ')', '');
      
      print @sql;
      exec (@sql);

      delete from #CreateIndexesTemp where [IndexName] = @indexName 
    end
    else break;
  end
end
GO

create PROCEDURE [dbo].[spDropIndexes](@fqnTableNamePattern nvarchar(255))
as
begin
  if (object_id('tempdb..#DropIndexesTemp') is not null) drop table #DropIndexesTemp
  if(@fqnTableNamePattern is null or len(@fqnTableNamePattern) = 0) set @fqnTableNamePattern = '%'
  
  select
  [SchemaName] 
  ,[ObjectName]
  ,[IndexName]
  ,[LastUserRead]
  ,[Usefulness]
  into #DropIndexesTemp
  from [dbo].[vDroppableIndexes]
  where [ObjectName] like @fqnTableNamePattern
  order by [LastUserRead],[Usefulness]

  declare @schemaName sysname,
    @objectName sysname,
    @indexName sysname,
    @sql nvarchar(4000);

  while(1=1)
  begin
    set @indexName = null;
    
    select top 1
    @schemaName = [SchemaName]
    ,@objectName = [ObjectName]
    ,@indexName = [IndexName]
    from #DropIndexesTemp
    order by [LastUserRead],[Usefulness]

    if(@indexName is not null)
    begin
      select @sql = 'DROP INDEX ' + quotename(@indexName) +
        ' ON ' + quotename(@schemaName) + '.' +
        quotename(@objectName) + ';';
      
      print @sql;
      exec (@sql);

      delete from #DropIndexesTemp where [IndexName] = @indexName 
    end
    else break;
  end
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

create view [dbo].[vMissingIndexes]

This view shows the server's suggestions for missing indexes that

should be created to improve performance based on actual

queries executed.

The formula for potential_user_benefit came straight from the

SQL Server Books Online documentation for the index-related

dynamic management view sys.dm_db_missing_index_group_stats:

http://msdn.microsoft.com/en-us/library/ms345421.aspx

I don't completely understand it, but it seems to work.

select

[IndexHandle] = mid.[index_handle]

,[DatabaseId] = mid.[database_id]

,[ObjectName] = mid.[statement]

,[EqualityColumns] = mid.[equality_columns]

,[InequalityColumns] = mid.[inequality_columns]

,[IncludedColumns] = mid.[included_columns]

,[UserSeeks] = migs.[user_seeks]

,[UserScans] = migs.[user_scans]

,[AvgTotalUserCost] = migs.[avg_total_user_cost]

,[AvgUserImpact] = migs.[avg_user_impact]

,[PotentialUserBenefit] = migs.[avg_total_user_cost] * migs.[avg_user_impact] * (migs.[user_seeks] + migs.[user_scans])

from sys.dm_db_missing_index_details mid

inner join sys.dm_db_missing_index_groups mig on mid.[index_handle] = mig.[index_handle]

inner join sys.dm_db_missing_index_group_stats migs on mig.[index_group_handle] = migs.[group_handle]

where (mid.[database_id] = db_id())

-- return the maximum of 3 dates

create function [dbo].[fGetMax3DateTimes]

(

@value1 datetime,

@value2 datetime,

@value3 datetime

)

returns datetime

begin

declare @value datetime;

select @value = MAX(value)

from

(

select @value1 value

union

select @value2 value

union

select @value3 value

) as allvalues;

return @value;

end

Indexes are selected for dropping by considering how long it has

been since they were last read and by comparing the number of

reads to the number of writes.

create view [dbo].[vDroppableIndexes]

select [SchemaName] = [schemas].name

,[ObjectName] = [tables].name

,[IndexName] = [indexes].name

,[Usefulness] = isnull(ius.[user_seeks] + ius.[user_scans] + ius.[user_lookups] - ius.[user_updates], 0)

,[LastUserRead] = [dbo].[fGetMax3DateTimes](ius.[last_user_seek],ius.[last_user_scan],ius.[last_user_lookup])

,[LastUserUpdate] = ius.[last_user_update]

from sys.schemas [schemas]

inner join sys.tables [tables] on [schemas].[schema_id] = [tables].[schema_id]

inner join sys.indexes [indexes] on [tables].[object_id] = [indexes].[object_id]

left outer join sys.dm_db_index_usage_stats ius on ius.[database_id] = db_id()

and [indexes].[object_id] = ius.[object_id]

and [indexes].[index_id] = ius.[index_id]

where ([indexes].[is_unique] = 0)

create function [dbo].[fGetMissingIndexName](@indexHandle int)

returns sysname

begin

declare @indexName sysname;

select @indexName = 'IX_' + obj.[name]

from sys.objects obj

inner join sys.dm_db_missing_index_details mid on obj.[object_id] = mid.[object_id]

where mid.[index_handle] = @indexHandle

and mid.[database_id] = db_id()

select @indexName = @indexName +

case when [column_usage] = 'INCLUDE' then '#'

else '_'

end + [column_name]

from sys.dm_db_missing_index_columns(@indexHandle);

return @indexName;

end

create procedure [dbo].[spCreateIndexes](@fullyQualifiedTableNamePattern nvarchar(255))

begin

if (object_id('tempdb..#CreateIndexesTemp') is not null) drop table #CreateIndexesTemp

if(@fullyQualifiedTableNamePattern is null or len(@fullyQualifiedTableNamePattern) = 0) set @fullyQualifiedTableNamePattern = '%'

select

[ObjectName]

,[IndexName] = [dbo].[fGetMissingIndexName]([IndexHandle])

,[EqualityColumns]

,[InequalityColumns]

,[IncludedColumns]

,[PotentialUserBenefit]

into #CreateIndexesTemp

from [dbo].[vMissingIndexes]

where [ObjectName] like @fullyQualifiedTableNamePattern

declare @objectName sysname

,@indexName sysname

,@equalityColumns nvarchar(4000)

,@inequalityColumns nvarchar(4000)

,@includedColumns nvarchar(4000)

,@sql nvarchar(4000)

while(1=1)

begin

set @indexName = null;

select top 1

@objectName = [ObjectName]

,@indexName = [IndexName]

,@equalityColumns = [EqualityColumns]

,@inequalityColumns = [InequalityColumns]

,@includedColumns = [IncludedColumns]

from #CreateIndexesTemp

order by [PotentialUserBenefit] desc;

if(@indexName is not null)

begin

select @sql = 'create index ' + quotename(@indexName) +

' on ' + @objectName + ' (' +

coalesce(@equalityColumns + ', ' +

@inequalityColumns, @equalityColumns,

@inequalityColumns) + ')' +

isnull(' include (' + @includedColumns + ')', '');

print @sql;

exec (@sql);

delete from #CreateIndexesTemp where [IndexName] = @indexName

end

else break;

end

create PROCEDURE [dbo].[spDropIndexes](@fqnTableNamePattern nvarchar(255))

begin

if (object_id('tempdb..#DropIndexesTemp') is not null) drop table #DropIndexesTemp

if(@fqnTableNamePattern is null or len(@fqnTableNamePattern) = 0) set @fqnTableNamePattern = '%'

select

[SchemaName]

,[ObjectName]

,[IndexName]

,[LastUserRead]

,[Usefulness]

into #DropIndexesTemp

from [dbo].[vDroppableIndexes]

where [ObjectName] like @fqnTableNamePattern

order by [LastUserRead],[Usefulness]

declare @schemaName sysname,

@objectName sysname,

@indexName sysname,

@sql nvarchar(4000);

while(1=1)

begin

set @indexName = null;

select top 1

@schemaName = [SchemaName]

,@objectName = [ObjectName]

,@indexName = [IndexName]

from #DropIndexesTemp

order by [LastUserRead],[Usefulness]

if(@indexName is not null)

begin

select @sql = 'DROP INDEX ' + quotename(@indexName) +

' ON ' + quotename(@schemaName) + '.' +

quotename(@objectName) + ';';

print @sql;

exec (@sql);

delete from #DropIndexesTemp where [IndexName] = @indexName

end

else break;

end

originally posted 2014-06-27

1	originally posted 2014-06-27

In any Excel cell that needs line-breaks, instead of using ALT-ENTER, put a placeholder {NL} in the Excel cell’s text.

Example: 123 Maple St{NL}Apt 2{NL}Sacramento,CA{NL}95818

Paste the Excel data into the SQL table as normal.

Execute this SQL statement to change the placeholders to SQL line-break characters:

update [tablename] set [fieldname] = replace([fieldname],'{NL}',CHAR(13))

originally posted 2014-08-06

1	originally posted 2014-08-06

Generates a unique namespace that conforms to DNS name format from the input.

Releated: Validate Format of a Domain Name post

Input formats:

If email address, the host part is used.

If URI, the primary domain name is used.

If neither of the above formats then uses the input as is.

Looks in the [Subscription] table of the specified database table for the [Namespace] column to determine uniqueness.

//LinqPad Query, Language = "C# Program"
void Main()
{
  string value = "justin@elementalp.com";
  GenerateNamespace(value).Dump(value + " becomes...");
  
  value = "The ABC Company!";
  GenerateNamespace(value).Dump(value + " becomes...");
  
  value = "Foo";
  GenerateNamespace(value).Dump(value + " becomes...");	
}

/// &amp;lt;summary&amp;gt;
/// Uses an input value as the basis for generating a unique namespace
/// If the formatted input name is already in use, a number will be appended to it until a unique namespace results.
/// Input value defaults to "ORG"
/// Output is made to confirm to DNS name requirements.
/// Examines the SQL object "[npt_cloud].[dbo].[Subscription].[Namespace]" to check for uniqueness.
/// &amp;lt;/summary&amp;gt;
public string GenerateNamespace(string value)
{

  //string value = "https://this.that.these.those.elementalp.com";
  //string value = "----= The-ElementalP-Company =----";
  string result = null;
  
  const string sql =
  @"declare @pattern nvarchar(100) = '%[^0-9A-Za-z\-]%' --filter chars that are not alpha, numerics, or dashes
  declare @defaultName nvarchar(100) = 'ORG'; --the default namespace name
  declare @try nvarchar(100) = null

  	if(@ns is null or len(@ns)=0) goto thenext;

  --remove characters not in the pattern
  while patindex(@pattern, @ns) &amp;gt; 0 set @ns = stuff(@ns, patindex(@pattern, @ns), 1, '');
  set @ns = ltrim(rtrim(@ns));
  if(@ns is null or len(@ns)=0) goto thenext;
  
  --remove leading dashes
  while (patindex('-%', @ns) = 1) set @ns = substring(@ns,2,len(@ns));
  
  --remove trailing dashes
  while (patindex('%-', @ns) = len(@ns)) set @ns = substring(@ns,1,len(@ns)-1);
  
  --shorten to 63 chars, make upper case, and trim                   
  set @ns = ltrim(rtrim(upper(substring(@ns,1,63))));
    
  thenext:
  
  --default empty namespace
  if(@ns is null or len(@ns)=0) set @ns = @defaultName  --goto theend;
    
  set @try = @ns
  
  declare @num int = 0;
  while (1=1)
  begin
    if exists (select [Namespace] from [dbo].[Subscription] where [Namespace] like @try)
    begin
      set @num = @num + 1;
      set @try = @ns + convert(nvarchar(10),@num)
    end
  else break;
  
  end
  theend:
  select [Namespace] = @try;";

  System.Diagnostics.Debug.WriteLine(string.Format("Input: {0}",  value!=null ? string.Format("\"{0}\"",value) : "null"));
    
  //parse host name from namespace
  if(!string.IsNullOrWhiteSpace(value))value = ParseNamespace(value);
  
  if(value==null) value = string.Empty;

  using (var connection = new SqlConnection("Data Source=.;Initial Catalog=npt_cloud;Application Name=npt;Integrated Security=true;"))
  {
    connection.Open();
    using (var command = new SqlCommand(sql, connection))
    {
      var param = new SqlParameter("@ns", System.Data.SqlDbType.NVarChar);
      param.Value = value;
      command.Parameters.Add(param);
    
      result = command.ExecuteScalar() as string;
      connection.Close();
      return result;
    }
  }
}

/// &amp;lt;summary&amp;gt;
/// Parses a namespace from input value.
/// If the input value is an email address, the output will be the host name.
/// If the input value is a URL, the output is the primary domain name.
/// If the input value is not one of the above formats, the input text before the first period (if any) is returned.
/// &amp;lt;/summary&amp;gt;
public static string ParseNamespace(string text)
{
  string ns = null;
  try
  {
      //try get domain as email address
      var addr = new MailAddress(text);
      ns = addr.Host;
  }
  catch (System.FormatException ex1)
  {
      try
      {
          //try get domain as uri
          var uri = new Uri(text);
          string[] hostParts = uri.Host.Split(new string[1] { "." }, StringSplitOptions.RemoveEmptyEntries);
          ns = hostParts[hostParts.Length-2];
      }
      catch (System.UriFormatException ex2)
      {
          //use id as the domain
          ns = text;
      }
  }

  if (ns != null) ns = ns.Split(new char[] { '.' }, 2)[0];
  
  return ns;
}

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

//LinqPad Query, Language = "C# Program"

void Main()

{

string value = "justin@elementalp.com";

GenerateNamespace(value).Dump(value + " becomes...");

value = "The ABC Company!";

GenerateNamespace(value).Dump(value + " becomes...");

value = "Foo";

GenerateNamespace(value).Dump(value + " becomes...");

}

/// &lt;summary&gt;

/// Uses an input value as the basis for generating a unique namespace

/// If the formatted input name is already in use, a number will be appended to it until a unique namespace results.

/// Input value defaults to "ORG"

/// Output is made to confirm to DNS name requirements.

/// Examines the SQL object "[npt_cloud].[dbo].[Subscription].[Namespace]" to check for uniqueness.

/// &lt;/summary&gt;

public string GenerateNamespace(string value)

{

//string value = "https://this.that.these.those.elementalp.com";

//string value = "----= The-ElementalP-Company =----";

string result = null;

const string sql =

@"declare @pattern nvarchar(100) = '%[^0-9A-Za-z\-]%' --filter chars that are not alpha, numerics, or dashes

declare @defaultName nvarchar(100) = 'ORG'; --the default namespace name

declare @try nvarchar(100) = null

if(@ns is null or len(@ns)=0) goto thenext;

--remove characters not in the pattern

while patindex(@pattern, @ns) &gt; 0 set @ns = stuff(@ns, patindex(@pattern, @ns), 1, '');

set @ns = ltrim(rtrim(@ns));

if(@ns is null or len(@ns)=0) goto thenext;

--remove leading dashes

while (patindex('-%', @ns) = 1) set @ns = substring(@ns,2,len(@ns));

--remove trailing dashes

while (patindex('%-', @ns) = len(@ns)) set @ns = substring(@ns,1,len(@ns)-1);

--shorten to 63 chars, make upper case, and trim

set @ns = ltrim(rtrim(upper(substring(@ns,1,63))));

thenext:

--default empty namespace

if(@ns is null or len(@ns)=0) set @ns = @defaultName --goto theend;

set @try = @ns

declare @num int = 0;

while (1=1)

begin

if exists (select [Namespace] from [dbo].[Subscription] where [Namespace] like @try)

begin

set @num = @num + 1;

set @try = @ns + convert(nvarchar(10),@num)

end

else break;

end

theend:

select [Namespace] = @try;";

System.Diagnostics.Debug.WriteLine(string.Format("Input: {0}", value!=null ? string.Format("\"{0}\"",value) : "null"));

//parse host name from namespace

if(!string.IsNullOrWhiteSpace(value))value = ParseNamespace(value);

if(value==null) value = string.Empty;

using (var connection = new SqlConnection("Data Source=.;Initial Catalog=npt_cloud;Application Name=npt;Integrated Security=true;"))

{

connection.Open();

using (var command = new SqlCommand(sql, connection))

{

var param = new SqlParameter("@ns", System.Data.SqlDbType.NVarChar);

param.Value = value;

command.Parameters.Add(param);

result = command.ExecuteScalar() as string;

connection.Close();

return result;

}

/// &lt;summary&gt;

/// Parses a namespace from input value.

/// If the input value is an email address, the output will be the host name.

/// If the input value is a URL, the output is the primary domain name.

/// If the input value is not one of the above formats, the input text before the first period (if any) is returned.

/// &lt;/summary&gt;

public static string ParseNamespace(string text)

{

string ns = null;

try

{

//try get domain as email address

var addr = new MailAddress(text);

ns = addr.Host;

}

catch (System.FormatException ex1)

{

try

{

//try get domain as uri

var uri = new Uri(text);

string[] hostParts = uri.Host.Split(new string[1] { "." }, StringSplitOptions.RemoveEmptyEntries);

ns = hostParts[hostParts.Length-2];

}

catch (System.UriFormatException ex2)

{

//use id as the domain

ns = text;

}

if (ns != null) ns = ns.Split(new char[] { '.' }, 2)[0];

return ns;

}

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'

originally posted 2014-12-29

1	originally posted 2014-12-29

Note: This T-SQL uses SQLCMD variables which can be enabled in SQL Server Management Studio.
Enable SQLCMD mode : Tools -&gt; Options -&gt; Query Execution -&gt; Check the box "By default, open new queries in SQLCMD mode".

1 2	Note: This T-SQL uses SQLCMD variables which can be enabled in SQL Server Management Studio. Enable SQLCMD mode : Tools -> Options -> Query Execution -> Check the box "By default, open new queries in SQLCMD mode".

:setvar TABLENAME "tblAttachments"
sp_MSforeachdb 'use [?]; 
if exists(select * from sys.tables where name = ''$(TABLENAME)'')
if exists (select count(*) from [$(TABLENAME)] having count(*) &amp;gt; 0)
  select [Db] = ''?'', [$(TABLENAME) Records] = count(*) from [$(TABLENAME)]';

:setvar TABLENAME "tblAttachments"

sp_MSforeachdb 'use [?];

if exists(select * from sys.tables where name = ''$(TABLENAME)'')

if exists (select count(*) from [$(TABLENAME)] having count(*) &gt; 0)

select [Db] = ''?'', [$(TABLENAME) Records] = count(*) from [$(TABLENAME)]';

<jco-blog/>

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

Deleting all but one duplicated row

Dynamic deletion of records from qualified tables

Find locked SQL Server objects

Script to Back Up SQL Database

Automatically Create and Delete Indexes Based on SQL Server’s Usage Metrics

Getting line-breaks into text from an Excel data for pasting to a SQL table

Generate Unique Namespace (in DNS Name Format)

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

Iterate databases that contain a target table that has one or more records

Category Archives: SQL Server