info@techdevops.com
TechDevOps.com
Explore Tools
Experts in Microsoft SQL Server Operations, Performance, Migrations, Replication & Cloud



Hash Lookup/Static Table Contents
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2015-06-28







Solution:


Create Table:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[LOOKUP_LIST_HASH](
[TBL] [varchar](150) NULL,
[ROWNO] [int] NULL,
[HashKey] [varbinary](150) NULL,
[InsertedDate] [datetime] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[LOOKUP_LIST_HASH] ADD CONSTRAINT [DF_LOOKUP_LIST_HASH_InsertedDate] DEFAULT (getdate()) FOR [InsertedDate]
GO


Create Stored Procedure:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create Procedure [dbo].[spaHashLookupTablesContents]
as
Set NoCount On
Begin Transaction
Truncate Table dbo.LOOKUP_LIST_HASH
Declare @lkp nvarchar(250), @cnt int
Declare cur_lkps Cursor For Select lookupname from dbo.LOOKUP_LIST t1 with (nolock)
Inner Join dbo.SysObjects t2 on t1.lookupname = t2.name
Set @cnt = 1
Open cur_lkps Fetch Next From cur_lkps Into @lkp
While @@FETCH_STATUS = 0
Begin
If @cnt = 1
Begin
Declare @str nvarchar(4000)
Select @str = COALESCE(@str + 'convert(varchar(50),', '') + 'isnull(' + t1.name + ',0))' + ' + '
from syscolumns t1 inner join sysobjects t2
on t1.id = t2.id
where t2.name = @lkp order by t1.name asc
Set @str = LEFT(@str, LEN(@str) - 1)
Set @str = 'SELECT ' + '''' + @lkp + '''' + ' as TBL, Identity(int,1,1) as ROWNO, HASHBYTES(''SHA1'', convert(varchar(150),' + @str + ')
as HASHKEY, getdate() as inserteddate into #TMP from ' + @lkp + ''
End
If @cnt = 0
Begin
Select @str = COALESCE(@str + 'convert(varchar(50),', '') + 'isnull(' + t1.name + ',0))' + ' + '
from syscolumns t1 inner join sysobjects t2
on t1.id = t2.id
where t2.name = @lkp order by t1.name asc
Set @str = LEFT(@str, LEN(@str) - 1)
Set @str = 'SELECT ' + '''' + @lkp + '''' + ' as TBL, Identity(int,1,1) as ROWNO, HASHBYTES(''SHA1'', ' + @str + ')
as HASHKEY, getdate() as inserteddate into #TMP from ' + @lkp + ''
End

Set @str = @str + ' Insert into dbo.LOOKUP_LIST_HASH Select * from #TMP Truncate Table #TMP'
Exec sp_executesql @str

Set @lkp = ''
Set @str = ''
Set @cnt = 0

FETCH NEXT FROM cur_lkps INTO @lkp
END

CLOSE cur_lkps;
DEALLOCATE cur_lkps;
Commit



Image 1:





UPDATE:

HASHBYTES (Transact-SQL)

..."Beginning with SQL Server 2016, all algorithms other than SHA2_256, and SHA2_512 are deprecated. Older algorithms (not recommended) will continue working, but they will raise a deprecation event."

*Please update the above T-SQL with the HASHBYTES Algorithm based off this information.

The SHA1 hash function is now completely unsafe