Scenario:

I've seen cases where tables are temporarily backed up(copied) and given a date on the end of the name. Sometimes, someone forgets to remove these tables or they are just no longer needed.

I wanted a way to find any tables that had a date in the name so I could delete any unneeded tables.

CodePudding user response：

Using metadata:

SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'; 
-- pattern at the end should resemble used date format
-- here: my_table20211216

CodePudding user response：

In case it's helpful.

After using the feedback from others, I wrote a simpler version that gets just tables. The original answer also had objects(stored procedures).

IF OBJECT_ID('tempdb..#t1') IS NOT NULL DROP TABLE #t1

SELECT      @@SERVERNAME        AS ServerName
          , (SELECT DB_NAME ()) AS DBName
          , s.name              AS SchemaName
          , st.name             AS TableName
INTO #t1          
FROM        sys.tables  AS st
INNER JOIN  sys.objects AS so ON so.object_id = st.object_id
INNER JOIN  sys.schemas AS s ON s.schema_id = st.schema_id
WHERE       st.name LIKE '%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
ORDER BY    st.name;

SELECT t1.ServerName
     , t1.DBName
     , t1.SchemaName
     , t1.TableName
     , 'DROP TABLE '   QUOTENAME(ServerName)   '.'   QUOTENAME(DBName) '.'    QUOTENAME(SchemaName)   '.'   QUOTENAME(TableName) AS drop_table
     FROM #t1 AS t1

CodePudding user response：

In case it's helpful to others.

This script has 2 parts. One does a single database and the other script looks at all databases on an instance.

The script(s) find all tables that have more than 6 numbers in them and look like a date. It also provides the script to be able to quickly drop them.

This was constructed with the help of a few other answers on StackOverflow.

Devart's answer on this thread. Query to get only numbers from a string

Select something that has more/less than x character

/*  SINGLE SERVER:
IF OBJECT_ID('tempdb..#t1') IS NOT NULL DROP TABLE #t1

DECLARE @temp TABLE(
    ServerName NVARCHAR(1000)
  , DatabaseName NVARCHAR(1000)
  , SchemaName NVARCHAR(1000)
  , TableName  NVARCHAR(1000)
);

INSERT INTO @temp(
    ServerName
  , DatabaseName
  , SchemaName
  , TableName
)
SELECT          @@SERVERNAME AS ServerName
              , (SELECT DB_NAME()) AS DatabaseName
              , ss.name
              , so.name
FROM            sys.objects AS so
LEFT OUTER JOIN sys.schemas AS ss ON so.schema_id = ss.schema_id;


SELECT  t.ServerName
      , t.DatabaseName
      , t.SchemaName
      , t.TableName
      , LEFT(t.subsrt, PATINDEX ('%[^0-9]%', t.subsrt   't') - 1) AS NumInTblName
INTO #t1
FROM    (SELECT subsrt = SUBSTRING (d.TableName, d.pos, LEN (d.TableName))
              , d.ServerName
              , d.DatabaseName
              , d.SchemaName
              , d.TableName
         FROM   (SELECT ServerName
                      , DatabaseName
                      , SchemaName
                      , TableName
                      , pos = PATINDEX ('%[0-9]%', TableName)
                 FROM   @temp
                 WHERE  PATINDEX ('%[0-9]%', TableName) <> '') d ) t

SELECT 
      ServerName
    , DatabaseName
    , SchemaName
    , TableName
    , NumInTblName
    , ISDATE(NumInTblName) AS [ISDate]
    , 'DROP TABLE ['   ServerName   '].'   '['  DatabaseName  '].['   SchemaName   '].'   '['  TableName  ']' AS DROP_TABLE_Script --  ' WHERE '   PotentialDate   ' > DATEADD(day, -365,GETDATE())' AS DROP_TABLE_Script
FROM #t1
WHERE LEN(NumInTblName) > 6
AND ISDATE(NumInTblName) = 1
ORDER BY SchemaName ASC, TableName ASC

*/
--All DB's on an instance.

EXEC sp_MSForEachDB @command1='USE ?;
DECLARE @temp TABLE(
    ServerName NVARCHAR(1000)
  , DatabaseName NVARCHAR(1000)
  , SchemaName NVARCHAR(1000)
  , TableName  NVARCHAR(1000)
);

INSERT INTO @temp(
    ServerName
  , DatabaseName
  , SchemaName
  , TableName
)
SELECT          @@SERVERNAME AS ServerName
              , (SELECT DB_NAME()) AS DatabaseName
              , ss.name
              , so.name
FROM            sys.objects AS so
LEFT OUTER JOIN sys.schemas AS ss ON so.schema_id = ss.schema_id;


SELECT  t.ServerName
      , t.DatabaseName
      , t.SchemaName
      , t.TableName
      , LEFT(t.subsrt, PATINDEX (''%[^0-9]%'', t.subsrt   ''t'') - 1) AS NumInTblName
INTO #t1
FROM    (SELECT subsrt = SUBSTRING (d.TableName, d.pos, LEN (d.TableName))
              , d.ServerName
              , d.DatabaseName
              , d.SchemaName
              , d.TableName
         FROM   (SELECT ServerName
                      , DatabaseName
                      , SchemaName
                      , TableName
                      , pos = PATINDEX (''%[0-9]%'', TableName)
                 FROM   @temp
                 WHERE  PATINDEX (''%[0-9]%'', TableName) <> '''') d ) t

SELECT 
      ServerName
    , DatabaseName
    , SchemaName
    , TableName
    , NumInTblName
    , ISDATE(NumInTblName) AS [ISDate]
    , ''DROP TABLE [''   ServerName   ''].''   ''[''  DatabaseName  ''].[''   SchemaName   ''].''   ''[''  TableName  '']'' AS DROP_TABLE_Script --  '' WHERE ''   PotentialDate   '' > DATEADD(day, -365,GETDATE())'' AS DROP_TABLE_Script
FROM #t1
WHERE LEN(NumInTblName) > 6
AND ISDATE(NumInTblName) = 1
ORDER BY SchemaName ASC, TableName ASC
'