sp_msforeachtable not available: get tablename + min(value) + max(value)

Question

I have an Azure SQL database. Most tables have a field called Meta_inserted (datetime). i woiuld like to lop through all tables and if there is a field called Meta_insert I would like to see: Table_name, min(meta_inserted) as first, max(meta_inserted) as last.

I am used to solving this using sp_msforeachtable but that is not available in Azure SQL. Now what?

I suppose make your own (better) `sp_msforeachtable`. I believe there are many examples online. — HoneyBadger, Feb 24 '20 at 11:54
Hi Henrov, if my answer is helpful for you, can you please mark it as answer?. This can be beneficial to other community members. Thank you. — Leon Yue, Feb 26 '20 at 02:50
Sorry @LeonYue, somehow I missed the notification, your answer deserves the credits! Thanx! — Henrov, Mar 04 '20 at 11:15

score 1 · Accepted Answer · answered Feb 25 '20 at 06:04

sp_MSforeachtable is an undocumented stored procedure in the SQL Server Master database. This apparently hasn't been ported over to Azure SQL.

So you need to create the stored procedure manually in Azure SQL master db.

Please reference: Deploy database to Azure SQL fails, sp_MSforeachtable not found

Here's the full code of the undocumented stored procedure sp_MSforeachtable:

USE [master]
GO
/****** Object:  StoredProcedure [sys].[sp_MSforeachtable]    Script Date: 8/18/2017 8:47:44 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER proc [sys].[sp_MSforeachtable]
    @command1 nvarchar(2000), @replacechar nchar(1) = N'?', @command2 nvarchar(2000) = null,
   @command3 nvarchar(2000) = null, @whereand nvarchar(2000) = null,
    @precommand nvarchar(2000) = null, @postcommand nvarchar(2000) = null
as
    /* This proc returns one or more rows for each table (optionally, matching 
@where), with each table defaulting to its own result set */
    /* @precommand and @postcommand may be used to force a single result set via 
a temp table. */

    /* Preprocessor won't replace within quotes so have to use str(). */
    declare @mscat nvarchar(12)
    select @mscat = ltrim(str(convert(int, 0x0002)))

    if (@precommand is not null)
        exec(@precommand)

    /* Create the select */
   exec(N'declare hCForEachTable cursor global for select ''['' + 
REPLACE(schema_name(syso.schema_id), N'']'', N'']]'') + '']'' + ''.'' + ''['' + 
REPLACE(object_name(o.id), N'']'', N'']]'') + '']'' from dbo.sysobjects o join 
sys.all_objects syso on o.id = syso.object_id '
         + N' where OBJECTPROPERTY(o.id, N''IsUserTable'') = 1 ' + N' and o.category & ' + @mscat + N' = 0 '
         + @whereand)
    declare @retval int
    select @retval = @@error
    if (@retval = 0)
        exec @retval = sys.sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0

    if (@retval = 0 and @postcommand is not null)
        exec(@postcommand)

    return @retval

Ref: An introduction to sp_MSforeachtable; run commands iteratively through all tables in a database

Hope this helps.

sp_msforeachtable not available: get tablename + min(value) + max(value)

1 Answers1