6 Comments. Leave new

• 

  Troy

  December 30, 2011 10:34 am

  Thanks much for the handy scripts Brent.

  Because I often deal with environments with MANY databases per server, I’ve modified your 2nd script to iterate through the sys.databases collection with a firehose and generate the rename and drop scripts for ALL the databases at once.

  Hope you don’t mind me posting that here.

  Thanks…Troy

  use master
  set nocount on
  declare cur cursor fast_forward read_only for
  select [name] from sys.databases
  where [name] not in (‘master’,’msdb’,’tempdb’,’model’) — ADD EXCEPTIONS HERE
  declare @dbName varchar(100), @sql nvarchar(max)
  open cur
  fetch next from cur into @dbName
  while @@FETCH_STATUS -1
  begin
  set @sql = ‘SELECT ”’ + @dbName + ”’ as [database]
  ,s.name AS [schema]
  ,o.name AS [table]
  ,p.rows
  ,”EXEC sp_rename ””[” + s.name + ”].[” + o.name + ”]””, ””[” + s.name + ”].[_ToBeDropped_” + o.name + ”]””” AS ScriptToRename
  ,”DROP TABLE ””[” + s.name + ”].[” + o.name + ”]” AS ScriptToDrop
  FROM ‘ + @dbname + ‘.sys.indexes i
  INNER JOIN ‘ + @dbname + ‘.sys.objects o ON i.object_id = o.object_id
  INNER JOIN ‘ + @dbname + ‘.sys.schemas s on s.schema_id = o.schema_id
  INNER JOIN ‘ + @dbname + ‘.sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
  LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id
  WHERE i.type_desc = ”HEAP”
  AND COALESCE(ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates, ius.last_user_seek, ius.last_user_scan, ius.last_user_lookup) IS NULL
  ORDER BY rows desc’
  –print @sql
  exec sp_executesql @sql
  fetch next from cur into @dbName
  end
  close cur
  deallocate cur

  Reply
  • 

    Troy

    December 30, 2011 10:41 am

    In pasting it here, the format got a bit whack in several places. Sorry about that.

    The comments will need to be dash-dash…

    The FETCH_STATUS will need to be a not-equals…

    Single quotes will prob need to be fixed as well, as they turned into slanted single ticks.

    Reply
• 

  david wright

  July 2, 2012 8:29 am

  why the cursor loop?

  Reply
• 

  SQLGuyChuck

  July 6, 2012 12:45 am

  One of my servers db’s needs a real diet, so I updated script to look at clustered tables and tables where counts of use sum to 0. I had to break the coalesce into parts, otherwise you get an error with date and int showing up in the coalesce.

  SELECT SCHEMA_NAME(o.schema_id) AS [schema],OBJECT_NAME(i.object_id) AS [table],
  p.rows,
  ‘EXEC sp_rename ”[‘ + SCHEMA_NAME(o.schema_id) + ‘].[‘ + OBJECT_NAME(i.object_id) + ‘]”, ”[‘ + SCHEMA_NAME(o.schema_id) + ‘].[_ToBeDropped_’ + OBJECT_NAME(i.object_id) + ‘]”’ AS ScriptToRename,
  ‘DROP TABLE ”[‘ + SCHEMA_NAME(o.schema_id) + ‘].[‘ + OBJECT_NAME(i.object_id) + ‘]”’ AS ScriptToDrop
  FROM sys.indexes i
  INNER JOIN sys.objects o ON i.object_id = o.object_id
  INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
  LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id
  WHERE i.type_desc in (‘HEAP’,’CLUSTERED’)
  and (COALESCE(ius.user_seeks, ius.user_scans, ius.user_lookups,ius.user_updates) IS NULL or ius.user_seeks+ ius.user_scans+ ius.user_lookups+ius.user_updates= 0)
  and Coalesce( ius.last_user_seek, ius.last_user_scan, ius.last_user_lookup) IS NULL
  ORDER BY rows desc

  Reply
• 

  Roni

  July 6, 2012 10:10 am

  Thanks for the useful tip,
  I’m curious to know What will be your resolutions next year 🙂

  Reply
• 

  Duane Lawrence

  April 23, 2013 8:46 am

  Everyone knows there is more than one way to do the same thing and this query is based on Brent’s above yet will get a different set of tables to look at and consider. I found a list of 34 tables that are likely to be out of date tables or in other words not used anymore.

  SELECT
  SCHEMA_NAME(oo.schema_id) AS [schema]
  ,OBJECT_NAME(oo.object_id) AS [table]
  ,pp.rows
  ,’EXEC sp_rename ”[‘ + SCHEMA_NAME(oo.schema_id) + ‘].[‘ + OBJECT_NAME(oo.object_id) + ‘]”, ”[‘ + SCHEMA_NAME(oo.schema_id) + ‘].[_ToBeDropped_’ + OBJECT_NAME(oo.object_id) + ‘]”’ AS ScriptToRename
  ,’DROP TABLE ”[_ToBeDropped_’ + SCHEMA_NAME(oo.schema_id) + ‘].[‘ + OBJECT_NAME(oo.object_id) + ‘]”’ AS ScriptToDrop
  ,ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates, ius.last_user_seek, ius.last_user_scan, ius.last_user_lookup
  FROM sys.objects as oo
  INNER JOIN sys.partitions as pp ON oo.object_id = pp.object_id
  LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON oo.object_id = ius.object_id
  WHERE oo.type = ‘U’
  AND ius.user_seeks + ius.user_scans + ius.user_lookups + ius.user_updates = 0
  and coalesce(ius.last_user_seek, ius.last_user_scan, ius.last_user_lookup) is null
  order by SCHEMA_NAME(oo.schema_id), OBJECT_NAME(oo.object_id)

  Reply