Tuesday, January 21, 2014

Finding out which tables aren't purged in my database


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

create table #tmpSQL(iIndex int identity(1,1) ,sqlStatement nvarchar(4000))
create table #tmpresults(tableName varchar(300),columnName varchar(300),minDate datetime)

INSERT INTO #tmpSQL(sqlStatement)
select distinct '
insert  #tmpResults
select ''' + t.name + ''', ''' + s.name + '''  ,min(' + s.name + ')  from ' +  ss.name + '.' +  t.name + '(NOLOCK)
'
from sys.columns s
inner join sys.types st
on st.user_type_id = s.user_type_id
inner join sys.tables t
on t.object_id = s.object_id
inner join sys.schemas ss
on ss.schema_id = t.schema_id
where t.object_id = s.object_id
and st.name in ('date','smalldatetime','datetime')

declare @iIndex int,@iMax int

select @iIndex = 1,@iMax=COUNT(*) from #tmpSQL

declare @sqlStatement nvarchar(4000)
set @sqlStatement = ''
WHILE @iIndex < = @iMax
BEGIN
select @sqlStatement = sqlStatement from #tmpSQL where iIndex = @iIndex
print @iIndex
declare @starTime datetime,@TimeDiff int
set @starTime = getdate()
print @sqlStatement
exec sp_executesql @sqlStatement
select @TimeDiff = datediff(ss,@starTime,GETDATE())
print @TimeDiff
 set @iIndex = @iIndex+1
END




with cte as(
select distinct tablename from #tmpresults
),cteCount as(
select ta.name, SUM(pa.rows) as numOfRows from cte
inner join
 sys.tables ta
 on cte.tableName = ta.name
 INNER JOIN sys.partitions pa
 ON pa.OBJECT_ID = ta.OBJECT_ID
 INNER JOIN sys.schemas sc
 ON ta.schema_id = sc.schema_id
 WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
 GROUP BY sc.name,ta.name
 )
 select cteCount.*,t.columnName,t.minDate From cteCount
 inner join #tmpresults t
 on cteCount.name = t.tableName
where minDate is not null
 ORDER BY numOfRows DESC

No comments:

Post a Comment