Run stored procedure if table exists

Question

My friend has been asked to develop a procedure to run a stored procedure if a table exists.

There are 53 databases on the server, and we only want to run that stored procedure if TableNameZZZ exists in the database.

My testing I have to loop through the databases and check sys.tables to see if the table exists.

Our issue is figuring out how to then call the stored procedure with variables as the parameters and then go on to the next database and see if it has the table.

Any ideas on logic I can put into the stored procedure or cursor to make this happen?

Code so far (nothing is output from the PRINT @sql statement):

DECLARE @dbName VARCHAR(20);
DECLARE @sql NVARCHAR(MAX);
DECLARE @retentionDays AS INT = 90
DECLARE @sDate AS DATETIME
DECLARE @eDate AS DATETIME

DECLARE C CURSOR LOCAL FAST_FORWARD FOR 
    SELECT DISTINCT(name) 
    FROM sys.databases 
    WHERE database_id > 4;

OPEN C;

FETCH NEXT FROM C INTO @dbName;

WHILE (@@FETCH_STATUS = 0)
BEGIN 
    PRINT @dbName;

    SET @sql = 'SET ' + CAST(@sDate AS NVARCHAR) + ' = GETDATE() - ' + CAST(@retentionDays AS NVARCHAR) + CHAR(13)+CHAR(10) +
               'SET ' + CAST(@eDate AS NVARCHAR) + ' = (SELECT MIN(timestamp) AS [minDate] FROM [' + @dbName + '].[dbo].[TableNameZZZ])' + CHAR(13)+CHAR(10) +
               'USE [' + @dbName + ']; IF EXISTS (SELECT * FROM sys.tables WHERE name = ''TableNameZZZ'')' + CHAR(13)+CHAR(10) +
'BEGIN' + CHAR(13)+CHAR(10) +
               'EXECUTE [master].[dbo].[Batch_Delete] ' + CHAR(13)+CHAR(10) +
        '@startDate = ' + CAST(@eDate AS NVARCHAR) + CHAR(13)+CHAR(10) +
        ',@endDate = ' + CAST(@sDate AS NVARCHAR) + CHAR(13)+CHAR(10) +
        ',@dbName = ' + @dbName + CHAR(13)+CHAR(10) +
        ',@schemaName = ''dbo''' + CHAR(13)+CHAR(10) +
        ',@tableName = ''TableNameZZZ''' + CHAR(13)+CHAR(10) +
        ',@dateFieldName = ''timestamp''' + CHAR(13)+CHAR(10) +
        ',@saveToHistoryTable = 0' + CHAR(13)+CHAR(10) +
        ',@batch = 10000' + CHAR(13)+CHAR(10) +
    'END;'

    PRINT @sql
    --EXEC sys.sp_executesql @sql;

    FETCH NEXT FROM C INTO @dbName;
END 

CLOSE C;
DEALLOCATE C;

You can use `sp_MSforeachdb` to loop through the databases and then execute the stored procedure — Squirrel, Aug 23 '23 at 00:55
To get a list of databases with the table you can use https://stackoverflow.com/a/16520904/73226 — Martin Smith, Aug 23 '23 at 01:15
How about adding a simple if statement at the top of the Sproc that checks to see if the table exists? — Mark Kram, Aug 23 '23 at 01:29
Aside: Bad habits to kick include using string data types without a length, e.g. `CONVERT(VARCHAR,...`. The default length is `30` in a `cast` or `convert`, otherwise `1`. It's not worth saving a few keystrokes for something that will, sooner or later, get cut'n'pasted inappropriately and nibble thee. — HABO, Aug 23 '23 at 03:22
`'SET ' + CAST(@sDate AS NVARCHAR) + ' = GETDATE()...` <-- what? — Stuck at 1337, Aug 23 '23 at 14:48

score 2 · Accepted Answer · answered Aug 23 '23 at 02:02

One way of doing it would be to create a helper proc with all the logic needed for a database. (Created as a temporary proc below)

CREATE OR ALTER PROC #HelperProc 
@dbName sysname
AS 
DECLARE @retentionDays AS INT = 90

declare @startDate datetime
DECLARE @endDate datetime = DATEADD(DAY , -@retentionDays, GETDATE()) 
DECLARE @execSQL NVARCHAR(500) = QUOTENAME(@dbName) + '.sys.sp_executesql'

EXEC @execSQL N'SELECT @startDate = MIN(timestamp) FROM [dbo].[TableNameZZZ]', N'@startDate datetime OUTPUT', @startDate = @startDate output

EXECUTE [master].[dbo].[Batch_Delete] 
 @startDate = @startDate
,@endDate = @endDate
,@dbName = @dbName
,@schemaName = 'dbo'
,@tableName = 'TableNameZZZ'
,@dateFieldName = 'timestamp'
,@saveToHistoryTable = 0
,@batch = 10000

And then call it for each database that contains the table - As the #HelperProc is only a #temp proc this is only in scope in the same session that creates it so the below would go underneath it in the same SSMS window.

GO

DECLARE @DBName SYSNAME

DECLARE @C1 AS CURSOR;
SET @C1 = CURSOR FAST_FORWARD FOR SELECT name
FROM   sys.databases
WHERE  CASE WHEN state_desc = 'ONLINE'  THEN OBJECT_ID(QUOTENAME(name) + '.[dbo].[TableNameZZZ]', 'U') END IS NOT NULL ;
OPEN @C1;
FETCH NEXT FROM @C1 INTO @DBName ;
WHILE @@FETCH_STATUS = 0
BEGIN
  PRINT @DBName
  EXEC #HelperProc @dbName
  FETCH NEXT FROM @C1 INTO @DBName ;
END

That looks very interesting let me try that out and see if I can make it happen. Thanks so much for this. — Garry B, Aug 23 '23 at 02:06

Run stored procedure if table exists

1 Answers1