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