Get all Tables which have Duplicate values on Columns Name and Description

indian143

Hi All, We are moving the data from a huge database into another database as we guess we have Duplicate values on Name and Description fields in some tables we want to list our all those Tables which have these columns and duplicates on those columns using some script dynamically. Is there any way to write that sort of script if some body already did it and can help me, it would be a great help I am also searching any link, suggestion or code snippet would be a great help. Thanks in advance.

Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."

GuyThiebaut

Here's something I have written which will give you table column value frequencies. It will require a little bit of reworking for what you require. It should provide a decent basis for finding duplicate value columns.

--SQL Server 2008 R2 compliant

--set up tables and table columns

create table #tabs
(
[id] [int] IDENTITY(1,1) NOT NULL,
[tablename] [nvarchar](max) NULL
)

drop table #cols

create table #cols
(
[id] [int] IDENTITY(1,1) NOT NULL,
[colname] [nvarchar](max) NULL,
[tablename] [nvarchar](max) NULL
)

--specify tablenames to be reported on
insert into #tabs(tablename) values('A table name')
insert into #tabs(tablename) values('Another table name')
insert into #tabs(tablename) values('Yet another table name')

--get columns for tables

truncate table colvalfreq_tbl

declare @tabcol nvarchar(max) = 'DECLARE tabcol_cursor CURSOR FOR SELECT [tablename] FROM #tabs'
exec sp_executesql @tabcol

declare @tab nvarchar(200)

open tabcol_cursor
fetch next from tabcol_cursor into @tab

exec(
'insert into #cols(tablename,colname)
select '''+@tab+''',name
from sys.columns t
where t.object_id = OBJECT_ID('''+@tab+''')'
)

while @@fetch_status = 0
begin

fetch next from tabcol\_cursor into @tab
if @@fetch\_status = 0
begin

  exec(
  'insert into #cols(tablename,colname) 
  select '''+@tab+''',name 
  from sys.columns t 
  where t.object\_id = OBJECT\_ID('''+@tab+''')'
  )

end

end

close tabcol_cursor
deallocate tabcol_cursor

--get column value frequencies

declare @colval_cursor nvarchar(max) = 'DECLARE qc_cursor CURSOR FOR SELECT [tablename],[colname] FROM #cols order by id'
exec sp_executesql @colval_cursor

declare @col nvarchar(200)

open qc_cursor
fetch next from qc_cursor into @tab,@col

exec ('insert into colvalfreq_tbl(tablename,colname,colvalue,count) select '''+@tab+''' tablename, '''+@col+''' colname,['+@col+'] as colval,count(*) count '+'from '+@tab+' group by ['+@col+']')
while @@fetch_status = 0
begin

fetch next from qc\_cursor into @tab,@col

if @@fetch\_status = 0
begin

  --insert frequencies into colvalfreq
  exec ('insert into colvalfreq\_tbl(tablename,colname,colvalue,count) select '''+@tab+''' tablename, '''+@col+''' colname,\['+@col+'\] as colval,count(\*) count '+'from '+@tab+' group by \['+@col+'\]')

end

end
close qc_cursor
deallocate qc_cursor

se

Jorgen Andersson

How to do it would depend a lot on the database. You should update your post with that info.

Wrong is evil and must be defeated. - Jeff Ello

indian143

anyways is fine it is just an internal running script but I want to run it on all tables in the selected database on selected server. I though of little bit with dynamic sql but yes that's question too, is there any better approach for it than dynamic sql?

Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."

RNA Team

@indian1433, you can try with sp_MSforeachdb and sp_MSforeachtable .Please check out and let us know if the hint has helped you. N.B.~ Since you mentioned that it's just for internal purpose, so I have mentioned those two undocumented Stored Procedure. Please don't use it in production since they r undocumented and there is no guarantee that in future MS will not remove them (: