sp_MSForEachTable -- only current day

actually, i suppose information schema view may be more to my gain, simply given the fact that this guy is undocumented.  

i just want a generic proc to run on any database I pass into @dbname, retrieving rowcounts selectively.  by 'selectively', I just mean maybe i into go into the given @dbname and retrieve table counts from all the tables where name like '%Details', and datefield >= today.

stuff like that.  sp_MSForeachTable is fine, or information schema views.  either way....

i've got one together using sp_MSForeachTable, but it's hard-coded to the current database, and I'm just trying to genericize it such that it can be used across any of my instances, for the given @dbname.  the one i've got works fine, but i'm getting all data, not just the current day.

suggestions are welcome

suggestions are definitely needed.  :-)

>but it must be only current day data.

please clarify how exactly you understand to apply that rule?
based on a given column name that holds the date of the entry?
or something else?

yes, based on a given columnname -- 'tradedate', for example -- i'd want only records inserted current tradedate

i was trying to do it somewhat dynamically -- the @where clause, based on the @dbname, or even a @tablename -- just a very generic proc that would count current day data for the tablenames relevant to the given @dbname

hard-coded, it works just fine.  but to genericize it, i don't know how best to pass that into sp_msforeachtable, or even information schema view/tables.

@dbname will be a problem, because the sp_MSForEachtable runs in the current db ...
so, you just use sp_ prefix for your procedure, and create it in the master db, and it will work the same way...

@tablename
well, you can workaround, saving the value into a table, and use that table data inside the 'query' you run via
exec sp_MsForEachTable to check :


exec sp_MsForEachTable  '
if exists(select null from that_table WHERE table_name = ''?''
 exec( '' select ''''?'''' table_name, count(*) from ? WHERE tradedate >= convert(datetime, convert(varchar(10), getdate(), 120), 120) '' )
'

RE @dbname, the below sort of works, angelll.  it's just that i realized after the fact that it's not really generic enough to use in any database on the targeted server.  

Because of this line:  

WHERE EventTime >= GETDATE()", @whereand = "AND o.name LIKE ''%Details'' AND o.name <> ''XXDetails''

'EventTime' is only in 'database1' on the server that I am testing.  As are the o.names LIKE '%Details'.

That's pretty much the reason for this inquiry -- just trying to genericize things enough that I can run it on any database, per server.

The 'tradedate' example I gave before was just an example -- but say I have another database on the server where the date attribute is 'SaleDate', or 'OrderDate', or whatever.... I'd somehow need to dynamically find/use the 'date' field, in order to perform the count on the chosen database.

prolly too much work... too much for what i'm trying to do, that is.  the immediate need is specific to one database, within which I just need to know how much is being written current day.  i think i'll hard code it for that db... unless you've got another suggestion for me.  

:-)

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC countproc (
 @dbname varchar(25),
 @where varchar(1000)=NULL,
 @debug bit = 0
)
AS
SET NOCOUNT ON;
/* just a counter -- writes all table counts for the given @dbname into dbadmin.dbo.TableCounts.

exec countproc @dbname = 'database1',@where = 'TableName LIKE ''%Details'' AND TableName <> ''XXDetails''',@debug = 1
exec countproc @dbname = 'database2',@where = 'TableName LIKE ''Index%''',@debug = 1
*/
BEGIN
	DECLARE @sql VARCHAR(4000)
	SET @sql = '
	USE ['+@dbname+'];
	
DELETE dbadmin.dbo.TableCounts
EXEC sp_msforeachtable "INSERT INTO dbadmin.dbo.TableCounts SELECT ''?'' AS TableName,COUNT(*),GETDATE() FROM ? WHERE EventTime >= GETDATE()", @whereand = "AND o.name LIKE ''%Details'' AND o.name <> ''XXDetails'' "

UPDATE dbadmin.dbo.TableCounts SET TableName = replace(tablename,''[dbo].['','''')
UPDATE dbadmin.dbo.TableCounts SET TableName = replace(tablename,'']'','''')

SELECT TableName,RecordCount,InsertTime 
FROM dbadmin.dbo.TableCounts
WHERE '+@where 
	
END

IF (@debug = 1)
  PRINT (@sql)
ELSE
  EXEC (@sql)

Select allOpen in new window

I don't see the problem, if you KNOW what column names to be put into what server...

you can change:
set @sql = ' ....  XXDetails ... '
into:

set @sql = ' .... ' + @colname + ' ... '

that's a good point --say i've got 200 some odd servers -- they all house one or more of the 'standard' databases -- within each, of course, i KNOW what column names to be used for the date attributes, within the given @dbname.

seems i just need to be a little smarter -- if @dbname = 'this', my @colname = 'that'.

agreed.  thank you for the input, angellll.   (should have thought of that myself)