sp_MSForEachTable -- only current day

How can I use this on ONLY the current day data?  Doing rowcounts, all tables in the given @dbname, but it must be only current day data.

Can't get it into the @wherand without this:

Cannot call methods on nvarchar.

Any advice?
LVL 17
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
>seems i just need to be a little smarter -- if @dbname = 'this', my @colname = 'that'.

be smart, save the information in a config table, and based on the @dbname, query @colname ...
dbaSQLAuthor Commented:
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
dbaSQLAuthor Commented:
suggestions are definitely needed.  :-)
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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?
dbaSQLAuthor Commented:
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.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
@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...

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) '' )
dbaSQLAuthor Commented:
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.  


CREATE PROC countproc (
 @dbname varchar(25),
 @where varchar(1000)=NULL,
 @debug bit = 0
/* 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
	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 

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

Open in new window

Guy Hengel [angelIII / a3]Billing EngineerCommented:
I don't see the problem, if you KNOW what column names to be put into what server...

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

set @sql = ' .... ' + @colname + ' ... '
dbaSQLAuthor Commented:
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'.
dbaSQLAuthor Commented:
agreed.  thank you for the input, angellll.   (should have thought of that myself)
All Courses

From novice to tech pro — start learning today.